CREATE SCHEMA alter_null_dist_key; SET search_path TO alter_null_dist_key; SET citus.next_shard_id TO 1720000; SET citus.shard_count TO 32; SET citus.shard_replication_factor TO 1; CREATE SEQUENCE dist_seq; CREATE TABLE null_dist_table(a bigint DEFAULT nextval('dist_seq') UNIQUE, "b" text, c bigint GENERATED BY DEFAULT AS IDENTITY); INSERT INTO null_dist_table("b") VALUES ('test'); SELECT create_distributed_table('null_dist_table', null, colocate_with=>'none', distribution_type=>null); NOTICE: Copying data from local table... NOTICE: copying the data has completed DETAIL: The local data in the table is no longer visible, but is still on disk. HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$alter_null_dist_key.null_dist_table$$) create_distributed_table --------------------------------------------------------------------- (1 row) -- add column ALTER TABLE null_dist_table ADD COLUMN d bigint DEFAULT 2; SELECT * FROM null_dist_table ORDER BY c; a | b | c | d --------------------------------------------------------------------- 1 | test | 1 | 2 (1 row) -- alter default, set to 3 ALTER TABLE null_dist_table ALTER COLUMN d SET DEFAULT 3; INSERT INTO null_dist_table("b") VALUES ('test'); SELECT * FROM null_dist_table ORDER BY c; a | b | c | d --------------------------------------------------------------------- 1 | test | 1 | 2 2 | test | 2 | 3 (2 rows) -- drop default, see null ALTER TABLE null_dist_table ALTER COLUMN d DROP DEFAULT; INSERT INTO null_dist_table("b") VALUES ('test'); SELECT * FROM null_dist_table ORDER BY c; a | b | c | d --------------------------------------------------------------------- 1 | test | 1 | 2 2 | test | 2 | 3 3 | test | 3 | (3 rows) -- cleanup the rows that were added to test the default behavior DELETE FROM null_dist_table WHERE "b" = 'test' AND a > 1; -- alter column type ALTER TABLE null_dist_table ALTER COLUMN d TYPE text; UPDATE null_dist_table SET d = 'this is a text' WHERE d = '2'; SELECT * FROM null_dist_table ORDER BY c; a | b | c | d --------------------------------------------------------------------- 1 | test | 1 | this is a text (1 row) -- drop seq column ALTER TABLE null_dist_table DROP COLUMN a; SELECT * FROM null_dist_table ORDER BY c; b | c | d --------------------------------------------------------------------- test | 1 | this is a text (1 row) -- add not null constraint ALTER TABLE null_dist_table ALTER COLUMN b SET NOT NULL; -- not null constraint violation, error out INSERT INTO null_dist_table VALUES (NULL, 2, 'test'); ERROR: null value in column "b" violates not-null constraint DETAIL: Failing row contains (null, 2, test). CONTEXT: while executing command on localhost:xxxxx -- drop not null constraint and try again ALTER TABLE null_dist_table ALTER COLUMN b DROP NOT NULL; INSERT INTO null_dist_table VALUES (NULL, 3, 'test'); SELECT * FROM null_dist_table ORDER BY c; b | c | d --------------------------------------------------------------------- test | 1 | this is a text | 3 | test (2 rows) -- add exclusion constraint ALTER TABLE null_dist_table ADD CONSTRAINT exc_b EXCLUDE USING btree (b with =); -- rename the exclusion constraint, errors out ALTER TABLE null_dist_table RENAME CONSTRAINT exc_b TO exc_b_1; ERROR: renaming constraints belonging to distributed tables is currently unsupported -- create exclusion constraint without a name ALTER TABLE null_dist_table ADD EXCLUDE USING btree (b with =); -- test setting autovacuum option ALTER TABLE null_dist_table SET (autovacuum_enabled = false); -- test multiple subcommands ALTER TABLE null_dist_table ADD COLUMN int_column1 INTEGER, DROP COLUMN d; SELECT * FROM null_dist_table ORDER BY c; b | c | int_column1 --------------------------------------------------------------------- test | 1 | | 3 | (2 rows) -- test policy and row level security CREATE TABLE null_dist_key_with_policy (table_user text); INSERT INTO null_dist_key_with_policy VALUES ('user_1'); SELECT create_distributed_table('null_dist_key_with_policy', null); NOTICE: Copying data from local table... NOTICE: copying the data has completed DETAIL: The local data in the table is no longer visible, but is still on disk. HINT: To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$alter_null_dist_key.null_dist_key_with_policy$$) create_distributed_table --------------------------------------------------------------------- (1 row) -- enable rls ALTER TABLE null_dist_key_with_policy ENABLE ROW LEVEL SECURITY; -- user_1 will be allowed to see the inserted row CREATE ROLE user_1 WITH LOGIN; GRANT ALL ON SCHEMA alter_null_dist_key TO user_1; GRANT ALL ON TABLE alter_null_dist_key.null_dist_key_with_policy TO user_1; CREATE POLICY table_policy ON null_dist_key_with_policy TO user_1 USING (table_user = current_user); -- user_2 will not be allowed to see the inserted row CREATE ROLE user_2 WITH LOGIN; GRANT ALL ON SCHEMA alter_null_dist_key TO user_2; GRANT ALL ON TABLE alter_null_dist_key.null_dist_key_with_policy TO user_2; CREATE POLICY table_policy_1 ON null_dist_key_with_policy TO user_2 USING (table_user = current_user); \c - user_1 - SELECT * FROM alter_null_dist_key.null_dist_key_with_policy; table_user --------------------------------------------------------------------- user_1 (1 row) \c - user_2 - SELECT * FROM alter_null_dist_key.null_dist_key_with_policy; table_user --------------------------------------------------------------------- (0 rows) -- postgres will always be allowed to see the row as a superuser \c - postgres - SELECT * FROM alter_null_dist_key.null_dist_key_with_policy; table_user --------------------------------------------------------------------- user_1 (1 row) -- cleanup SET client_min_messages TO ERROR; DROP SCHEMA alter_null_dist_key CASCADE; DROP ROLE user_1, user_2;