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