pg_attrdef
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
- PostgreSQL 12
- column
adsrc
removed (commit fe503823)
- column
- PostgreSQL 6.2
- added (commit 25aa0f8d)
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)
References
- PostgreSQL documentation: pg_attrdef