CODEWITHSUNDEEP
postgresql
Ayush Goyal
Ayush Goyal

Reputation: 437

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

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.

Upvotes: 8

Views: 22003

Answers (2)

Daniel Alexandre
Daniel Alexandre

Reputation: 186

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:

Data Output

Upvotes: 6

S-Man
S-Man

Reputation: 23756

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

Upvotes: 11

Related Questions