Upgrading a Varchar Column to Enum Type in Postgresql

Upgrading a varchar column to enum type in postgresql

You need to define a cast to be used because there is no default cast available.

If all values in the varcharColumn comply with the enum definition, the following should work:

alter table foo 
  ALTER COLUMN varcharColumn TYPE enum_type using varcharColumn::enum_type;

I personally don't like enums because they are quite unflexible. I prefer a check constraint on a varchar column if I want to restrict the values in a column. Or - if the list of values changes often and is going to grow - a good old "lookup table" with a foreign key constraint.

How to alter Postgres column type from ENUM to VARCHAR

Yes, you can

alter table table_name alter column col_name  TYPE varchar;

Postgres Update column with Enum from string

If the text is identical to the enum member text, a cast to the enum type should be enough.

    SET kind = participations_migration.kind::participations_type

Upgrading an int column to enum type in postgresql

The USING clause is an expression to tell postgresql how to transform the value. This is an expression just as you might use in SELECT. So if you need to specify a mapping then you can just use a CASE statement.

alter table foo 
  alter bar type mood 
  using 
     case bar 
        when 0 then 'sad' 
        when 1 then 'ok' 
        when 10 then 'happy' 
     end :: mood;

How to modify add type enum table postgres?

No need for ALTER TABLE. Use the ALTER TYPE command on its own:

ALTER TYPE enum_type ADD VALUE 'review';

how to change type of column in postgres

You have to do it in two steps:

CREATE TYPE sub_type
   AS ENUM ('User', 'Organization');

ALTER TABLE subscriptions
   ALTER subscribable_type TYPE sub_type USING subscribable_type::sub_type;

How can I add a new value to an ENUM in Postgres without locking the table?

Approach 3, just add a new value to the enum:

ALTER TYPE animal_type_enum ADD VALUE 'snake';

---

If you frequently add or remove new lookup values, a separate lookup table is a much better choice.

Adding a new value is a simple INSERT operation that doesn't lock anything (especially not the table referencing the lookup table).

While the foreign key checks do add some overhead, they shouldn't matter that much (assuming the FK column is properly indexed) unless you do bulk INSERTs or DELETEs very frequently.

For single row INSERTs or DELETEs (or only "hundreds" of rows) you probably won't even notice the overhead of the FK lookup - especially if the lookup table is small and only contains a few rows.

Postgres - UPDATE with CASE won't work on enum column

you have to explicitly cast the type to the enum type :

UPDATE fruits
SET fruit_type = CASE WHEN event_date = '2019-01-01' THEN 'apple'::enum_fruits_fruit_type END;

I think when you directly set the column , postgresql does it for you automatically but it comes to conditions, it can't do it .

---

Related Topics