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
pubviaroot
added (commit 83fd4532)
- column
- PostgreSQL 11
- column
pubtruncate
added (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