ClickHouse Managed Postgres migrations FAQ

​

I’m seeing an “invalid input value for enum” error during replication

ALTER TYPE your_enum_type ADD VALUE 'new_value';

​

I’m seeing a unique constraint or check constraint violation error during replication

• Unique constraints: We apply changes as batched MERGE/UPSERT operations that write the latest value per primary key, so the order of operations for a given key may not match the ordering the unique index expects. This can temporarily violate a UNIQUE constraint within the transaction, even though the constraint holds once the MERGE completes. Postgres cannot defer unique index checks the way it defers foreign keys, so there is no way to postpone the check to the end of the transaction. This does not affect data consistency: row identity is defined by the primary key, and the MERGE logic is keyed on that same primary key.
• Check constraints: A check constraint on the target may be stricter than the source, or an intermediate state during a batched MERGE may not satisfy the constraint even though the final state does. As with unique constraints, this does not affect data consistency, since the MERGE is keyed on the primary key that defines row identity.

-- Drop a unique constraint
ALTER TABLE your_table DROP CONSTRAINT your_constraint_name;

-- Drop a check constraint
ALTER TABLE your_table DROP CONSTRAINT your_check_constraint_name;

SELECT conname, conrelid::regclass, contype
FROM pg_constraint
WHERE conname = 'your_constraint_name';

ALTER TABLE your_table ADD CONSTRAINT your_constraint_name UNIQUE (column1, column2);
ALTER TABLE your_table ADD CONSTRAINT your_check_constraint_name CHECK (column1 > 0);