PostgreSQL INSERT into an array of enums

Question

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.

Solution 1

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.

Solution 2

The alternative to an ARRAY constructor like @Mark correctly supplied is to use a string literal:

'{projector,PAsystem,safe}'::equipment[]  -- cast optional

This variant is shorter and some clients have problems with the ARRAY constructor, which is a function-like element.

Plus, the cast is optional in this context (for cleaner code, better readability). Since the literal is type unknown initially (unlike the result of an ARRAY constructor!), Postgres will derive the type from the target column, and everything just works.

It's always been like that - tested for Postgres 9.3 or later:

db<>fiddle here - Postgres 14

db<>fiddle here - Postgres 9.5

sqlfiddle - Postgres 9.3

Solution 3

Old question, but a new answer. In modern versions of Postgres (tested with 9.6) none of this is required. It works as expected:

INSERT INTO lecture_room (equipment) VALUES ('{"projector", "safe"}');

Solution 4

Additionally to @harm answer, you can skip quotations marks:

INSERT INTO lecture_room (equipment) VALUES ('{projector, safe}');

View more solutions

Comments

[fullStackChris]

Does this handle duplicates automatically or is additional checking required? For example if projector is already in the equipment column of lecture_room, will we see two entries of projector?

[dpnmn]

No, there is no duplicate check. For inserts/updates the values are exactly the array you provide. And note that this is an array, not a set. You will have to perform any duplicate check yourself - '{safe, safe, safe}' is legal and will be stored as three values.