0

I have an existing type example_type with values a,b, and c.

Up until now, there was a nullable column that used the above type. (default value of that column was null)

I am trying to write a migration that should eventually set the default value for that column to be example_type.c.

First of all, I executed the following query:

ALTER TABLE example_table 
ALTER COLUMN example_column 
SET DATA TYPE example_type;

Now I am trying to set the default value of that type to be c with:

ALTER TYPE example_type 
SET DEFAULT example_type.c;

Which fails on ERROR: syntax error at or near "DEFAULT"

I looked around ALTER TYPE docs and found nothing related to setting a default value.

Is there another way to approach this?

EDIT:

I also tried to set a default value for the column itself with:

ALTER TABLE example_table
ALTER COLUMN example_column 
SET DEFAULT example_type.c;

which resulted in : ERROR: cannot use column reference in DEFAULT expression

The type was originally created with:

CREATE TYPE example_type AS ENUM('a', 'b', 'c');
10
  • You can only set defaults for columns or domains, not for types.
    – user1822
    Mar 6, 2023 at 14:25
  • @a_horse_with_no_name I also tried to set a default for the column itself and it failed for a different reason, see edit. Mar 6, 2023 at 14:28
  • Well, as the error message says: a default value must be a constant. You can't reference other columns for defaults.
    – user1822
    Mar 6, 2023 at 14:30
  • meaning there's no way to set a column's default value to a type's value? The only workaround is putting the actual const value of example_type.c? Mar 6, 2023 at 14:32
  • A type has no value, so your question doesn't really make sense to me.
    – user1822
    Mar 6, 2023 at 14:43

1 Answer 1

1

The correct syntax to set the default value for an enum type is

ALTER TABLE tab ALTER col SET DEFAULT CAST ('c' AS example_type);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.