Обсуждение: Insert value input syntax of an array of types without ARRAY/ROW nor casting?
Insert value input syntax of an array of types without ARRAY/ROW nor casting?
От
Stefan Keller
Дата:
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[] );
Re: Insert value input syntax of an array of types without ARRAY/ROW nor casting?
От
Alban Hertroys
Дата:
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!
Re: Insert value input syntax of an array of types without ARRAY/ROW nor casting?
От
Stefan Keller
Дата:
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! > > >
Re: Insert value input syntax of an array of types without ARRAY/ROW nor casting?
От
Vibhor Kumar
Дата:
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