CODEWITHSUNDEEP
sqlarrayspostgresql
DarkCoffee
DarkCoffee

Reputation: 950

Braces inside the array - postgresql

I have a table "temp" with two attributes: integer, text[].

I would like to insert a record with the brace inside the array. For example a record like this:

1, {'1{c}1','a'}

where 1 is the integer and '1{c}1' is the first element of the array and 'a' the second element of the array.

I tried a simply insert like this:

INSERT INTO temp VALUES (id, '{'1{c}1','a'}');

but it says that is malformed.

Upvotes: 2

Views: 2454

Answers (3)

roman
roman

Reputation: 117345

As an addition, it's also possible to use array constructors, I think it's more safe to use, because array elements are just SQL constants and you also could use expressions inside the array constructors:

insert into "temp" values(1, array['1{c}1','a']);

it's clear that this is array of strings, and this too

insert into "temp" values(1, array['1','2']);

Upvotes: 3

Mark Stosberg
Mark Stosberg

Reputation: 13381

According to the PostgreSQL documentation for arrays,

You can put double quotes around any element value, and must do so if it contains commas or curly braces.

A correct syntax would like this:

INSERT INTO "temp" VALUES (1, '{"1{c}1",a}');

You can see a complete, working example on SQL fiddle.

Upvotes: 2

JeffS
JeffS

Reputation: 2737

You don't want those inner single quotes.

INSERT INTO temp VALUES (id, '{1{c}1,a}');

Upvotes: 0

Related Questions