Thread: Is there any easy way to determine a default value specified for table column?

Hi everyone!

Is there a way to "nicely" determine a default value of a table column? A function, which could be used, like:
SELECT default_of('my_table.my_integer_column') :: integer;
?

I scanned pg_proc table:
===================================
postgres=# select proname from pg_proc where proname like '%dflt%' or proname like '%default%';
        proname
------------------------
 RI_FKey_setdefault_del
 RI_FKey_setdefault_upd
(2 rows)
===================================
, so I guess there is no easy way to get a column default, is there?

I need it to imitate something like (notice COALESCE(...)):
INSERT INTO my_table (column1,column2,column3) VALUES (COALESCE($1, DEFAULT), 'column2_value', 'column3_value');

Regards,
Belka

Belka Lambda wrote on 14.03.2010 01:24:
> Hi everyone!
>
> Is there a way to "nicely" determine a default value of a table column? A function, which could be used, like:

The defaults are store in pg_attrdef, the corresponding column definitions in pg_attribute.

So you would need to do a join between the two tables, something like:

select c.relname, a.attname, def.adsrc
from pg_attrdef def
   join pg_class c on def.adrelid = c.oid
   join pg_attribute a on a.attrelid = c.oid and a.attnum = def.adnum
where c.relname = 'the_table_name'

Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> Belka Lambda wrote on 14.03.2010 01:24:
>> Is there a way to "nicely" determine a default value of a table column? A function, which could be used, like:

> The defaults are store in pg_attrdef, the corresponding column definitions in pg_attribute.

> So you would need to do a join between the two tables, something like:

> select c.relname, a.attname, def.adsrc
> from pg_attrdef def
>    join pg_class c on def.adrelid = c.oid
>    join pg_attribute a on a.attrelid = c.oid and a.attnum = def.adnum
> where c.relname = 'the_table_name'

Relying on adsrc is a very bad idea.  I'd suggest
    pg_catalog.pg_get_expr(adbin, adrelid)
which is what pg_dump uses.

            regards, tom lane