PostgreSQL: How to Get Default Values of Each Column

Often in PostgreSQL you may want to get the default value of each column in a table.

You can use the following syntax to do so:

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

This particular example will return the column name and default value for each column in the table named athletes.

The following example shows how to use this syntax in practice.

Example: How to Get Default Values of Columns in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER DEFAULT 0,
  team TEXT DEFAULT 'Mavs',
  points INTEGER DEFAULT 10
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);

-- view all rows in table
SELECT * FROM athletes;

 id | team | points 
----+------+--------
  1 | Mavs |     22
  2 | Mavs |     30
  3 | Mavs |     12
  4 | Mavs |      9
  5 | Mavs |     14
  6 | Heat |     16
  7 | Heat |     40
  8 | Heat |     18
  9 | Heat |     16
 10 | Heat |     33

Suppose that we would like to get the default value for each column in the athletes table.

We can use the following syntax to do so:

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

 column_name | column_default 
-------------+----------------
 id          | 0
 team        | 'Mavs'::text
 points      | 10

The resulting table shows the column name and default value for each column in the table.

For example, we can see:

• The default value for the id column is 0.
• The default value for the team column is ‘Mavs’.
• The default value for the points column is 10.

You can use similar syntax to get the default value for each column in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Update Multiple Columns in Table
PostgreSQL: How to Rename Column Only if Exists
PostgreSQL: How to Count Occurrences of Each Value in Column