Insert value input syntax of an array of types without ARRAY/ROW nor casting? - Mailing list 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[] );