Hi,
I need to add a new column that will be an auto-increment column for an existing table.
For existing rows, the newly introduced column should generate an auto serial number.
I tried executing below query, but I am getting an error as “ERROR: cannot add column with constraints to a hypertable that has compression enabled
SQL state: 0A000”
ALTER TABLE table_name
ADD COLUMN id SERIAL
GENERATED BY DEFAULT AS IDENTITY
Is there any way by which I can add a new column without disturbing the hyper table/chunk?
If yes: how?
If No, what is the best way to add new coulmn?
Hi @Mohammed_Iliyas_pate, you can add the new column, the problem is with the generated by default as identity part.
serial is the “old” implementation of auto-generated unique values that has been part of Postgres for ages. However that is not part of the SQL standard.
To be more compliant with the SQL standard, Postgres 10 introduced the syntax using generated as identity.
More here.
As hypertables are partitioned into chunks that are independent tables, it’s not possible to use the identity as a unique index on hypertables. Take a look at the official docs here.
You need to combine time and other columns. This restriction is necessary to guarantee global uniqueness in the index.
Create a unique index as you normally would in PostgreSQL, using CREATE UNIQUE INDEX . Make sure to include all partitioning columns in the index. You can include other columns as well if needed.