Return to Answer

The basic misunderstanding is that you assume variable substitution for an SQL utility command like ALTER TABLE, which is not implemented.

Equally 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:

• Error when setting n_distinct using a plpgsql variable

Hence, you have to concatenate the statement before you execute it - like @Abelisto already hinted. But you don't strictly need a variable, you can concatenate 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 also not strictly necessary. And since it's a default value for a uuid column, casting the string literal back to type uuid isn't required, either. Postgres will derive that from the column type automatically.

Related:

• CREATE SEQUENCE using expressions with psql variables for parameters

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:

• Error when setting n_distinct using a plpgsql variable

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:

• CREATE SEQUENCE using expressions with psql variables for parameters

The basic misunderstanding is that you assume variable substitution for an SQL utility command like ALTER TABLE, which is not implemented.

Equally 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:

• Error when setting n_distinct using a plpgsql variable

Hence, you have to concatenate the statement before you execute it - like @Abelisto already hinted. But you don't strictly need a variable, you can concatenate 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 also not strictly necessary. And since it's a default value for a uuid column, casting the string literal back to type uuid isn't required, either. Postgres will derive that from the column type automatically.

Related:

• CREATE SEQUENCE using expressions with psql variables for parameters

The basic misunderstanding is that you assume variable substitution for an SQL utility command like ALTER TABLE, which is not implemented.

Equally 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:

• Error when setting n_distinct using a plpgsql variable

Hence, you have to concatenate the statement before you execute it - like @Abelisto already hinted. But you don't strictly need a variable, you can concatenate 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 also not strictly necessary. And since it's a default value for a uuid column, casting the string literal back to type uuid isn't required, either. Postgres will derive that from the column type automatically.

Related:

• CREATE SEQUENCE using expressions with psql variables for parameters

The basic misunderstanding is that you assume variable substitution for an SQL utility command like ALTER TABLE, which is not implemented.

Equally 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:

• Error when setting n_distinct using a plpgsql variable

Hence, you have to concatenate the statement before you execute it - like @Abelisto already hinted. But you don't strictly need a variable, you can concatenate 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 also not strictly necessary. And since it's a default value for a uuid column, casting the string literal back to type uuid isn't required, either. Postgres will derive that from the column type automatically.

Related:

• CREATE SEQUENCE using expressions with psql variables for parameters

The basic misunderstanding is that you assume variable substitution for an SQL utility command like ALTER TABLE, which is not implemented.

Equally 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:

• Error when setting n_distinct using a plpgsql variable

Hence, you have to concatenate the statement before you execute it - like @Abelisto already hinted. But you don't strictly need a variable, you can concatenate 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 also not strictly necessary. And since it's a default value for a uuid column, casting the string literal back to type uuid isn't required, either. Postgres will derive that from the column type automatically.

Related:

• CREATE SEQUENCE using expressions with psql variables for parameters