codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>31.4. Column Lists</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="logical-replication-row-filter.html" title="31.3. Row Filters" /><link rel="next" href="logical-replication-conflicts.html" title="31.5. Conflicts" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">31.4. Column Lists</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="logical-replication-row-filter.html" title="31.3. Row Filters">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="logical-replication.html" title="Chapter 31. Logical Replication">Up</a></td><th width="60%" align="center">Chapter 31. Logical Replication</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="logical-replication-conflicts.html" title="31.5. Conflicts">Next</a></td></tr></table><hr /></div><div class="sect1" id="LOGICAL-REPLICATION-COL-LISTS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">31.4. Column Lists <a href="#LOGICAL-REPLICATION-COL-LISTS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="logical-replication-col-lists.html#LOGICAL-REPLICATION-COL-LIST-EXAMPLES">31.4.1. Examples</a></span></dt></dl></div><p>3   Each publication can optionally specify which columns of each table are4   replicated to subscribers. The table on the subscriber side must have at5   least all the columns that are published. If no column list is specified,6   then all columns on the publisher are replicated.7   See <a class="xref" href="sql-createpublication.html" title="CREATE PUBLICATION"><span class="refentrytitle">CREATE PUBLICATION</span></a> for details on the syntax.8  </p><p>9   The choice of columns can be based on behavioral or performance reasons.10   However, do not rely on this feature for security: a malicious subscriber11   is able to obtain data from columns that are not specifically12   published.  If security is a consideration, protections can be applied13   at the publisher side.14  </p><p>15   If no column list is specified, any columns added later are automatically16   replicated. This means that having a column list which names all columns17   is not the same as having no column list at all.18  </p><p>19   A column list can contain only simple column references.  The order20   of columns in the list is not preserved.21  </p><p>22   Specifying a column list when the publication also publishes23   <a class="link" href="sql-createpublication.html#SQL-CREATEPUBLICATION-FOR-TABLES-IN-SCHEMA"><code class="literal">FOR TABLES IN SCHEMA</code></a>24   is not supported.25  </p><p>26   For partitioned tables, the publication parameter27   <a class="link" href="sql-createpublication.html#SQL-CREATEPUBLICATION-WITH-PUBLISH-VIA-PARTITION-ROOT"><code class="literal">publish_via_partition_root</code></a>28   determines which column list is used. If <code class="literal">publish_via_partition_root</code>29   is <code class="literal">true</code>, the root partitioned table's column list is30   used. Otherwise, if <code class="literal">publish_via_partition_root</code> is31   <code class="literal">false</code> (the default), each partition's column list is used.32  </p><p>33   If a publication publishes <code class="command">UPDATE</code> or34   <code class="command">DELETE</code> operations, any column list must include the35   table's replica identity columns (see36   <a class="xref" href="sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY"><code class="literal">REPLICA IDENTITY</code></a>).37   If a publication publishes only <code class="command">INSERT</code> operations, then38   the column list may omit replica identity columns.39  </p><p>40   Column lists have no effect for the <code class="literal">TRUNCATE</code> command.41  </p><p>42   During initial data synchronization, only the published columns are43   copied.  However, if the subscriber is from a release prior to 15, then44   all the columns in the table are copied during initial data synchronization,45   ignoring any column lists.46  </p><div class="warning" id="LOGICAL-REPLICATION-COL-LIST-COMBINING"><h3 class="title">Warning: Combining Column Lists from Multiple Publications</h3><p>47     There's currently no support for subscriptions comprising several48     publications where the same table has been published with different49     column lists.  <a class="xref" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><span class="refentrytitle">CREATE SUBSCRIPTION</span></a> disallows50     creating such subscriptions, but it is still possible to get into51     that situation by adding or altering column lists on the publication52     side after a subscription has been created.53    </p><p>54     This means changing the column lists of tables on publications that are55     already subscribed could lead to errors being thrown on the subscriber56     side.57    </p><p>58     If a subscription is affected by this problem, the only way to resume59     replication is to adjust one of the column lists on the publication60     side so that they all match; and then either recreate the subscription,61     or use <code class="literal">ALTER SUBSCRIPTION ... DROP PUBLICATION</code> to62     remove one of the offending publications and add it again.63    </p></div><div class="sect2" id="LOGICAL-REPLICATION-COL-LIST-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">31.4.1. Examples <a href="#LOGICAL-REPLICATION-COL-LIST-EXAMPLES" class="id_link">#</a></h3></div></div></div><p>64    Create a table <code class="literal">t1</code> to be used in the following example.65</p><pre class="programlisting">66test_pub=# CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));67CREATE TABLE68</pre><p>69    Create a publication <code class="literal">p1</code>. A column list is defined for70    table <code class="literal">t1</code> to reduce the number of columns that will be71    replicated. Notice that the order of column names in the column list does72    not matter.73</p><pre class="programlisting">74test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d);75CREATE PUBLICATION76</pre><p>77     <code class="literal">psql</code> can be used to show the column lists (if defined)78     for each publication.79</p><pre class="programlisting">80test_pub=# \dRp+81                               Publication p182  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root83----------+------------+---------+---------+---------+-----------+----------84 postgres | f          | t       | t       | t       | t         | f85Tables:86    "public.t1" (id, a, b, d)87</pre><p>88     <code class="literal">psql</code> can be used to show the column lists (if defined)89     for each table.90</p><pre class="programlisting">91test_pub=# \d t192                 Table "public.t1"93 Column |  Type   | Collation | Nullable | Default94--------+---------+-----------+----------+---------95 id     | integer |           | not null |96 a      | text    |           |          |97 b      | text    |           |          |98 c      | text    |           |          |99 d      | text    |           |          |100 e      | text    |           |          |101Indexes:102    "t1_pkey" PRIMARY KEY, btree (id)103Publications:104    "p1" (id, a, b, d)105</pre><p>106     On the subscriber node, create a table <code class="literal">t1</code> which now107     only needs a subset of the columns that were on the publisher table108     <code class="literal">t1</code>, and also create the subscription109     <code class="literal">s1</code> that subscribes to the publication110     <code class="literal">p1</code>.111</p><pre class="programlisting">112test_sub=# CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id));113CREATE TABLE114test_sub=# CREATE SUBSCRIPTION s1115test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'116test_sub-# PUBLICATION p1;117CREATE SUBSCRIPTION118</pre><p>119     On the publisher node, insert some rows to table <code class="literal">t1</code>.120</p><pre class="programlisting">121test_pub=# INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');122INSERT 0 1123test_pub=# INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');124INSERT 0 1125test_pub=# INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3');126INSERT 0 1127test_pub=# SELECT * FROM t1 ORDER BY id;128 id |  a  |  b  |  c  |  d  |  e129----+-----+-----+-----+-----+-----130  1 | a-1 | b-1 | c-1 | d-1 | e-1131  2 | a-2 | b-2 | c-2 | d-2 | e-2132  3 | a-3 | b-3 | c-3 | d-3 | e-3133(3 rows)134</pre><p>135     Only data from the column list of publication <code class="literal">p1</code> is136     replicated.137</p><pre class="programlisting">138test_sub=# SELECT * FROM t1 ORDER BY id;139 id |  b  |  a  |  d140----+-----+-----+-----141  1 | b-1 | a-1 | d-1142  2 | b-2 | a-2 | d-2143  3 | b-3 | a-3 | d-3144(3 rows)145</pre></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="logical-replication-row-filter.html" title="31.3. Row Filters">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="logical-replication.html" title="Chapter 31. Logical Replication">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="logical-replication-conflicts.html" title="31.5. Conflicts">Next</a></td></tr><tr><td width="40%" align="left" valign="top">31.3. Row Filters </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 31.5. Conflicts</td></tr></table></div></body></html>