-1

I am trying to publicate the cnt_title table using PostgreSQL Logical Replication. Note that I only want to share the product titles.

create publication cnt_publication_for_stk for table 
cnt_title where (doc_type = 'PRODUCT');

What I have found is a syntax error saying User-defined types are not allowed. This is consistent with the idea of docs_type being a column of custom type tdoc, defined as

create type tdoc as enum (
  'ARTICLE', 
  'PRODUCT', 
  'VOUCHER' 
);

Is there any work around for this expression?

coterobarros
  • 941
  • 1
  • 16
  • 25

2 Answers2

1

You should use a lookup table instead of an enum type. That is, use a table:

CREATE TABLE doctype (
   id smallint PRIMARY KEY,
   name text UNIQUE NOT NULL
);

INSERT INTO doctype VALUES
   (1, 'ARTICLE'),
   (2, 'PRODUCT'),
   (3, 'VOUCHER');

and use the primary key instead of the enum value.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • This is basically what a enum type is behind the scenes, so I imagine this limitation will disappear in future versions – coterobarros Mar 29 '23 at 13:48
  • Waiting for this feature for son long and finally does now work with enums! :-) – coterobarros Mar 29 '23 at 13:49
  • 1
    This limitation may never be lifted. How can you guarantee that user-defined types are the same on both sides? On the other hand, postgres_fdw allows you to define what you want to push down... – Laurenz Albe Mar 29 '23 at 13:52
0

According to the docs, row filter expressions cannot contain user-defined functions, operators, types, and collations, system column references or non-immutable built-in functions.

In addition, if a publication publishes UPDATE or DELETE operations, the row filter WHERE clause must contain only columns that are covered by the replica identity

David Jones
  • 2,879
  • 2
  • 18
  • 23