Contents

pg_attrdef

A system table used to store column default values

pg_attrdef is a system catalogue table used to store column default values.

pg_attrdef was added in PostgreSQL 6.2.

Notes

"column d.adsrc does not exist"

If you encounter an error like this:

ERROR: column d.adsrc does not exist

it means you are using client software which has not yet been updated to support PostgreSQL 12.

There is no way of fixing this issue other than upgrading the affected client software.

Definition by PostgreSQL version

pg_attrdef (PostgreSQL 16)

              Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Collation | Nullable | Default 
---------+--------------+-----------+----------+---------
 oid     | oid          |           | not null | 
 adrelid | oid          |           | not null | 
 adnum   | smallint     |           | not null | 
 adbin   | pg_node_tree | C         | not null | 
Indexes:
    "pg_attrdef_oid_index" PRIMARY KEY, btree (oid)
    "pg_attrdef_adrelid_adnum_index" UNIQUE CONSTRAINT, btree (adrelid, adnum)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 15)

              Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Collation | Nullable | Default 
---------+--------------+-----------+----------+---------
 oid     | oid          |           | not null | 
 adrelid | oid          |           | not null | 
 adnum   | smallint     |           | not null | 
 adbin   | pg_node_tree | C         | not null | 
Indexes:
    "pg_attrdef_oid_index" PRIMARY KEY, btree (oid)
    "pg_attrdef_adrelid_adnum_index" UNIQUE CONSTRAINT, btree (adrelid, adnum)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 14)

              Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Collation | Nullable | Default 
---------+--------------+-----------+----------+---------
 oid     | oid          |           | not null | 
 adrelid | oid          |           | not null | 
 adnum   | smallint     |           | not null | 
 adbin   | pg_node_tree | C         | not null | 
Indexes:
    "pg_attrdef_oid_index" PRIMARY KEY, btree (oid)
    "pg_attrdef_adrelid_adnum_index" UNIQUE CONSTRAINT, btree (adrelid, adnum)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 13)

              Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Collation | Nullable | Default 
---------+--------------+-----------+----------+---------
 oid     | oid          |           | not null | 
 adrelid | oid          |           | not null | 
 adnum   | smallint     |           | not null | 
 adbin   | pg_node_tree | C         | not null | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 12)

              Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Collation | Nullable | Default 
---------+--------------+-----------+----------+---------
 oid     | oid          |           | not null | 
 adrelid | oid          |           | not null | 
 adnum   | smallint     |           | not null | 
 adbin   | pg_node_tree | C         | not null | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 11)

              Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Collation | Nullable | Default 
---------+--------------+-----------+----------+---------
 adrelid | oid          |           | not null | 
 adnum   | smallint     |           | not null | 
 adbin   | pg_node_tree |           |          | 
 adsrc   | text         |           |          | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 10)

              Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Collation | Nullable | Default 
---------+--------------+-----------+----------+---------
 adrelid | oid          |           | not null | 
 adnum   | smallint     |           | not null | 
 adbin   | pg_node_tree |           |          | 
 adsrc   | text         |           |          | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 9.6)

   Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Modifiers 
---------+--------------+-----------
 adrelid | oid          | not null
 adnum   | smallint     | not null
 adbin   | pg_node_tree | 
 adsrc   | text         | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 9.5)

   Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Modifiers 
---------+--------------+-----------
 adrelid | oid          | not null
 adnum   | smallint     | not null
 adbin   | pg_node_tree | 
 adsrc   | text         | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 9.4)

   Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Modifiers 
---------+--------------+-----------
 adrelid | oid          | not null
 adnum   | smallint     | not null
 adbin   | pg_node_tree | 
 adsrc   | text         | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 9.3)

   Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Modifiers 
---------+--------------+-----------
 adrelid | oid          | not null
 adnum   | smallint     | not null
 adbin   | pg_node_tree | 
 adsrc   | text         | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 9.2)

   Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Modifiers 
---------+--------------+-----------
 adrelid | oid          | not null
 adnum   | smallint     | not null
 adbin   | pg_node_tree | 
 adsrc   | text         | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 9.1)

   Table "pg_catalog.pg_attrdef"
 Column  |     Type     | Modifiers 
---------+--------------+-----------
 adrelid | oid          | not null
 adnum   | smallint     | not null
 adbin   | pg_node_tree | 
 adsrc   | text         | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 9.0)

 Table "pg_catalog.pg_attrdef"
 Column  |   Type   | Modifiers 
---------+----------+-----------
 adrelid | oid      | not null
 adnum   | smallint | not null
 adbin   | text     | 
 adsrc   | text     | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 8.4)

 Table "pg_catalog.pg_attrdef"
 Column  |   Type   | Modifiers 
---------+----------+-----------
 adrelid | oid      | not null
 adnum   | smallint | not null
 adbin   | text     | 
 adsrc   | text     | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 8.3)

 Table "pg_catalog.pg_attrdef"
 Column  |   Type   | Modifiers 
---------+----------+-----------
 adrelid | oid      | not null
 adnum   | smallint | not null
 adbin   | text     | 
 adsrc   | text     | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

pg_attrdef (PostgreSQL 8.2)

 Table "pg_catalog.pg_attrdef"
 Column  |   Type   | Modifiers 
---------+----------+-----------
 adrelid | oid      | not null
 adnum   | smallint | not null
 adbin   | text     | 
 adsrc   | text     | 
Indexes:
    "pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
    "pg_attrdef_oid_index" UNIQUE, btree (oid)

Documentation: pg_attrdef

Change history

Examples

postgres=# CREATE TABLE foo (val TEXT DEFAULT 'bar');
CREATE TABLE
postgres=# SELECT a.*
postgres-#   FROM pg_attrdef a
postgres-#   JOIN pg_class c ON a.adrelid=c.oid
postgres-#  WHERE c.relname='foo'\x\g\x
Expanded display is on.
-[ RECORD 1 ]--------------------------------------------------------------
oid     | 16456
adrelid | 16453
adnum   | 1
adbin   | {CONST :consttype 25
                          :consttypmod -1
                          :constcollid 100
                          :constlen -1
                          :constbyval false
                          :constisnull false
                          :location 35
                          :constvalue 7 [ 28 0 0 0 98 97 114 ]}

Use pg_get_expr() to convert the value adbin into human-readable format, e.g.: 

postgres=# SELECT pg_catalog.pg_get_expr(a.adbin, a.adrelid, true)
postgres-# FROM pg_attrdef a
postgres-#   JOIN pg_class c ON a.adrelid=c.oid
postgres-# WHERE c.relname='foo';
 pg_get_expr
-------------
 'bar'::text
(1 row)

Categories

System catalogue

See also

pg_attribute