pg_publication_rel

A system table storing the mapping between relations and publications

pg_publication_rel is a system catalogue table storing the mapping between relations and publications.

pg_publication_rel was added in PostgreSQL 10.

The view pg_publication_tables provides a human-readable version of this table.

Note that only relations explicitly added to a publication (e.g. CREATE PUBLICATION ... FOR TABLE ...) will be recorded in this table. Tables in publications which were created with CREATE PUBLICATION ... FOR ALL TABLES will not be recorded in pg_publication_rel., but will be visible in pg_publication_tables.

Definition by PostgreSQL version

pg_publication_rel (PostgreSQL 15)

          Table "pg_catalog.pg_publication_rel"
 Column  |     Type     | Collation | Nullable | Default 
---------+--------------+-----------+----------+---------
 oid     | oid          |           | not null | 
 prpubid | oid          |           | not null | 
 prrelid | oid          |           | not null | 
 prqual  | pg_node_tree | C         |          | 
 prattrs | int2vector   |           |          | 
Indexes:
    "pg_publication_rel_oid_index" PRIMARY KEY, btree (oid)
    "pg_publication_rel_prpubid_index" btree (prpubid)
    "pg_publication_rel_prrelid_prpubid_index" UNIQUE CONSTRAINT, btree (prrelid, prpubid)
    

Documentation: pg_publication_rel

pg_publication_rel (PostgreSQL 14)

      Table "pg_catalog.pg_publication_rel"
 Column  | Type | Collation | Nullable | Default 
---------+------+-----------+----------+---------
 oid     | oid  |           | not null | 
 prpubid | oid  |           | not null | 
 prrelid | oid  |           | not null | 
Indexes:
    "pg_publication_rel_oid_index" PRIMARY KEY, btree (oid)
    "pg_publication_rel_prrelid_prpubid_index" UNIQUE CONSTRAINT, btree (prrelid, prpubid)
    

Documentation: pg_publication_rel

pg_publication_rel (PostgreSQL 13)

      Table "pg_catalog.pg_publication_rel"
 Column  | Type | Collation | Nullable | Default 
---------+------+-----------+----------+---------
 oid     | oid  |           | not null | 
 prpubid | oid  |           | not null | 
 prrelid | oid  |           | not null | 
Indexes:
    "pg_publication_rel_oid_index" UNIQUE, btree (oid)
    "pg_publication_rel_prrelid_prpubid_index" UNIQUE, btree (prrelid, prpubid)
    

Documentation: pg_publication_rel

pg_publication_rel (PostgreSQL 12)

      Table "pg_catalog.pg_publication_rel"
 Column  | Type | Collation | Nullable | Default 
---------+------+-----------+----------+---------
 oid     | oid  |           | not null | 
 prpubid | oid  |           | not null | 
 prrelid | oid  |           | not null | 
Indexes:
    "pg_publication_rel_oid_index" UNIQUE, btree (oid)
    "pg_publication_rel_prrelid_prpubid_index" UNIQUE, btree (prrelid, prpubid)
    

Documentation: pg_publication_rel

pg_publication_rel (PostgreSQL 11)

      Table "pg_catalog.pg_publication_rel"
 Column  | Type | Collation | Nullable | Default 
---------+------+-----------+----------+---------
 prpubid | oid  |           | not null | 
 prrelid | oid  |           | not null | 
Indexes:
    "pg_publication_rel_oid_index" UNIQUE, btree (oid)
    "pg_publication_rel_prrelid_prpubid_index" UNIQUE, btree (prrelid, prpubid)
    

Documentation: pg_publication_rel

pg_publication_rel (PostgreSQL 10)

      Table "pg_catalog.pg_publication_rel"
 Column  | Type | Collation | Nullable | Default 
---------+------+-----------+----------+---------
 prpubid | oid  |           | not null | 
 prrelid | oid  |           | not null | 
Indexes:
    "pg_publication_rel_oid_index" UNIQUE, btree (oid)
    "pg_publication_rel_prrelid_prpubid_index" UNIQUE, btree (prrelid, prpubid)
    

Documentation: pg_publication_rel

Change history

This table has not been modified since it was added in PostgreSQL 10.

Examples

If creating a publication for one or more specified tables, an entry for each table associating it with the publication will be made in pg_publication_rel:

postgres=# CREATE PUBLICATION test_publication
             FOR TABLE foo;
CREATE PUBLICATION

postgres=# SELECT *, prrelid::regclass AS table FROM pg_publication_rel;
  oid  | prpubid | prrelid | table 
-------+---------+---------+-------
 16394 |   16393 |   16390 | foo
(1 row)

Categories

Logical replication, Replication, System catalogue

See also

pg_publication, pg_publication_namespace, pg_publication_tables, pg_subscription_rel