Alter table and add new column if already exists

CrateDB
1

Hello,

It seems that the IF NOT EXISTS condition is not supported in the ALTER TABLE statement

Is there an alternative to execute such statement as below?

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS month AS date_trunc(‘month’, timestamp) GENERATED ALWAYS;

2

Hi @Alin_Mihut

No, there is no direct alternative other than manual checking beforehand.
If you see this as an important feature feel free to open a feature request in crate/crate:

Anyway it is not possible to add generated columns to a table that already holds data:

CREATE TABLE t001 (ts TIMESTAMP);
-- CREATE OK, 1 row affected (0.103 sec)
ALTER TABLE t001 ADD COLUMN ts_g GENERATED ALWAYS AS date_trunc('day',ts);
-- ALTER OK, -1 rows affected (0.076 sec)
INSERT INTO t001 (ts) VALUES (now());
-- INSERT OK, 1 row affected (0.055 sec)
ALTER TABLE t001 ADD COLUMN ts_g2 GENERATED ALWAYS AS date_trunc('week',ts);
-- UnsupportedFeatureException[Cannot add a generated column to a table that isn't empty]

best regards
Georg

1 Like