How can i create a table with one column of enum datatype in postgresql database?

Question

How can I create a table with one column of enum datatype in PostgreSQL database?

Table name: Employee

Columns:

ID: Integer

Name: ENUM

Below is the query but not sure it is correct or not.

CREATE TYPE Name AS ENUM();

CREATE TABLE IF NOT EXISTS Employee(
    ID integer NOT NULL,
    Name DEFAULT NULL,
    CONSTRAINT "Employee_pkey" PRIMARY KEY (id)
 );

Can someone please help.

S-Man · Accepted Answer · 2018-12-04 14:56:01Z

12

1. In the line

Name DEFAULT NULL,

you either forgot the name of the column or defining the columns as enum type:

myname Name DEFAULT NULL, -- add column name

or

Name Name DEFAULT NULL, -- add enum type

2. Because "Name" is a keyword in Postgres you also have to change the type name. Otherwise it will not work.

3. However: Your enum type has no values. So you are not able to insert any value. You have to add some enum values:

CREATE TYPE name_type AS ENUM('name1', 'name2');

Final:

CREATE TYPE name_type AS ENUM('name1', 'name2');

CREATE TABLE Employee2(
    ID integer, 
    myname name_type
);

demo: db<>fiddle

edited Dec 4, 2018 at 14:56

answered Dec 4, 2018 at 14:45

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user330315 Over a year ago

For some strange reason... that's because name is a keyword: postgresql.org/docs/current/sql-keywords-appendix.html although a non-reserved one, so it should work

S-Man Over a year ago

Thanks for the hint. I already found it as well and changed the text :)

Ayush Goyal Over a year ago

Hey - Thanks for the explanation :) Here, 'myname' is 'Name' according to my column name? And, I don't want the default value. So can I write like : CREATE TYPE name_type AS ENUM();

S-Man Over a year ago

yes mynameis the name of the column, name_typeis the type (the enum type) similar to integer or whatelse. Check out the example link :)

Ayush Goyal Over a year ago

Hey - can you please clarify one more thing :) By default, if I want to assign NULL value then is it compulsory to give default values here - ('name1', 'name2') can I write like : CREATE TYPE name_type AS ENUM(NULL); or CREATE TYPE name_type AS ENUM(); CREATE TABLE Employee2( ID integer NOT NULL, myname name_type DEFAULT NULL );

|

Daniel Alexandre · Accepted Answer · 2018-12-04 15:10:36Z

6

Here you got a simple example, consider to add a name to your enum column at Employee Table, and add some values to your enum.

 CREATE TYPE NameEnum AS ENUM('Jony','Bala','Mark');

 CREATE TABLE IF NOT EXISTS Employee(
    ID integer NOT NULL,
    name NameEnum DEFAULT NULL,
    CONSTRAINT "Employee_pkey" PRIMARY KEY (id)
 );

 Insert into Employee(ID,name)
 Values(1,  (SELECT enum_first(NULL::NameEnum)))

 Select * from Employee
 Output:

answered Dec 4, 2018 at 15:10

Daniel Alexandre

1869 bronze badges

2 Comments

Ayush Goyal Over a year ago

Hey Danial - Thanks for the explanation. By default, if I want to assign NULL value then is it compulsory to give default values here - ('Jony','Bala','Mark') can I write like : CREATE TYPE NameEnum AS ENUM(NULL); or CREATE TYPE NameEnum AS ENUM(); CREATE TABLE IF NOT EXISTS Employee( ID integer NOT NULL, name NameEnum DEFAULT NULL, CONSTRAINT "Employee_pkey" PRIMARY KEY (id) ); please clarify.

Daniel Alexandre Over a year ago

Well you actually have a command to add values to the enum, so if you consider this answer correct please mark it like that. Here you have an addition to the code that i gave: ALTER TYPE NameEnum ADD VALUE 'new_value'; Insert into Employee(ID,name) Values(2, (SELECT enum_last(NULL::NameEnum))) Select * from Employee Output: 1 : Jony ; 2 : new_value

Collectives™ on Stack Overflow

How can i create a table with one column of enum datatype in postgresql database?

2 Answers 2

9 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related