Thread: BUG #8677: char(n) - bad limit with arrays of composite type
BUG #8677: char(n) - bad limit with arrays of composite type
From
fabio.pasqualini@ac-reims.fr
Date:
The following bug has been logged on the website: Bug reference: 8677 Logged by: Fabio Pasqualini Email address: fabio.pasqualini@ac-reims.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'',...
fabio.pasqualini@ac-reims.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