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.

Mark Stosberg · Accepted Answer · 2013-08-14 15:15:25Z

35

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.

edited Aug 14, 2013 at 15:15

answered Aug 14, 2013 at 15:00

Mark Stosberg

13.5k6 gold badges48 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2025-02-27 03:03:36Z

25

The alternative to an ARRAY constructor (like Mark supplied) is a string literal:

'{projector,PAsystem,safe}'::equipment[]  -- cast is 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

edited Feb 27 at 3:03

answered Aug 14, 2013 at 17:06

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Comments

MJavaDev · Accepted Answer · 2018-07-05 13:19:46Z

19

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

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

answered Jul 5, 2018 at 13:19

MJavaDev

2713 silver badges10 bronze badges

Comments

harm · Accepted Answer · 2017-05-04 13:08:22Z

18

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

answered May 4, 2017 at 13:08

harm

10.5k11 gold badges40 silver badges43 bronze badges

2 Comments

fullStackChris Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

PostgreSQL INSERT into an array of enums

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related