CODEWITHSUNDEEP
arraysdatabasepostgresqlinsertpg
Michael Kosyk
Michael Kosyk

Reputation: 358

Postgres - inserting a varchar array via SQL

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.

Upvotes: 5

Views: 22145

Answers (2)

user330315
user330315

Reputation:

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.

Upvotes: 7

Pankaj K
Pankaj K

Reputation: 758

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

Upvotes: 10

Related Questions