CODEWITHSUNDEEP
database-designenumspsql
Aishwarya V
Aishwarya V

Reputation: 63

Can we create ENUM on INTEGER column in PSQL?

I'm using PSQL and I'm trying to create a table with columns REQ_TYPE and RESP_STATUS. And I've decided to go with ENUM to avoid invalid entries in the table.

I've used this Query to create ENUM for REQ_TYPE successfully.

CREATE TYPE RequestType AS ENUM('GET','POST','PUT','DELETE','PATCH');

But for RESP_STATUS, when I tried the below query,

CREATE TYPE ResponseStatus AS ENUM(200,201,202,204,301,302,304,400,401,403,404,405,413,415,429,500);

I got the response

ERROR: syntax error at or near "200"

I'm sure that

CREATE TYPE ResponseStatus AS ENUM('200','201','202');

would solve my problem. But I just want to keep RESP_STATUS as INTEGER.

Can I create ENUM on INTEGER? If yes, how?

Upvotes: 0

Views: 1079

Answers (2)

IMSoP
IMSoP

Reputation: 97718

An enum is a set of arbitrary labels, equal only to themselves; they're not a constraint on values of a particular type. Think of the strings in the type definition as more like column names or programming language constants than actual values.

You might instead be looking for a domain (see documentation for CREATE DOMAIN), which would allow you to effectively "sub-type" integer with a list of allowed values:

CREATE DOMAIN http_status AS integer 
CHECK (
    VALUE IN 
    (200,201,202,204,301,302,304,400,401,403,404,405,413,415,429,500)
)

However, as suggested in Mike Sherrill's answer, it might be more appropriate to use a normal integer column with a foreign key constraint pointing to a list of known HTTP status codes, as this allows:

  • Details about the HTTP status to be stored (e.g. description, is_error, is_redirect)
  • New HTTP statuses to be added more easily (for instance, you're missing 410 GONE and 417 Expectation Failed from your example)

Upvotes: 2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

No, you can't create an ENUM on a set of integers. From reading the fine manual

Enum types take a list of one or more quoted labels

I can't fathom why you would want an ENUM of integers. Use integers directly, either in a CHECK constraint, or with a foreign key reference to a table of valid integers.

For HTTP status codes, which seems to be your target, a foreign key reference to a table of HTTP status codes and their meaning makes a lot of sense.

Upvotes: 1

Related Questions