How to substitute a variable when creating a check constraint?

Question

I need to add a required field for newly added rows. However, it is undesirable to set the default value for old rows due to the large size of the table. I need to provide an automated script that will do this. I tried this, but it does not work:

do $$ declare max_id int8; begin select max(id) into max_id from transactions; alter table transactions add constraint check_process_is_assigned check (id <= max_id or process_id is not null); end $$;

The id is always less than or equal to the max_id (assuming it is not null). What is the point of the logic? — Gordon Linoff
– Gordon Linoff, Commented Sep 15, 2020 at 23:54
What @GordonLinoff says and in addition from docs: "Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row (see Section 5.4.1). The system column tableoid may be referenced, but not any other system column." — Adrian Klaver
– Adrian Klaver, Commented Sep 16, 2020 at 0:23
@Gordon: The point, AIUI, is to create a CHECK constraint that enforces process_id IS NOT NULL only for future rows (bigger IDs than the current maximum). — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 16, 2020 at 1:11

Erwin Brandstetter · Accepted Answer · 2020-09-16 01:27:46Z

Utility commands like ALTER TABLE do not accept parameters. Only the basic DML commands SELECT, INSERT, UPDATE, DELETE do. See:

You need dynamic SQL like:

DO $do$ BEGIN EXECUTE format( 'ALTER TABLE transactions ADD CONSTRAINT check_process_is_assigned CHECK (id <= %s OR process_id IS NOT NULL)' , (SELECT max(id) FROM transactions) ); END $do$;

db<>fiddle here

This creates a CHECK constraint based on the current maximum id.

Maybe a NOT VALID constraint would serve better? That is not checked against existing rows:

ALTER TABLE transactions ADD CONSTRAINT check_process_is_assigned CHECK (process_id IS NOT NULL) NOT VALID;

But you do have to "fix" old rows that get updated in this case. (I.e. assign a value to process_id if it was NULL so far.) See:

Collectives™ on Stack Overflow

How to substitute a variable when creating a check constraint?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related