Get the default values of table columns in Postgres?

Question

I'm looking for a way to run a query to find the default values of the columns of a table in Postgres. For example, if I made a table with the following query:

_{**Editor's note: I fixed the table definition as it has no impact on the question.}

CREATE TABLE mytable (
    integer int DEFAULT 2,
    text varchar(64) DEFAULT 'I am default',
    moretext varchar(64) DEFAULT 'I am also default',
    unimportant int 
);

I need a query that would tell me, in some format, that the default for integer is 2, text is 'I am default', and moretext is 'I am also default'. The query result can include any value for any other column that doesn't have a default, i.e., unimportant is unimportant for my purposes and doesn't matter at all.

Answer 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.