[SOLVED] Cannot create a conditional publication based on a user-defined type on PostgreSQL 15

Cannot create a conditional publication based on a user-defined type on PostgreSQL 15

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?

Solution

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.