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, Sep 15 '20 at 23:54
What @GordonLinoff says and in addition from [docs](https://www.postgresql.org/docs/current/sql-createtable.html): "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, Sep 16 '20 at 00: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, Sep 16 '20 at 01:11

Erwin Brandstetter · Accepted Answer · 2020-09-16T01:27:46.003

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:

How to substitute a variable when creating a check constraint?

1 Answers1

Linked