Postgres - inserting a varchar array via SQL

Question

I want to execute this query.

INSERT INTO items (title, description,
start_date,
expiration_date,
type,
quantity,
state,
sold,
cover_photo_file_name,
cover_photo_content_type,
cover_photo_file_size,
instructions)
VALUES
(
    'some_test_value',
    'lorem ipsum',
    '2015-01-01 00:00:00',
    '2015-06-10 00:00:00',
    0,
    19,
    0,
    0,
    'RackMultipart20150317-10093-1ny4gmx.gif',
    'image/gif',
    157574,
    {"lorem ipsum\r","dolor sit\r","loremloremipsumipsum 'sitsit' dolor sit"}
    );

I get a very nasty

ERROR: syntax error at or near "{"

When i try to do it the

ARRAY["Lorem ipsum\r", ...]

I get:

ERROR: column "Lorem ipsum" does not exist

The problem is i cannot change the db right now (i don't have time for it) so i need to construct a query that will "do" the job (although this db requires normalization with use of DROP DATABSE... )

What is wrong with my code? Maybe i'm missing something obvious but my tired mind is just skipping it.

Pankaj K · Accepted Answer · 2015-04-23 12:13:41Z

10

INSERT INTO items (title, description,
start_date,
expiration_date,
type,
quantity,
state,
sold,
cover_photo_file_name,
cover_photo_content_type,
cover_photo_file_size,
instructions)
VALUES
  ('some_test_value',
  'lorem ipsum',
'2015-01-01 00:00:00',
'2015-06-10 00:00:00',
0,
19,
0,
0,
'RackMultipart20150317-10093-1ny4gmx.gif',
'image/gif',
157574,
'{"lorem ipsum\r","dolor sit\r","loremloremipsumipsum 'sitsit' dolor sit"}'
);

Please try this...

answered Apr 23, 2015 at 12:13

Pankaj K

7567 silver badges20 bronze badges

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

2 Comments

Nilesh Over a year ago

Please can you also add what did you change?

Craig Ringer Over a year ago

While this is correct, I encourage you to try to explain why and/or add relevant documentation links.

score 7 · Accepted Answer · 2015-04-23 12:19:20Z

7

String literals need to be enclosed in single quotes ' not double quotes.

Only inside the "short hand" array notation using {...} the values need to be enclosed in double quotes:

'{"lorem ipsum", "dolor sit"}'::text[]

Using the ARRAY constructor is easier here:

ARRAY['Lorem ipsum\r', ...]

because the single quotes don't need to be escaped.

edited Apr 23, 2015 at 12:19

answered Apr 23, 2015 at 12:13

user330315

3 Comments

Craig Ringer Over a year ago

The first is actually different, the single quotes form part of the actual value of the array member. Compare SELECT '{''lorem ipsum'', ''dolor sit''}'::text[]; with SELECT '{"lorem ipsum", "dolor sit"}'::text[]; or SELECT '{lorem ipsum, dolor sit}'::text[];. Array literals use double quotes for inner values, and those quotes are optional. It's more like CSV than SQL. Yes, that's horrible.

user330315 Over a year ago

@CraigRinger: of course, what was I thinking. Corrected

Craig Ringer Over a year ago

I don't mean to follow you around and nitpick by the way. Trying to help out. I've just realised it might seem a bit that way lately ;-)

Collectives™ on Stack Overflow

Postgres - inserting a varchar array via SQL

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related