Re: Insert value input syntax of an array of types without ARRAY/ROW nor casting?

On 19 Mar 2011, at 2:33, Stefan Keller wrote:

> Given the test snippet below, why do the following insert attempts fail?
>
> The literal constant would be the most intuitive syntax. The attempt
> below also closely follows the documentation AFAIK:
> http://www.postgresql.org/docs/current/static/arrays.html
>
> INSERT INTO mytypetable VALUES (
> 5, '{ ('a', 'aa'), ('b', 'bb') }' );
>> ERROR: Syntax Error

You need to escape those quotes you put inside the literal:

INSERT INTO mytypetable VALUES (
5, '{ (''a'', ''aa''), (''b'', ''bb'') }' );

Or use dollar-quoting (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING):

INSERT INTO mytypetable VALUES (
5, $${ ('a', 'aa'), ('b', 'bb') }$$ );

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4d848304235883070015353!