pg_publication
A system table storing all publications created in the database
pg_publication is a system catalogue table storing all publications created in the database.
pg_publication was added in PostgreSQL 10.
Individual tables are associated with a subscription via the pg_publication_rel table.
psql commands
\dRp[+]- lists available publications
Definition by PostgreSQL version
pg_publication (PostgreSQL 16)
Table "pg_catalog.pg_publication"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
oid | oid | | not null |
pubname | name | | not null |
pubowner | oid | | not null |
puballtables | boolean | | not null |
pubinsert | boolean | | not null |
pubupdate | boolean | | not null |
pubdelete | boolean | | not null |
pubtruncate | boolean | | not null |
pubviaroot | boolean | | not null |
Indexes:
"pg_publication_oid_index" PRIMARY KEY, btree (oid)
"pg_publication_pubname_index" UNIQUE CONSTRAINT, btree (pubname)
Documentation: pg_publication
pg_publication (PostgreSQL 15)
Table "pg_catalog.pg_publication"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
oid | oid | | not null |
pubname | name | | not null |
pubowner | oid | | not null |
puballtables | boolean | | not null |
pubinsert | boolean | | not null |
pubupdate | boolean | | not null |
pubdelete | boolean | | not null |
pubtruncate | boolean | | not null |
pubviaroot | boolean | | not null |
Indexes:
"pg_publication_oid_index" PRIMARY KEY, btree (oid)
"pg_publication_pubname_index" UNIQUE CONSTRAINT, btree (pubname)
Documentation: pg_publication
pg_publication (PostgreSQL 14)
Table "pg_catalog.pg_publication"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
oid | oid | | not null |
pubname | name | | not null |
pubowner | oid | | not null |
puballtables | boolean | | not null |
pubinsert | boolean | | not null |
pubupdate | boolean | | not null |
pubdelete | boolean | | not null |
pubtruncate | boolean | | not null |
pubviaroot | boolean | | not null |
Indexes:
"pg_publication_oid_index" PRIMARY KEY, btree (oid)
"pg_publication_pubname_index" UNIQUE CONSTRAINT, btree (pubname)
Documentation: pg_publication
pg_publication (PostgreSQL 13)
Table "pg_catalog.pg_publication"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
oid | oid | | not null |
pubname | name | | not null |
pubowner | oid | | not null |
puballtables | boolean | | not null |
pubinsert | boolean | | not null |
pubupdate | boolean | | not null |
pubdelete | boolean | | not null |
pubtruncate | boolean | | not null |
pubviaroot | boolean | | not null |
Indexes:
"pg_publication_oid_index" UNIQUE, btree (oid)
"pg_publication_pubname_index" UNIQUE, btree (pubname)
Documentation: pg_publication
pg_publication (PostgreSQL 12)
Table "pg_catalog.pg_publication"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
oid | oid | | not null |
pubname | name | | not null |
pubowner | oid | | not null |
puballtables | boolean | | not null |
pubinsert | boolean | | not null |
pubupdate | boolean | | not null |
pubdelete | boolean | | not null |
pubtruncate | boolean | | not null |
Indexes:
"pg_publication_oid_index" UNIQUE, btree (oid)
"pg_publication_pubname_index" UNIQUE, btree (pubname)
Documentation: pg_publication
pg_publication (PostgreSQL 11)
Table "pg_catalog.pg_publication"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
pubname | name | | not null |
pubowner | oid | | not null |
puballtables | boolean | | not null |
pubinsert | boolean | | not null |
pubupdate | boolean | | not null |
pubdelete | boolean | | not null |
pubtruncate | boolean | | not null |
Indexes:
"pg_publication_oid_index" UNIQUE, btree (oid)
"pg_publication_pubname_index" UNIQUE, btree (pubname)
Documentation: pg_publication
pg_publication (PostgreSQL 10)
Table "pg_catalog.pg_publication"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
pubname | name | | not null |
pubowner | oid | | not null |
puballtables | boolean | | not null |
pubinsert | boolean | | not null |
pubupdate | boolean | | not null |
pubdelete | boolean | | not null |
Indexes:
"pg_publication_oid_index" UNIQUE, btree (oid)
"pg_publication_pubname_index" UNIQUE, btree (pubname)
Documentation: pg_publication
Change history
- PostgreSQL 13
- column
pubviarootadded (commit 83fd4532)
- column
- PostgreSQL 11
- column
pubtruncateadded (commit 039eb6e9)
- column
- PostgreSQL 10
- added (commit 665d1fad)
Examples
postgres=# CREATE PUBLICATION test_publication FOR TABLE foo; CREATE PUBLICATION postgres=# SELECT * FROM pg_publication\gx -[ RECORD 1 ]+----------------- oid | 91147 pubname | test_publication pubowner | 10 puballtables | f pubinsert | t pubupdate | t pubdelete | t pubtruncate | t pubviaroot | f postgres=# \dRp List of publications Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ------------------+----------+------------+---------+---------+---------+-----------+---------- test_publication | postgres | f | t | t | t | t | f (1 row) postgres=# \dRp+ Publication test_publication Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f | t | t | t | t | f Tables: "public.foo"
References
- PostgreSQL documentation: pg_publication
