Question 89185 in PostgreSQL Question posted in
The official documentation can be found here.

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?

Tags:

2

Answers


  1. 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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search