cybertec-postgresql
Unique constraint on squeeze table
Hi,
This query is regarding the README information that says make sure that your table has either primary key or unique constraint.
postgres=# create table test ( id int, age int NOT NULL );
CREATE TABLE
postgres=# alter table test add constraint unique_const UNIQUE (id);
ALTER TABLE
postgres=# insert into test values (generate_series(1, 5000), 30);
INSERT 0 5000
postgres=# SELECT squeeze.squeeze_table('public', 'test');
ERROR: Table "public"."test" has no identity index
The code expects the table to either have a replica identity index or a primary key and does not check for the unique constraint. I am a little confused about the "primary key or unique constraint" mention in the README.
Why a table to be squeezed requires a primary key or unique constraint, is that your question?
My question is can pg_squeeze work when the table has a unique constraint and no primary key ?
Hi, As per my understanding, pg_squeeze expects the table to have a primary key or a replica identity configured. I was wondering if the below line in the README needs an update ?
**Register table for regular processing**
First, make sure that your table has either primary key or unique constraint.
First, make sure that your table has either primary key or replica identity.
Also, one more observation is the README has the description for skip_analyze but is not part of any function definitions.
The problem is that the id column is nullable. You fix your test this way:
ALTER TABLE test ALTER COLUMN id SET NOT NULL;
ALTER TABLE test REPLICA IDENTITY USING INDEX "unique_const";
I'll adjust the documentation. Thanks.
skip_analyze is a column of the squeeze.tables table, not a function argument.