pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
varchar error
6+ messages / 6 participants
[nested] [flat]

* varchar error
@ 2005-06-23 08:18 Raul Secan <raul@zerosoft.ro>
  2005-06-23 08:33 ` Re: varchar error Volkan YAZICI <volkan.yazici@gmail.com>
  2005-06-23 08:40 ` Re: varchar error Anthony van den Berg <anthony@kaartjeposten.nl>
  2005-06-23 08:53 ` Re: varchar error Gnanavel Shanmugam <s.gnanavel@inbox.com>
  2005-06-23 11:29 ` Re: varchar error Marco Colombo <pgsql@esiway.net>
  2005-06-23 15:58 ` Re: varchar error Bruno Wolff III <bruno@wolff.to>
  0 siblings, 5 replies; 6+ messages in thread

From: Raul Secan @ 2005-06-23 08:18 UTC (permalink / raw)
  To: pgsql-php

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.

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: varchar error
  2005-06-23 08:18 varchar error Raul Secan <raul@zerosoft.ro>
@ 2005-06-23 08:33 ` Volkan YAZICI <volkan.yazici@gmail.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Volkan YAZICI @ 2005-06-23 08:33 UTC (permalink / raw)
  To: Raul Secan <raul@zerosoft.ro>; +Cc: pgsql-php

Hi,

On 6/23/05, Raul Secan <raul@zerosoft.ro> wrote:
> 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 think that it's the function of database to manipulate the input.

> 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? 

IMHO, you can create an insert (and update) procedure (like
my_insert() and my_update()) with using substr() [1] function.

[1] www.postgresql.org/docs/8.0/interactive/functions-string.html

Regards.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: varchar error
  2005-06-23 08:18 varchar error Raul Secan <raul@zerosoft.ro>
@ 2005-06-23 08:40 ` Anthony van den Berg <anthony@kaartjeposten.nl>
  4 siblings, 0 replies; 6+ messages in thread

From: Anthony van den Berg @ 2005-06-23 08:40 UTC (permalink / raw)
  To: Raul Secan <raul@zerosoft.ro>; +Cc: pgsql-php

you might try a trigger!


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.


-- 

Kaartjeposten.nl
Molukkenstraat 80 / 7512 XT  Enschede
* info@kaartjeposten.nl / msn. kaartjeposten@hotmail.com
i. www.kaartjeposten.nl / t. 06 - 28 13 88 57
Nu ook voor uw verjaardagkalenders

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: varchar error
  2005-06-23 08:18 varchar error Raul Secan <raul@zerosoft.ro>
@ 2005-06-23 08:53 ` Gnanavel Shanmugam <s.gnanavel@inbox.com>
  4 siblings, 0 replies; 6+ messages in thread

From: Gnanavel Shanmugam @ 2005-06-23 08:53 UTC (permalink / raw)
  To: Raul Secan <raul@zerosoft.ro>; pgsql-php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><HTML><HEAD><META http-equiv=Content-Type content="text/html; charset=utf-8"><META content="IBISIT WebMail" name=GENERATOR></HEAD><BODY>
<div>try with either Rule or Trigger.<br>
&nbsp;</div><div>with regards,<br>S.Gnanavel</div><br><br><blockquote style="border-left: 2px solid rgb(0, 0, 255); padding-left: 5px; margin-left: 5px; margin-right: 0px;"><div class="msgHeaders">-----Original Message-----<br><b>From:</b> raul@zerosoft.ro<br><b>Sent:</b> Thu, 23 Jun 2005 11:18:45 +0300<br><b>To:</b> pgsql-php@postgresql.org<br><b>Subject:</b> [PHP] varchar error<br><br></div><div class="oldBody"><div>
<div><font color="#800000" face="Verdana" size="2">Hello, I just have 
this:</font></div>
<div><font color="#800000" face="Verdana" size="2"></font>&nbsp;</div>
<div><font color="#800000" face="Verdana" size="2">CREATE TABLE test 
(<br>&nbsp;&nbsp;&nbsp; mytext varchar(5)<br>) WITHOUT OIDS;</font></div>
<div><font color="#800000" face="Verdana" size="2"></font>&nbsp;</div>
<div><font color="#800000" face="Verdana" size="2">If I put a string with more than 5 
chars in mytext, I receive an error, regarding the wrong lenght of the 
string.</font></div>
<div><font color="#800000" face="Verdana" size="2"></font>&nbsp;</div>
<div><font color="#800000" face="Verdana" size="2">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.</font></div>
<div><font color="#800000" face="Verdana" size="2"></font>&nbsp;</div>
<div><font color="#800000" face="Verdana" size="2">I don't want to do this from PHP, 
and I was wandering how this can&nbsp;be done in PostreSQL? Maybe in CREATE 
TABLE definition?</font></div>
<div><font color="#800000" face="Verdana" size="2"></font>&nbsp;</div>
<div><font color="#800000" face="Verdana" size="2">Cheers, 
Raul.</font></div></div></div></blockquote>
</BODY></HTML>


^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: varchar error
  2005-06-23 08:18 varchar error Raul Secan <raul@zerosoft.ro>
@ 2005-06-23 11:29 ` Marco Colombo <pgsql@esiway.net>
  4 siblings, 0 replies; 6+ messages in thread

From: Marco Colombo @ 2005-06-23 11:29 UTC (permalink / raw)
  To: Raul Secan <raul@zerosoft.ro>; +Cc: pgsql-php

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: varchar error
  2005-06-23 08:18 varchar error Raul Secan <raul@zerosoft.ro>
@ 2005-06-23 15:58 ` Bruno Wolff III <bruno@wolff.to>
  4 siblings, 0 replies; 6+ messages in thread

From: Bruno Wolff III @ 2005-06-23 15:58 UTC (permalink / raw)
  To: Raul Secan <raul@zerosoft.ro>; +Cc: pgsql-php

On Thu, Jun 23, 2005 at 11:18:45 +0300,
  Raul Secan <raul@zerosoft.ro> 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?

The way to do this is to use the substring function to do this. You can extract
the first 5 characters of the string you are supplied.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-06-23 15:58 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-23 08:18 varchar error Raul Secan <raul@zerosoft.ro>
2005-06-23 08:33 ` Volkan YAZICI <volkan.yazici@gmail.com>
2005-06-23 08:40 ` Anthony van den Berg <anthony@kaartjeposten.nl>
2005-06-23 08:53 ` Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-06-23 11:29 ` Marco Colombo <pgsql@esiway.net>
2005-06-23 15:58 ` Bruno Wolff III <bruno@wolff.to>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox