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