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

From: Stefan Keller <sfkeller(at)gmail(dot)com>
To: pgsql-general List <pgsql-general(at)postgresql(dot)org>
Subject: Insert value input syntax of an array of types without ARRAY/ROW nor casting?
Date: 2011-03-19 01:33:10
Message-ID: AANLkTi=crri1nGrJmFrEmfcwrsvcgdeH1O-sM-XO8JxL@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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[] );

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Figueiredo Jr. 2011-03-19 01:47:21 Re: [GENERAL] Re: [GENERAL] Re: [GENERAL] Re: [GENERAL] Different encoding for string values and identifier strings? Or (select 'tést' as tést) returns different values for string and identifier...
Previous Message Tom Lane 2011-03-19 01:01:53 Re: Re: [GENERAL] Re: [GENERAL] Re: [GENERAL] Different encoding for string values and identifier strings? Or (select 'tést' as tést) returns different values for string and identifier...