Postgresql: How to use ENUM datatype?

Question

I am new to Postgresql, I am trying to create this table actually just following a similar mysql table. But I keep getting this error for ENUM() Below is the query for creating the table structure:

CREATE TABLE IF NOT EXISTS gkb_users (
id bigint NOT NULL,
userid character varying(50) NOT NULL DEFAULT '',
password character varying(50) NOT NULL DEFAULT '',
firstname text NOT NULL,
middlename text NOT NULL,
lastname text NOT NULL,
email character varying(100) NOT NULL DEFAULT '',
gender enum('Male','Female') NOT NULL,
dob date NOT NULL,
mobile character varying(10) NOT NULL DEFAULT '',
telephone character varying(15) NOT NULL DEFAULT '',
city character varying(50) NOT NULL DEFAULT '',
address text NOT NULL,
shippingaddress text NOT NULL,
PIN character varying(255) NOT NULL,
shipping_PIN character varying(255) NOT NULL,
area character varying(255) NOT NULL,
shipping_area character varying(255) NOT NULL,
previouscart text NOT NULL,
updatedon timestamp(0) NOT NULL,
is_deleted enum('0','1') NOT NULL
);

Any help will be greatly appreciated. Thanks

Better use a proper check constraint, not an enum (and you should read the manual on how to actually use an enum, where [in the manual](http://www.postgresql.org/docs/current/static/sql-commands.html) did you find that syntax?) — , Jul 20 '16 at 10:42
How would I set it in phppgadmin? There is no check datatype — DEV, Jul 21 '16 at 03:48
a check constraint is not a "datatype". It's a constraint. You [add it](https://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS) in the `CREATE TABLE` statement or with an ALTER TABLE statement just like any other constraint. You should really learn Postgres' [SQL statements](https://www.postgresql.org/docs/current/static/sql-commands.html) rather then relying on some GUI interface to build your data model. — , Jul 21 '16 at 05:41
So in this case, for say `is_deleted` it would be `is_deleted numeric CONSTRAINT deleted CHECK (0 || 1)` ? — DEV, Jul 21 '16 at 05:58
`||` is to concatenate strings. You would use something like `check (is_delete in (0,1))`. Better: use a proper `boolean` — , Jul 21 '16 at 06:00
Cool thanks and what about when it comes to Gender? Is it better to use character varying or just character? — DEV, Jul 21 '16 at 06:18
There is no difference between `text` and `varchar`. Please read the manual: https://www.postgresql.org/docs/current/static/datatype-character.html — , Jul 21 '16 at 06:32
Does this answer your question? [Postgres ENUM data type or CHECK CONSTRAINT?](https://stackoverflow.com/questions/10923213/postgres-enum-data-type-or-check-constraint) — Channa, Mar 21 '21 at 06:26

score 7 · Answer 1 · edited Jul 20 '16 at 10:42

ENUM is a user-defined datatype. You can use CREATE TYPE syntax to create your enum and then use it in the schema to create table.

CREATE TYPE your_enum2 AS ENUM('0','1');
CREATE TYPE your_enum1 AS ENUM('male','female');

Followed by the CREATE TABLE statement,

 CREATE TABLE IF NOT EXISTS gkb_users (
 id bigint NOT NULL,
 userid character varying(50) NOT NULL DEFAULT '',
 password character varying(50) NOT NULL DEFAULT '',
 firstname text NOT NULL,
 middlename text NOT NULL,
 lastname text NOT NULL,
 email character varying(100) NOT NULL DEFAULT '',
 gender your_enum1 NOT NULL,
 dob date NOT NULL,
 mobile character varying(10) NOT NULL DEFAULT '',
 telephone character varying(15) NOT NULL DEFAULT '',
 city character varying(50) NOT NULL DEFAULT '',
 address text NOT NULL,
 shippingaddress text NOT NULL,
 PIN character varying(255) NOT NULL,
 shipping_PIN character varying(255) NOT NULL,
 area character varying(255) NOT NULL,
 shipping_area character varying(255) NOT NULL,
 previouscart text NOT NULL,
 updatedon timestamp(0) NOT NULL,
 is_deleted your_enum2 NOT NULL
 );

Refer postgresql docs https://www.postgresql.org/docs/current/static/datatype-enum.html for more information on enum creation and usage.

It is giving me an error while attempting to insert into the table via phppgadmin. CREATE TYPE error — DEV, Jul 21 '16 at 03:22

score 5 · Answer 2 · answered Jul 21 '16 at 06:06

You don't need an enum for this (and I personally think one never needs an enum - but that's off topic).

You should either implement this as a check constraint:

CREATE TABLE IF NOT EXISTS gkb_users 
(
  id                bigint NOT NULL,
  userid            varchar(50) NOT NULL DEFAULT '',
  password          varchar(50) NOT NULL DEFAULT '',
  firstname         text NOT NULL,
  middlename        text NOT NULL,
  lastname          text NOT NULL,
  email             varchar(100) NOT NULL DEFAULT '',
  gender            text NOT NULL,
  dob               date NOT NULL,
  mobile            varchar(10) NOT NULL DEFAULT '',
  telephone         varchar(15) NOT NULL DEFAULT '',
  city              varchar(50) NOT NULL DEFAULT '',
  address           text NOT NULL,
  shippingaddress   text NOT NULL,
  pin               varchar(255) NOT NULL,
  shipping_pin      varchar(255) NOT NULL,
  area              varchar(255) NOT NULL,
  shipping_area     varchar(255) NOT NULL,
  previouscart      text NOT NULL,
  updatedon         timestamp(0) NOT NULL,
  is_deleted        integer NOT NULL,
  constraint check_gender check (gender in ('Male', 'Female')), 
  constraint check_deleted flag check (is_deleted in (0,1))
)

However, for is_delete should better be a proper boolean column - then you also don't need a check constraint for that column.

Postgres - like many other DBMS - is case sensitive when comparing strings. So with the above constraint you won't be able to store 'male' into the gender column.

Unrelated but: if you were assuming that varchar(255) has some magic performance benefits compared to e.g. varchar(300) then you are wrong. The maximum length of a varchar column does not influence the performance or the space requirements when storing the values.

Postgresql: How to use ENUM datatype?

2 Answers2

Linked