pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPG 7.2 varchar change
5+ messages / 4 participants
[nested] [flat]
* PG 7.2 varchar change
@ 2004-08-13 03:30 slane <slane@moyergroup.com>
2004-08-13 03:57 ` Re: PG 7.2 varchar change Tom Lane <tgl@sss.pgh.pa.us>
2004-08-13 04:24 ` Re: PG 7.2 varchar change Christopher Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 5+ messages in thread
From: slane @ 2004-08-13 03:30 UTC (permalink / raw)
To: pgsql-admin; +Cc: Jamie Thomas <jthomas@soliantconsulting.com>
Hello all:
I am upgrading a web application from postgres 7.1.2 to 7.4.3 (too long in
coming to that point, I know).
I have sifted through the history files and identified a restricted number
of changes that potentially impact the app, a few of which I don¹t
understand.
Here¹s the currently most alarming, a change that is recorded for 7.2:
CHAR(), VARCHAR() now reject strings that are too long (Peter E)
What does this mean? Or how do I find out what it means? My understanding
was that varchar fields had no text limit. But these are written like
functions. Does this refer to coercion functions that now reject strings
that are longer than the specified size of the underlying column? What if
the column was specified as type Œtext¹? And what was the old behavior?
Thanks for any insight. This is the only change that¹s really concerning us
deeply right now.
-- sgl
=======================================================
Steve Lane
Vice President
Soliant Consulting
14 North Peoria St Suite 2H
Chicago, IL 60607
voice: (312) 850-3930 email: slane@soliantconsulting.com
fax: (312) 850-3930 web: http://www.soliantconsulting.com
=======================================================
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG 7.2 varchar change
2004-08-13 03:30 PG 7.2 varchar change slane <slane@moyergroup.com>
@ 2004-08-13 03:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-13 04:32 ` Re: PG 7.2 varchar change Steve Lane <slane@soliantconsulting.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2004-08-13 03:57 UTC (permalink / raw)
To: slane <slane@moyergroup.com>; +Cc: pgsql-admin@postgresql.org, Jamie Thomas <jthomas@soliantconsulting.com>
slane <slane@moyergroup.com> writes:
> Here¹s the currently most alarming, a change that is recorded for 7.2:
> CHAR(), VARCHAR() now reject strings that are too long (Peter E)
> What does this mean? Or how do I find out what it means?
If in doubt, experiment.
In 7.1:
regression=# create table foo(f1 varchar(5));
CREATE
regression=# insert into foo values('1234567890');
INSERT 2913462 1
regression=# select * from foo;
f1
-------
12345
(1 row)
In 7.4:
regression=# create table foo(f1 varchar(5));
CREATE TABLE
regression=# insert into foo values('1234567890');
ERROR: value too long for type character varying(5)
7.4 follows the SQL spec (or our current understanding of it anyway):
strings will not be silently truncated. If you want to force truncation
to a particular length you can cast the string explicitly:
regression=# select 'way too long'::varchar(5);
varchar
---------
way t
(1 row)
That works the same in either version.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG 7.2 varchar change
2004-08-13 03:30 PG 7.2 varchar change slane <slane@moyergroup.com>
2004-08-13 03:57 ` Re: PG 7.2 varchar change Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-13 04:32 ` Steve Lane <slane@soliantconsulting.com>
2004-08-13 18:58 ` Re: PG 7.2 varchar change Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Steve Lane @ 2004-08-13 04:32 UTC (permalink / raw)
To: pgsql-admin; +Cc: Jamie Thomas <jthomas@soliantconsulting.com>
Thanks, Tom, that's very helpful. One more clarification: if the underlying
column has no express length limit (for example, it was defined as type
'text'), is this issue moot?
-- sgl
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Thu, 12 Aug 2004 23:57:11 -0400
> To: slane <slane@moyergroup.com>
> Cc: pgsql-admin@postgresql.org, Jamie Thomas <jthomas@soliantconsulting.com>
> Subject: Re: [ADMIN] PG 7.2 varchar change
>
> slane <slane@moyergroup.com> writes:
>> Here¹s the currently most alarming, a change that is recorded for 7.2:
>> CHAR(), VARCHAR() now reject strings that are too long (Peter E)
>> What does this mean? Or how do I find out what it means?
>
> If in doubt, experiment.
>
> In 7.1:
>
> regression=# create table foo(f1 varchar(5));
> CREATE
> regression=# insert into foo values('1234567890');
> INSERT 2913462 1
> regression=# select * from foo;
> f1
> -------
> 12345
> (1 row)
>
> In 7.4:
>
> regression=# create table foo(f1 varchar(5));
> CREATE TABLE
> regression=# insert into foo values('1234567890');
> ERROR: value too long for type character varying(5)
>
> 7.4 follows the SQL spec (or our current understanding of it anyway):
> strings will not be silently truncated. If you want to force truncation
> to a particular length you can cast the string explicitly:
>
> regression=# select 'way too long'::varchar(5);
> varchar
> ---------
> way t
> (1 row)
>
> That works the same in either version.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG 7.2 varchar change
2004-08-13 03:30 PG 7.2 varchar change slane <slane@moyergroup.com>
2004-08-13 03:57 ` Re: PG 7.2 varchar change Tom Lane <tgl@sss.pgh.pa.us>
2004-08-13 04:32 ` Re: PG 7.2 varchar change Steve Lane <slane@soliantconsulting.com>
@ 2004-08-13 18:58 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-08-13 18:58 UTC (permalink / raw)
To: Steve Lane <slane@soliantconsulting.com>; +Cc: pgsql-admin@postgresql.org, Jamie Thomas <jthomas@soliantconsulting.com>
Steve Lane <slane@soliantconsulting.com> writes:
> Thanks, Tom, that's very helpful. One more clarification: if the underlying
> column has no express length limit (for example, it was defined as type
> 'text'), is this issue moot?
Right, it only applies to specifically-declared length limits.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG 7.2 varchar change
2004-08-13 03:30 PG 7.2 varchar change slane <slane@moyergroup.com>
@ 2004-08-13 04:24 ` Christopher Browne <cbbrowne@acm.org>
1 sibling, 0 replies; 5+ messages in thread
From: Christopher Browne @ 2004-08-13 04:24 UTC (permalink / raw)
To: pgsql-admin
Quoth slane@moyergroup.com (slane):
> Hello all:
>
> I am upgrading a web application from postgres 7.1.2 to 7.4.3 (too long in
> coming to that point, I know).
>
> I have sifted through the history files and identified a restricted number
> of changes that potentially impact the app, a few of which I don¹t
> understand.
>
> Here¹s the currently most alarming, a change that is recorded for 7.2:
>
> CHAR(), VARCHAR() now reject strings that are too long (Peter E)
Consider this example:
tutorial=# create table test_string (f1 varchar, f2 varchar(10));
CREATE TABLE
tutorial=# insert into test_string (f1, f2) values ('abc', 'def');
INSERT 2623360 1
tutorial=# insert into test_string (f1, f2) values ('abcdefghijklmnopqrstuvwxyz', 'def');
INSERT 2623361 1
tutorial=# insert into test_string (f1, f2) values ('abcdefghijklmnopqrstuvwxyz', 'abcdefghiasdfasdfa');
ERROR: value too long for type character varying(10)
If no maximum length is specified, PostgreSQL is free to stick a
goodly amount of data in the field.
But supposing you decide that a particular column is VARCHAR(10),
trying to stuff more than 10 characters into it will fail, as you see
above.
Doing similar with char:
tutorial=# create table test_chars (f1 char, f2 char(10));
CREATE TABLE
tutorial=# insert into test_chars (f1, f2) values ('abc', 'def');
ERROR: value too long for type character(1)
Does that illustrate what's going on? I hope so...
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/spreadsheets.html
"Fashion is a form of ugliness so intolerable that we have to alter it
every six months." -- Oscar Wilde
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-08-13 18:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-13 03:30 PG 7.2 varchar change slane <slane@moyergroup.com>
2004-08-13 03:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-13 04:32 ` Steve Lane <slane@soliantconsulting.com>
2004-08-13 18:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-13 04:24 ` Christopher Browne <cbbrowne@acm.org>
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