Examples of Row Filters in Publications and Subscriptions

Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f | t | t | t | t | f Tables: "public.t1" "public.t2" WHERE (e = 99) Publication p3 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root ----------+------------+---------+---------+---------+-----------+---------- postgres | f | t | t | t | t | f Tables: "public.t2" WHERE (d = 10) "public.t3" WHERE (g = 10) ]]></programlisting></para> <para> <command>psql</command> can be used to show the row filter expressions (if defined) for each table. See that table <literal>t1</literal> is a member of two publications, but has a row filter only in <literal>p1</literal>. See that table <literal>t2</literal> is a member of two publications, and has a different row filter in each of them. <programlisting><![CDATA[ /* pub # */ \d t1 Table "public.t1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | not null | b | integer | | | c | text | | not null | Indexes: "t1_pkey" PRIMARY KEY, btree (a, c) Publications: "p1" WHERE ((a > 5) AND (c = 'NSW'::text)) "p2" /* pub # */ \d t2 Table "public.t2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- d | integer | | not null | e | integer | | | f | integer | | | Indexes: "t2_pkey" PRIMARY KEY, btree (d) Publications: "p2" WHERE (e = 99) "p3" WHERE (d = 10) /* pub # */ \d t3 Table "public.t3" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- g | integer | | not null | h | integer | | | i | integer | | | Indexes: "t3_pkey" PRIMARY KEY, btree (g) Publications: "p3" WHERE (g = 10) ]]></programlisting></para> <para> On the subscriber node, create a table <literal>t1</literal> with the same definition as the one on the publisher, and also create the subscription <literal>s1</literal> that subscribes to the publication <literal>p1</literal>. <programlisting> /* sub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c)); /* sub # */ CREATE SUBSCRIPTION s1 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s1' /* sub - */ PUBLICATION p1; </programlisting></para> <para> Insert some rows. Only the rows satisfying the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal> are replicated. <programlisting> /* pub # */ INSERT INTO t1 VALUES (2, 102, 'NSW'); /* pub # */ INSERT INTO t1 VALUES (3, 103, 'QLD'); /* pub # */ INSERT INTO t1 VALUES (4, 104, 'VIC'); /* pub # */ INSERT INTO t1 VALUES (5, 105, 'ACT'); /* pub # */ INSERT INTO t1 VALUES (6, 106, 'NSW'); /* pub # */ INSERT INTO t1 VALUES (7, 107, 'NT'); /* pub # */ INSERT INTO t1 VALUES (8, 108, 'QLD'); /* pub # */ INSERT INTO t1 VALUES (9, 109, 'NSW'); /* pub # */ SELECT * FROM t1; a | b | c ---+-----+----- 2 | 102 | NSW 3 | 103 | QLD 4 | 104 | VIC 5 | 105 | ACT 6 | 106 | NSW 7 | 107 | NT 8 | 108 | QLD 9 | 109 | NSW (8 rows) </programlisting> <programlisting> /* sub # */ SELECT * FROM t1; a | b | c ---+-----+----- 6 | 106 | NSW 9 | 109 | NSW (2 rows) </programlisting></para> <para> Update some data, where the old and new row values both satisfy the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>. The <command>UPDATE</command> replicates the change as normal. <programlisting> /* pub # */ UPDATE t1 SET b = 999 WHERE a = 6; /* pub # */ SELECT * FROM t1; a | b | c ---+-----+----- 2 | 102 | NSW 3 | 103 | QLD 4 | 104

This section provides practical examples of how row filters are defined in publications and how they affect data replication to subscribers. It demonstrates how to use psql commands \d to display table definitions and their associated publications, including any row filter expressions. It then shows how to create a subscription that subscribes to a publication with a row filter, and how only rows that satisfy the filter are replicated to the subscriber. The examples include inserting and updating data on the publisher and observing the replicated data on the subscriber based on the row filters.