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.

Enclasp

Answer 1

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...

Rizzo

Please can you also add what did you change? – Korey

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

Answer 2

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.

Jackjackadandy

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. – Bunt

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

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 ;-) – Bunt