varchar error

This is a multi-part message in MIME format.

------=_NextPart_000_000A_01C577E5.51E02F70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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.
------=_NextPart_000_000A_01C577E5.51E02F70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#e3e1db>
<DIV><FONT face=3DVerdana color=3D#800000 size=3D2>Hello, I just have
this:</FONT></DIV>
<DIV><FONT face=3DVerdana color=3D#800000 size=3D2></FONT> </DIV>
<DIV><FONT face=3DVerdana color=3D#800000 size=3D2>CREATE TABLE test
(<BR>    mytext varchar(5)<BR>) WITHOUT =
OIDS;</FONT></DIV>
<DIV><FONT face=3DVerdana color=3D#800000 size=3D2></FONT> </DIV>
<DIV><FONT face=3DVerdana color=3D#800000 size=3D2>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 face=3DVerdana color=3D#800000 size=3D2></FONT> </DIV>
<DIV><FONT face=3DVerdana color=3D#800000 size=3D2>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 face=3DVerdana color=3D#800000 size=3D2></FONT> </DIV>
<DIV><FONT face=3DVerdana color=3D#800000 size=3D2>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?</FONT></DIV>
<DIV><FONT face=3DVerdana color=3D#800000 size=3D2></FONT> </DIV>
<DIV><FONT face=3DVerdana color=3D#800000 size=3D2>Cheers,
Raul.</FONT></DIV></BODY></HTML>

------=_NextPart_000_000A_01C577E5.51E02F70--
Raul Secan [ Do, 23 Juni 2005 10:18 ] [ ID #850337 ]

Re: varchar error

Hi,

On 6/23/05, Raul Secan <raul [at] 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.htm l

Regards.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Volkan YAZICI [ Do, 23 Juni 2005 10:33 ] [ ID #850338 ]

Re: varchar error

This is a multi-part message in MIME format.
--------------010607010608060009040805
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 [at] kaartjeposten.nl / msn. kaartjeposten [at] hotmail.com
i. www.kaartjeposten.nl / t. 06 - 28 13 88 57
Nu ook voor uw verjaardagkalenders



--------------010607010608060009040805
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Arial">you might try a trigger!<br>
<br>
</font><br>
Raul Secan wrote:
<blockquote cite="mid000d01c577cc$2c9c1f30$0a10a8c0 [at] zerosoft.ro"
type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta content="MSHTML 6.00.2800.1106" name="GENERATOR">
<style></style>
<div><font color="#800000" face="Verdana" size="2">Hello, I just have
this:</font></div>
<div> </div>
<div><font color="#800000" face="Verdana" size="2">CREATE TABLE test (<br>
    mytext varchar(5)<br>
) WITHOUT OIDS;</font></div>
<div> </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> </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> </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 be done in PostreSQL?
Maybe in CREATE TABLE definition?</font></div>
<div> </div>
<div><font color="#800000" face="Verdana" size="2">Cheers, Raul.</font></div>
</blockquote>
<br>
<div class="moz-signature">-- <br>
<meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
<title>kaartjeposten</title>
<p class="MsoNormal"><font color="red" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: red;">Kaartjeposten.nl<br>
</span></font><font color="gray" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: gray;">Molukkenstraat
80 / 7512 XT  Enschede<br>
</span></font><font color="black" face="Wingdings" size="2"><span
style="font-size: 10pt; font-family: Wingdings; color: black;"
lang="EN-US">*</span></font><font color="gray" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: gray;" lang="EN-US">
</span></font><font color="gray" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: gray;"><a class="moz-txt-link-abbreviated" href="mailto:info [at] kaartjeposten.nl">info [at] kaartjeposten.nl</a>
/
msn. <a class="moz-txt-link-abbreviated" href="mailto:kaartjeposten [at] hotmail.com">kaartjeposten [at] hotmail.com</a><br>
i. <a class="moz-txt-link-abbreviated" href="http://www.kaartjeposten.nl">www.kaartjeposten.nl</a> / t. 06 - 28 13 88 57<br>
</span></font><font color="red" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial; color: red;">Nu ook voor
uw verjaardagkalenders</span></font><font color="navy"><span
style="color: navy;"><o:p></o:p></span></font></p>
<span class="EmailStijl18"><font color="navy" face="Arial" size="2"><span
style="font-size: 10pt; font-family: Arial;"><!--[if !supportEmptyParas]-->
<br>
</span></font></span>
</div>
</body>
</html>

--------------010607010608060009040805--
Anthony van den Berg [ Do, 23 Juni 2005 10:40 ] [ ID #850339 ]

Re: varchar error

<=21DOCTYPE HTML PUBLIC =22-//W3C//DTD HTML 4.0 =
Transitional//EN=22><HTML><HEAD><META http-equiv=3DContent-Type =
content=3D=22text/html; charset=3Dutf-8=22><META content=3D=22IBISIT =
WebMail=22 name=3DGENERATOR></HEAD><BODY>
<div>try with either Rule or Trigger.<br>
 </div><div>with regards,<br>S.Gnanavel</div><br><br><blockquote =
style=3D=22border-left: 2px solid rgb(0, 0, 255); padding-left: 5px; =
margin-left: 5px; margin-right: 0px;=22><div =
class=3D=22msgHeaders=22>-----Original Message-----<br><b>From:</b> =
raul=40zerosoft.ro<br><b>Sent:</b> Thu, 23 Jun 2005 11:18:45 =
+0300<br><b>To:</b> pgsql-php=40postgresql.org<br><b>Subject:</b> =
=5BPHP=5D varchar error<br><br></div><div class=3D=22oldBody=22><div>
<div><font color=3D=22=23800000=22 face=3D=22Verdana=22 =
size=3D=222=22>Hello, I just have
this:</font></div>
<div><font color=3D=22=23800000=22 face=3D=22Verdana=22 =
size=3D=222=22></font> </div>
<div><font color=3D=22=23800000=22 face=3D=22Verdana=22 =
size=3D=222=22>CREATE TABLE test
(<br>    mytext varchar(5)<br>) WITHOUT OIDS;</font></div>
<div><font color=3D=22=23800000=22 face=3D=22Verdana=22 =
size=3D=222=22></font> </div>
<div><font color=3D=22=23800000=22 face=3D=22Verdana=22 size=3D=222=22>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=3D=22=23800000=22 face=3D=22Verdana=22 =
size=3D=222=22></font> </div>
<div><font color=3D=22=23800000=22 face=3D=22Verdana=22 size=3D=222=22>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=3D=22=23800000=22 face=3D=22Verdana=22 =
size=3D=222=22></font> </div>
<div><font color=3D=22=23800000=22 face=3D=22Verdana=22 size=3D=222=22>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?</font></div>
<div><font color=3D=22=23800000=22 face=3D=22Verdana=22 =
size=3D=222=22></font> </div>
<div><font color=3D=22=23800000=22 face=3D=22Verdana=22 =
size=3D=222=22>Cheers,
Raul.</font></div></div></div></blockquote>
</BODY></HTML>=
Gnanavel Shanmugam [ Do, 23 Juni 2005 10:53 ] [ ID #850340 ]

Re: varchar error

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 [at] ESI.it


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Marco Colombo [ Do, 23 Juni 2005 13:29 ] [ ID #850341 ]

Re: varchar error

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
Bruno Wolff III [ Do, 23 Juni 2005 17:58 ] [ ID #850342 ]
Datenbanken » gmane.comp.db.postgresql.php » varchar error

Vorheriges Thema: Pqsocket not implemented in PHP
Nächstes Thema: FW: pg_put_line() Frustration

[ Startseite ] [ Administrator ] [ Suche ] [ Hinweise ] [ Impressum ]

Powered by FudForum