CODEWITHSUNDEEP
postgresqltypesenumssql-insert
Zapnologica
Zapnologica

Reputation: 22556

PostgreSQL INSERT into an array of enums

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.

Upvotes: 33

Views: 29408

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656882

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

Upvotes: 24

MJavaDev
MJavaDev

Reputation: 271

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

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

Upvotes: 19

harm
harm

Reputation: 10385

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"}');

Upvotes: 18

Mark Stosberg
Mark Stosberg

Reputation: 13381

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.

Upvotes: 35

Related Questions