Search code examples
arraysdatabasepostgresqlinsertpg

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.

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