PostgreSQL INSERT into an array of enumerations

Question

How to 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 for 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 this 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 1

PostgreSQL does not know how to automatically enter text input for equipment input. You must explicitly declare your lines as equipment type:

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

I have confirmed this with SQL Fiddle .

Answer 2

An alternative to the ARRAY constructor like @Mark, correctly posed , is to directly quote a string literal :

 '{projector,PAsystem,safe}'::equipment[] 

This option is shorter, and some clients have problems with the ARRAY constructor, which is a functionally selected element.

Answer 3

Old question, but new answer. In modern versions of Postgres (verified since 9.6), all this is not required. It works as expected:

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