PostgreSQL – How to Set Column DEFAULT Using Variable Value

alter-tabledefault valuedynamic-sqlplpgsqlpostgresql

In a script for setting up a database which uses random UUIDs for primary keys, I have:

CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT);
CREATE TABLE beta (aref UUID REFERENCES alpha (id));

INSERT INTO alpha (value) VALUES (42);

Then over a foreign key, to have that record in "alpha" be the default target for insertions into the "beta" table table:

DO $$
    DECLARE l_id UUID;
    BEGIN
        SELECT alpha.id FROM alpha INTO l_id;
        ALTER TABLE beta ALTER COLUMN aref SET DEFAULT l_id;
    END;
$$ LANGUAGE plpgsql;

ERROR:  column "l_id" does not exist
CONTEXT:  SQL statement "ALTER TABLE beta ALTER COLUMN aref SET DEFAULT l_id"
PL/pgSQL function inline_code_block line 1 at SQL statement

How do I use the value of a variable as the default value for a column?

Best Answer

You seem to assume variable substitution for an SQL utility command like ALTER TABLE, but that's not implemented.

Also not possible to pass values for utility commands to EXECUTE with the USING clause in plpgsql.
Detailed explanation for both in this related answer on SO:

Concatenate the statement, then execute it - like @Abelisto hinted. But you don't need a variable, you can concatenate the lookup value directly:

DO $$
BEGIN
EXECUTE format('ALTER TABLE beta ALTER COLUMN aref SET DEFAULT %L'
             , (SELECT a.id::text FROM alpha a));
END $$;

The SELECT must return a single value.
I cast to text explicitly, which is not strictly necessary. Casting the string literal to the target type uuid isn't required, either. Postgres will derive that from the column type automatically.

Related:

Related Question