PostgreSQL INSERT into an array of enums

postgresql types enums sql-insert
Zapnologica picture Zapnologica · Aug 14, 2013 · Viewed 12.3k times · Source

How can I insert an array of enums?
Here is my enum:

CREATE TYPE equipment AS ENUM ('projector','PAsystem','safe','PC','phone');

Then my table has an array of equipment:

CREATE TABLE lecture_room (
   id INTEGER DEFAULT NEXTVAL('lecture_id_seq')
 , seatCount int
 , equipment equipment[]
) INHERITS(venue);

Here is my ATTEMPT to INSERT:

INSERT INTO lecture_room (building_code, floorNo,  roomNo, length, width
                        , seatCount, equipment) 
VALUES 
('IT', 4, 2, 10, 15 ,120, ARRAY['projector','PAsystem','safe']),

But it gives me the following error:

ERROR: column "equipment" is of type equipment[] but expression is of type text[]
SQL state: 42804
Hint: You will need to rewrite or cast the expression.

Answer

Mark Stosberg picture Mark Stosberg · Aug 14, 2013

PostgreSQL doesn't know how to automatically cast input of type text to input of type equipment. You have to explicitly declare your strings as being of type equipment:

ARRAY['projector','PAsystem','safe']::equipment[]

I confirmed this with SQL Fiddle.

Related questions

Difference between text and varchar (character varying)

What's the difference between the text data type and the character varying (varchar) data types? According to the documentation If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. …

Read More
How to concatenate columns in a Postgres SELECT?

I have two string columns a and b in a table foo. select a, b from foo returns values a and b. However, concatenation of a and b does not work. I tried : select a || b from foo and select …

Read More
Difference between timestamps with/without time zone in PostgreSQL

Are timestamp values stored differently in PostgreSQL when the data type is WITH TIME ZONE versus WITHOUT TIME ZONE? Can the differences be illustrated with simple test cases?

Read More