Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get the default values of table columns in Postgres?

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.

like image 635
sjchen Avatar asked Nov 16 '11 03:11

sjchen


People also ask

What is the default value of column?

Default values can be NULL, or they can be a value that matches the data type of the column (number, text, date, for example).

What is Postgres default value?

If no default value is declared explicitly, the default value is the null value. This usually makes sense because a null value can be considered to represent unknown data.

How do I set the default value in a column in pgAdmin?

Set Column Default using pgAdminIn the popup, go to 'Columns' tab and click on the edit icon againt a column to change the default value, as shown below. Now, go to the 'Constraints' tab and set or change the default value. Click on the Save button to save the changes.

Are Postgres columns nullable by default?

NULL The column is allowed to contain null values. This is the default.


1 Answers

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.

like image 174
Peter Eisentraut Avatar answered Sep 30 '22 19:09

Peter Eisentraut