31.4. Column Lists
Each publication can optionally specify which columns of each table are replicated to subscribers. The table on the subscriber side must have at least all the columns that are published. If no column list is specified, then all columns on the publisher are replicated. See CREATE PUBLICATION for details on the syntax.
The choice of columns can be based on behavioral or performance reasons. However, do not rely on this feature for security: a malicious subscriber is able to obtain data from columns that are not specifically published. If security is a consideration, protections can be applied at the publisher side.
If no column list is specified, any columns added later are automatically replicated. This means that having a column list which names all columns is not the same as having no column list at all.
A column list can contain only simple column references. The order of columns in the list is not preserved.
Specifying a column list when the publication also publishes
FOR TABLES IN SCHEMA
is not supported.
For partitioned tables, the publication parameter
publish_via_partition_root
determines which column list
is used. If
publish_via_partition_root
is
true
, the root partitioned table's column list is used.
Otherwise, if
publish_via_partition_root
is
false
(the default), each partition's column list is used.
If a publication publishes
UPDATE
or
DELETE
operations, any column list must include the
table's replica identity columns (see
REPLICA IDENTITY
).
If a publication publishes only
INSERT
operations, then
the column list may omit replica identity columns.
Column lists have no effect for the
TRUNCATE
command.
During initial data synchronization, only the published columns are copied. However, if the subscriber is from a release prior to 15, then all the columns in the table are copied during initial data synchronization, ignoring any column lists.
31.4.1. Combining Multiple Column Lists
Warning
It is not supported to have a subscription comprising several publications
where the same table has been published with different column lists.
This means changing the column lists of the tables being subscribed could
cause inconsistency of column lists among publications, in which case
the
ALTER PUBLICATION
will be successful but later
the walsender on the publisher, or the subscriber may throw an error. In
this scenario, the user needs to recreate the subscription after adjusting
the column list or drop the problematic publication using
ALTER SUBSCRIPTION ... DROP PUBLICATION
and then add it
back after adjusting the column list.
31.4.2. Examples
Create a table
t1
to be used in the following example.
test_pub=# CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id)); CREATE TABLE
Create a publication
p1
. A column list is defined for
table
t1
to reduce the number of columns that will be
replicated. Notice that the order of column names in the column list does
not matter.
test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d); CREATE PUBLICATION
psql
can be used to show the column lists (if defined)
for each publication.
test_pub=# \dRp+
Publication p1
Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
postgres | f | t | t | t | t | f
Tables:
"public.t1" (id, a, b, d)
psql
can be used to show the column lists (if defined)
for each table.
test_pub=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
a | text | | |
b | text | | |
c | text | | |
d | text | | |
e | text | | |
Indexes:
"t1_pkey" PRIMARY KEY, btree (id)
Publications:
"p1" (id, a, b, d)
On the subscriber node, create a table
t1
which now
only needs a subset of the columns that were on the publisher table
t1
, and also create the subscription
s1
that subscribes to the publication
p1
.
test_sub=# CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id)); CREATE TABLE test_sub=# CREATE SUBSCRIPTION s1 test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1' test_sub-# PUBLICATION p1; CREATE SUBSCRIPTION
On the publisher node, insert some rows to table
t1
.
test_pub=# INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1'); INSERT 0 1 test_pub=# INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2'); INSERT 0 1 test_pub=# INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3'); INSERT 0 1 test_pub=# SELECT * FROM t1 ORDER BY id; id | a | b | c | d | e ----+-----+-----+-----+-----+----- 1 | a-1 | b-1 | c-1 | d-1 | e-1 2 | a-2 | b-2 | c-2 | d-2 | e-2 3 | a-3 | b-3 | c-3 | d-3 | e-3 (3 rows)
Only data from the column list of publication
p1
is
replicated.
test_sub=# SELECT * FROM t1 ORDER BY id; id | b | a | d ----+-----+-----+----- 1 | b-1 | a-1 | d-1 2 | b-2 | a-2 | d-2 3 | b-3 | a-3 | d-3 (3 rows)