Lists: | pgsql-bugs |
---|
From: | fabio(dot)pasqualini(at)ac-reims(dot)fr |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #8677: char(n) - bad limit with arrays of composite type |
Date: | 2013-12-11 19:01:54 |
Message-ID: | E1Vqp2g-0008Cc-63@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 8677
Logged by: Fabio Pasqualini
Email address: fabio(dot)pasqualini(at)ac-reims(dot)fr
PostgreSQL version: 8.4.19
Operating system: Debian
Description:
-- test with psql (8.4.19) on debian
create table t1
(
libelle varchar(100),
cp char(5)
);
create table t2
(
id_t2 integer primary key,
nom varchar(100),
tab_t1 t1 array
);
insert into t2(id_t2, nom, tab_t1)
values ( 1,'test1', '{ "(''toto'', ''1'')", "(''titi'', ''1'')"}' );
insert into t2(id_t2, nom, tab_t1)
values ( 2,'test2', '{ "(''toto'', ''12'')", "(''titi'', ''12'')"}' );
insert into t2(id_t2, nom, tab_t1)
values ( 3,'test3', '{ "(''toto'', ''123'')", "(''titi'', ''123'')"}' );
insert into t2(id_t2, nom, tab_t1)
values ( 4,'test4', '{ "(''toto'', ''1234'')", "(''titi'', ''1234'')"}' );
insert into t2(id_t2, nom, tab_t1)
values ( 5,'test5', '{ "(''toto'', ''12345'')", "(''titi'', ''12345'')"}'
);
CREATE TABLE
psql:./enseignement/bts/2014/prog_c++/carnet2/bug/test.sql:14: NOTICE:
CREATE TABLE / PRIMARY KEY créera un index implicite « t2_pkey » pour la
table « t2 »
CREATE TABLE
INSERT 0 1
INSERT 0 1
psql:./enseignement/bts/2014/prog_c++/carnet2/bug/test.sql:25: ERREUR:
valeur trop longue pour le type character(5)
LIGNE 2 : values ( 3,'test3', '{ "(''toto'', ''123'')", "(''titi'', '...
^
psql:./enseignement/bts/2014/prog_c++/carnet2/bug/test.sql:28: ERREUR:
valeur trop longue pour le type character(5)
LIGNE 2 : values ( 4,'test4', '{ "(''toto'', ''1234'')", "(''titi'', ...
^
psql:./enseignement/bts/2014/prog_c++/carnet2/bug/test.sql:31: ERREUR:
valeur trop longue pour le type character(5)
LIGNE 2 : values ( 5,'test5', '{ "(''toto'', ''12345'')", "(''titi'',...
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | fabio(dot)pasqualini(at)ac-reims(dot)fr |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #8677: char(n) - bad limit with arrays of composite type |
Date: | 2013-12-12 16:48:51 |
Message-ID: | 18204.1386866931@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
fabio(dot)pasqualini(at)ac-reims(dot)fr writes:
> psql:./enseignement/bts/2014/prog_c++/carnet2/bug/test.sql:25: ERREUR:
> valeur trop longue pour le type character(5)
> LIGNE 2 : values ( 3,'test3', '{ "(''toto'', ''123'')", "(''titi'', '...
> ^
These errors are entirely correct, because you've misunderstood the
(admittedly complicated) quoting requirements for nested arrays/records
in literal values. Extracting the values that you did manage to store
shows the problem:
# select (tab_t1)[1].cp from t2;
cp
-------
'1'
'12'
(2 rows)
The quotes are being taken as data characters, and there's a leading space
in there too.
The rules for writing composite literals are explained here:
http://www.postgresql.org/docs/9.3/static/rowtypes.html#ROWTYPES-IO-SYNTAX
On the whole, it's very often easier to use ARRAY[] and ROW()
constructors than to get the quoting rules right for literal syntax.
I'd have done these examples like this:
insert into t2(id_t2, nom, tab_t1)
values ( 3,'test3',array[row('toto', '123'), row('titi', '123')]::t1[]);
You need an explicit cast unfortunately, because otherwise the ARRAY[]
constructor yields record[] which is not considered automatically
castable to t1[].
regards, tom lane