Search code examples
postgresqlreplicationpglogical

Using pgLogical row filter for jsonB column type

I am trying to replicate a table that contains jsonB column. I want to use row filtering to select only the rows that I need. How do I define the row filter for jsonB?

Table person:

        id                  serial,
        name                TEXT NOT NULL,
        details          JSONB NOT NULL,
        modified_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        PRIMARY KEY (name));

Sample data:

 id |  name   |     details     |          modified_at          
----+---------+-----------------+-------------------------------
  1 | person1 | {"city": "nyc"} | 2021-05-17 06:05:55.735086+00
  2 | person2 | {"city": "SF"}  | 2021-05-17 06:06:30.028065+00

I have tried the following and they don't work:

SELECT pglogical.replication_set_add_table(set_name:= 'replicate1', relation := 'person', row_filter:= details->>'city' = 'nyc');

The other option I tried:

SELECT pglogical.replication_set_add_table(set_name:= 'replicate1', relation := 'person', row_filter:= "details->>'city' = 'nyc'");
Solution

  • The solution can be found here: https://github.com/2ndQuadrant/pglogical/issues/332