Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

postgresql

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.

like image 291
Ayush Goyal Avatar asked Dec 04 '18 14:12

Ayush Goyal


People also ask

Is enum a data type in PostgreSQL?

PostgreSQL enum is the data type that was used in PostgreSQL to stored same type of values in column field, we can store same type of values using enum.

What is enum type in PostgreSQL?

Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data.

How do I change enum type in PostgreSQL?

Other than that, PostgreSQL doesn't support changing or deleting specific enum value. The workaround is the same as above: rename old type, create new and correct type, and delete old type.

Does Postgres support enums?

PostgreSQL supports enum types and composite types as database columns, and Npgsql supports reading and writing these. This allows you to seamlessly read and write enum and composite values to the database without worrying about conversions.


2 Answers

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

like image 54
S-Man Avatar answered Sep 18 '22 14:09

S-Man


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

like image 25
Daniel Alexandre Avatar answered Sep 20 '22 14:09

Daniel Alexandre