Rohit Bajaj
asked on
Enum in postgreSQL
Hi,
I have to create the following table in postgreSQL :
I got an error : SQL Error [42704]: ERROR: type "enum" does not exist
I saw on postgreSQL documentation page : https://www.postgresql.org/docs/9.2/datatype-enum.html
That one first needs to create an enum type in postgreSQL.
This is very different from MYSQL where you can just create a column of enum type by specifying the values...
I have the following questions..
1) Since the kycType in my java classes is an enum. And it has a fixed set of values should i create an ENUM in postgre for it or just create a varchar for it ?
2) Because postgreSQL uses enum types in a different way than MySQL does what impact it has if i create the above kycType as an enum type and then use it in the table ?
Thanks
I have to create the following table in postgreSQL :
create table rbl_kyc_profile (
id int8 not null,
userid varchar(50) not null,
kyctype enum ('PAPER','E_KYC','MINIMAL','SHORTFALL')
)
I got an error : SQL Error [42704]: ERROR: type "enum" does not exist
I saw on postgreSQL documentation page : https://www.postgresql.org/docs/9.2/datatype-enum.html
That one first needs to create an enum type in postgreSQL.
This is very different from MYSQL where you can just create a column of enum type by specifying the values...
I have the following questions..
1) Since the kycType in my java classes is an enum. And it has a fixed set of values should i create an ENUM in postgre for it or just create a varchar for it ?
2) Because postgreSQL uses enum types in a different way than MySQL does what impact it has if i create the above kycType as an enum type and then use it in the table ?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
slightwv's (good) point here reminds me of something else. If your application requires any translations then dbms enforced enums are a pain in the backside, and indeed using a set of tables to define lookup values (with translations if needed) is an approach that provides great flexibility. As always "it depends" on what you want to achieve (and how much time/money you have :)
ps:
slightwv is a DBA, I am not. We may disagree on how much of an application's logic should be enforced through a dbms