X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E89EA5284D for ; Thu, 23 Jun 2005 08:27:06 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12907-09 for ; Thu, 23 Jun 2005 11:27:00 +0000 (GMT) Received: from Nazgul.esiway.net (Nazgul.esiway.net [193.194.16.154]) by svr1.postgresql.org (Postfix) with ESMTP id 8FED7528A8 for ; Thu, 23 Jun 2005 08:26:56 -0300 (ADT) Received: from Megathlon.ESI (Ghost.esi.it [193.194.16.225]) by Nazgul.esiway.net (8.13.1/8.13.1) with ESMTP id j5NBQv9X027987 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Thu, 23 Jun 2005 13:26:57 +0200 Received: from Frodo.esi (Frodo.ESI [10.10.10.13]) (user=marco mech=CRAM-MD5 bits=0) by Megathlon.ESI (8.12.11/8.12.11) with ESMTP id j5NBQvID019048 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NO); Thu, 23 Jun 2005 13:26:57 +0200 Subject: Re: varchar error From: Marco Colombo To: Raul Secan Cc: pgsql-php@postgresql.org In-Reply-To: <000d01c577cc$2c9c1f30$0a10a8c0@zerosoft.ro> References: <000d01c577cc$2c9c1f30$0a10a8c0@zerosoft.ro> Content-Type: text/plain Date: Thu, 23 Jun 2005 13:29:07 +0200 Message-Id: <1119526147.24016.208.camel@Frodo.esi> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-4) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/23 X-Sequence-Number: 3182 On Thu, 2005-06-23 at 11:18 +0300, Raul Secan wrote: > Hello, I just have this: > > CREATE TABLE test ( > mytext varchar(5) > ) WITHOUT OIDS; > > If I put a string with more than 5 chars in mytext, I receive an > error, regarding the wrong lenght of the string. > > In MySQL I know that the string is automatically reduced to the number > of char allowed by the column, even if I insert a longer string. > > I don't want to do this from PHP, and I was wandering how this can be > done in PostreSQL? Maybe in CREATE TABLE definition? > > Cheers, Raul. The job of the database is to accept valid data and to refuse invalid ones, not to silently convert invalid data into a valid form. While it is possible to do that conversion in PostgreSQL, I suggest you either reconsider doing it in the application (the place it belongs to), or think again about the schema (maybe storing the whole string). BTW, you can also truncate the string at insert time, just change: INSERT INTO test (mytext) VALUES ('alongstring'); into: INSERT INTO test (mytext) VALUES (substring('alongstring' for 5)); Here it is, in action: marco=# CREATE TABLE test (mytext varchar(5)) WITHOUT OIDS; CREATE TABLE marco=# INSERT INTO test (mytext) VALUES ('alongstring'); ERROR: value too long for type character varying(5) marco=# INSERT INTO test (mytext) VALUES (substring('alongstring' for 5)); INSERT 0 1 marco=# SELECT * FROM test; mytext -------- along (1 row) Of course, you have to do that on every UPDATE, too. If that's what you want to achieve, I find it much more readable to do the substring() or the PHP equivalent explicitly, rather than relying on some implicit RULE or TRIGGER (or worse, on a database that silently truncates it). For sure I get puzzled when SELECT returns 'along' after I do INSERT 'alongstring'. Think about consistency. .TM. -- ____/ ____/ / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _____/ _____/ _/ Colombo@ESI.it