recentpopularlog in

postgres

« earlier   
Instant realtime GraphQL on Postgres | Hasura GraphQL Engine
Instant realtime GraphQL APIs on any Postgres application, existing or new
graphql  postgres  postgresql  backend  heroku  database 
3 days ago by zbuttram
Altering PostgreSQL columns from one enum to another – Thoughts, etc.
Altering the type of a column in postgres from one enum type to another requires an intermediate cast to text. Additionally, if there is a default value for the field, it cannot be altered and instead must be dropped and re-added as the new type.

Simple case: New type contains all old-type values
In the simple version, the new enum type contains (at least) all of the same labels as the old one. For instance, as with these two:

CREATE TYPE old_enum AS ENUM ('a', 'b', 'c', 'd');
CREATE TYPE new_enum AS ENUM ('a', 'b', 'c', 'd', 'e');
No default on column
If a table has an old_enum column and we want to turn it into a new_enum one, with no default value in place on the column, we can use the following command:

ALTER TABLE table_name
ALTER COLUMN column_name
SET DATA TYPE new_enum
USING column_name::text::new_enum;

The USING expression casts the current value of column_name to text, and then to new_enum. This works because every allowed value of the first enum type exists in the second.

With a default value
This case is not significantly more difficult to deal with. If there is a default value on the column, we simply remove it before altering the enum type of the column, and then add a new one when we’re done:

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT,
ALTER COLUMN column_name
SET DATA TYPE new_enum
USING column_name::text::new_enum,
ALTER COLUMN column_name SET DEFAULT 'a';


....
postgres  postgresql  sql  change  alter  table  column  data  type  using  enum  cast  to  text 
5 days ago by theskett
PostgreSQL Change Column Type: Step-by-Step Examples
ALTER TABLE assets
ALTER COLUMN asset_no TYPE INT USING asset_no::integer;

but this doesn't work, to convert between enums; must cast to text, then new enum.
postgres  postgresql  alter  table  column  type  using  cast 
5 days ago by theskett

Copy this bookmark:





to read