sql - save enum in database as enum - Stack Overflow

admin管理员组

文章数量:1333450

I'd like to save entity with parameter "type" which need to be as ENUM in database.

CREATE TYPE type AS ENUM ('TYPE1','TYPE2','TYPE3');

My entity looks like:

@Column(name = "type")
@Enumerated(EnumType.STRING)
val type: TypeEnum

In previous version it was text in db.And i write migration like this

ALTER TABLE table1
ALTER COLUMN type TYPE type USING type::enum_type;

And getting error lumn [type] in table [table1]; found [type (Types#VARCHAR)], but expecting [smallint (Types#TINYINT)]

Some history: in migrationOne: type text NOT NULL

Than i have task to change text to enum ib db. I do this:

CREATE TYPE type_enum AS ENUM ('TYPE1','TYPE2','TYPE3');
ALTER TABLE table1
ALTER COLUMN type TYPE type_enum
    USING CASE
              WHEN type = 'TYPE1' THEN 'TYPE1'::type_enum
              WHEN type = 'TYPE2' THEN 'TYPE2'::type_enum
              WHEN type = 'TYPE3' THEN 'TYPE3'::type_enum
    END;

`

And have error when try to create entity. I think the problem when hibernate try to save entity with parameter type like string not like type_enum in db

I'd like to save entity with parameter "type" which need to be as ENUM in database.

CREATE TYPE type AS ENUM ('TYPE1','TYPE2','TYPE3');

My entity looks like:

@Column(name = "type")
@Enumerated(EnumType.STRING)
val type: TypeEnum

In previous version it was text in db.And i write migration like this

ALTER TABLE table1
ALTER COLUMN type TYPE type USING type::enum_type;

And getting error lumn [type] in table [table1]; found [type (Types#VARCHAR)], but expecting [smallint (Types#TINYINT)]

Some history: in migrationOne: type text NOT NULL

Than i have task to change text to enum ib db. I do this:

CREATE TYPE type_enum AS ENUM ('TYPE1','TYPE2','TYPE3');
ALTER TABLE table1
ALTER COLUMN type TYPE type_enum
    USING CASE
              WHEN type = 'TYPE1' THEN 'TYPE1'::type_enum
              WHEN type = 'TYPE2' THEN 'TYPE2'::type_enum
              WHEN type = 'TYPE3' THEN 'TYPE3'::type_enum
    END;

`

And have error when try to create entity. I think the problem when hibernate try to save entity with parameter type like string not like type_enum in db

• sql
• postgresql
• hibernate

Share Improve this question Follow edited Nov 20, 2024 at 14:44 Mankasss asked Nov 20, 2024 at 13:28 MankasssMankasss 2066 bronze badges 2

• Did you already have data in table1? – Chris Maurer Commented Nov 20, 2024 at 14:26
• Yes,i have data.But at this moment i using empty db and i get error when create entity – Mankasss Commented Nov 20, 2024 at 14:37

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Here's a step-by-step approach to address this:

Create the ENUM Type: Ensure that the ENUM type is created correctly in your database:

SQL

CREATE TYPE type AS ENUM ('TYPE1', 'TYPE2', 'TYPE3');

Update the Entity Mapping: Verify that your entity mapping is correct:

Java

@Column(name = "type")
@Enumerated(EnumType.STRING)
val type: TypeEnum

Write the Migration Script:

Here's a refined migration script to handle the type conversion and potential data loss:

SQL

ALTER TABLE table1
ALTER COLUMN type TYPE type
USING CASE 
    WHEN type = 'TYPE1' THEN 'TYPE1'::type
    WHEN type = 'TYPE2' THEN 'TYPE2'::type
    WHEN type = 'TYPE3' THEN 'TYPE3'::type
    ELSE NULL -- Handle unexpected values
END;

本文标签： sqlsave enum in database as enumStack Overflow