Get the default values of table columns in Postgres?

Solution 1:

Use the information schema:

SELECT column_name, column_default
FROM information_schema.columns
WHERE (table_schema, table_name) = ('public', 'mytable')
ORDER BY ordinal_position;

 column_name │             column_default             
─────────────┼────────────────────────────────────────
 integer     │ 2
 text        │ 'I am default'::character varying
 moretext    │ 'I am also default'::character varying
 unimportant │ 
(4 rows)

Up to the schema naming, this should work in any SQL database system.

Solution 2:

System catalogs are the source of truth in Postgres:

SELECT pg_get_expr(d.adbin, d.adrelid) AS default_value
FROM   pg_catalog.pg_attribute    a
LEFT   JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid,  d.adnum)
WHERE  NOT a.attisdropped           -- no dropped (dead) columns
AND    a.attnum   > 0               -- no system columns
AND    a.attrelid = 'myschema.mytable'::regclass
AND    a.attname  = 'mycolumn';

• Related answer on dba.SE discussing information schema vs. system catalogs

LEFT JOIN guarantees a result as long as the column exists. If there is no default you get NULL - which happens to be the default default. And almost always correct. But see:

• How to use default value of data type as column default?

To exclude columns without default, use JOIN instead and be prepared to get no row occasionally.

The special cast ::regclass considers the current setting for search_path. With names that aren't schema-qualified (which you should, to be sure!), you may or may not get the expected result. See rebuttal below. More in the manual. Related:

• How does the search_path influence identifier resolution and the "current schema"

No need to include pg_class once we have the OID of the table. Faster without.

Why the first answer wouldn't do

@Zohaib's query is almost but not quite right. There are a couple of issues. I copied it here for future reference. Do not use this:

SELECT adsrc as default_value
 FROM pg_attrdef pad, pg_atttribute pat, pg_class pc
 WHERE pc.relname='your_table_name'
     AND pc.oid=pat.attrelid AND pat.attname='your_column_name'
     AND pat.attrelid=pad.adrelid AND pat.attnum=pad.adnum

• Copied from some blog. Good that it's mentioned, but the source should be added. People reading that blog need to be warned.

• Typo in pg_atttribute - fixed easily.

• Doesn't return any rows, if there is no default specified for the requested column. Better make that a LEFT JOIN pg_attrdef ON .., so you always get a resulting row if the column exists. It will be NULL, if there is no default, which is actually the correct result because NULL is the default then.

• If you remove attname from the WHERE clause, you only get values for columns that actually have a default value. Not for others. And you need to add attname to the SELECT list or you will not know for which column.

• The query would also return the default of a column that is already dropped, which is wrong. Read about the details in the manual.

• Most importantly: the query can give completely wrong results, as it does not take the schema name into account. There can be any number of table1.col1 in a postgres database: in various schemas. If more than one have a default, you get multiple values. If the column you have in mind does not have a default, but another one in another schema does, you will be fooled and never know it.

• Last not least: in recent PostgreSQL releases, the adsrc column has been removed from pg_attrdef by commit fe5038236c, as it was redundant, deprecated and unused in PostgreSQL.

To sum it up: C/P from some blog without insight went dangerously wrong.

Solution 3:

I liked Erwin's Answer but was having some difficulties:

1. Sometimes I would get column names "........pg.dropped.30.......", etc. even with the NOT a.attisdropped qualification. It was not consistent, I wound up having to loop through the results and check the column names.
2. The returned default value sometimes had a cast attached, e.g. 'I am default'::character varying which did not work when they are being stuffed into web form input values. I could not think of a good way to remove the cast suffix without doing something like .replace(/::.*/, '') which is not robust enough. I bet Erwin can figure out some magical way to EVAL() the returned value and use the a.atttypid column to get the right data type.

So I went back to what I was doing before:

BEGIN;
INSERT INTO mytable DEFAULT VALUES RETURNING *;
ROLLBACK;

One thing to note here is that any SERIAL columns will get incremented. It likely does not matter so long as it is unique if it is just a table index. Otherwise it is a deal breaker.

The other thing to watch out for is any TRIGGER AFTER/BEFORE INSERT which will fire even though the INSERT gets rolled back (I think any changes the trigger function makes will get rolled back though.)