Обсуждение: Is there any easy way to determine a default value specified for table column?
Is there any easy way to determine a default value specified for table column?
От
Belka Lambda
Дата:
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
Re: Is there any easy way to determine a default value specified for table column?
От
Thomas Kellerer
Дата:
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