Datatype ENUM in PostgreSQL

PostgreSQL - related issues
Post Reply
Percy
Posts: 5
Joined: Mon Nov 12, 2012 2:51 pm

Datatype ENUM in PostgreSQL

Post by Percy » Wed Jun 12, 2013 4:21 pm

Hello dear forum,

in PostgreSQL to use the data type 'ENUMERATION', one has to first create specific enumeration-types like:
CREATE TYPE enum_colour AS ENUM ('red', 'green', 'blue', '...');

to be able to use the created enumeration as a column data type, e.g. like:

CREATE TABLE car (
model varchar(255),
colour enum_colour
);


But when adding a datatype with 'presicion: ENUMERATION' to a DbSchema project via "Menu->Databases->Database settings->PostgreSQL->Data Types", the resulting SQL code will look like:

CREATE TABLE car (
model varchar(255),
colour enum_colour('red', 'green', 'blue', '...')
);

This syntax will not work with PostgreSQL (tested for version 8.4, but documentation for newer releases notes nothing about a changed syntax).

---

One could work around, by leaving the 'List of values' empty (in the form for the column configuration), which (at least with DBSchema version 6.4.0 build 3790) then generates:

CREATE TABLE car (
model varchar(255),
colour enum_colour
);
[Note that the enum values AND the parentheses are omitted then]

... and by additionally prepending the 'CREATE TYPE ...' stuff manually to the generated SQL code, this results in a valid syntax.
Of course this brings the drawback to remember and prepend this manually every time you generated your SQL code.

Is there any way to avoid the extra step done manually?

--
Best regards,
Percy

mithcd143
Posts: 27
Joined: Fri Jun 14, 2013 3:52 pm

Re: Datatype ENUM in PostgreSQL

Post by mithcd143 » Fri Jun 14, 2013 3:57 pm

This one might be tough. Have you tried to create a support ticket about this already?

donose.mihai
Posts: 470
Joined: Thu Apr 07, 2011 11:05 am

Re: Datatype ENUM in PostgreSQL

Post by donose.mihai » Wed Jun 26, 2013 6:37 am

Hello,


Seems their JDBC driver doesn’t support the enum type like MySql for example, you can not map it. Therefore if you want to use it you must use like in your example until the guys from Postgres will implement this feature into the JDBC:

This will work in DbSchema as well:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);

Please let me know if I can help you further.

tchen99_98
Posts: 5
Joined: Thu Oct 19, 2017 2:20 pm

Re: Datatype ENUM in PostgreSQL

Post by tchen99_98 » Thu Oct 19, 2017 3:49 pm

Does anyone has any update on how DbSchema support Enum type?

I have a similar needs to add a column with Enum Type

thanks

Post Reply