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

Hi,

I'm playing around with array of types but don't get the intuitive
syntax really.

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

This would be close to Oracle (SQL standard?) syntax by using an
implicitly generated constructor with same name as type ('mytypes'):

INSERT INTO mytypetable VALUES (
  6, ARRAY[ mytypes('a', 'aa'), ('b', 'bb') ] );
> ERROR: Function mytypes(unknown, unknown) does not exist

Any help?

Yours, S.


-- Testing arrays of types
CREATE TYPE mytype AS (
   attr1 varchar,
   attr2 varchar
);
CREATE TABLE mytypetable (
  id serial,
  mytypes mytype[10]
);

INSERT INTO mytypetable VALUES ( 0, null );
INSERT INTO mytypetable VALUES ( 1, '{ null, null }' );
INSERT INTO mytypetable VALUES (
  2, ARRAY[ (null, null) ]::mytype[] );
INSERT INTO mytypetable VALUES (
  3, ARRAY[ ('a', 'aa'), ('b', 'bb') ]::mytype[] );
INSERT INTO mytypetable VALUES (
  4, ARRAY[ ROW('a', 'aa'), ROW('b', 'bb') ]::mytype[] );

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!

Thank you for the hint.

Unfortunately it still does'nt work. I get

ERROR:  wrong record constant: »('a'«
LINE 2:  5, $${ ('a', 'aa'), ('b', 'bb') }$$ );
           ^
DETAIL:  Unexpected end of line.

Yours, S.

2011/3/19 Alban Hertroys <dalroi@solfertje.student.utwente.nl>:
> 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:1205,4d848300235885070126629!
>
>
>

On Mar 19, 2011, at 9:50 PM, Stefan Keller wrote:

> Unfortunately it still does'nt work. I get
>
> ERROR:  wrong record constant: »('a'«
> LINE 2:  5, $${ ('a', 'aa'), ('b', 'bb') }$$ );
>           ^
> DETAIL:  Unexpected end of line.



Try following, it should work:
INSERT INTO mytypetable VALUES (
6,ARRAY[row('a','aa'), row('b', 'bb')]::mytype[] ); 


Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com