Обсуждение: operator varchar = integer

Hey Group,

i know what all will say but i need to recreate the = operator for
datatypes varchar and integer in PostgreSQL 8.3.

Our Software Project has Millions of Lines and so it would be difficult
to check all queries and Datatypes. Also it works really fine and we all
know the risk of wrong auto casting.

Anyone knows the Syntax?

Thanks a lot for your great work.


Daniel.

ups;

i have this script but forgott to execute it in the new createt db....
monday morning....

thnx.

Filip Rembiałkowski schrieb:
> 2008/5/5 Daniel Schuchardt <daniel_schuchardt@web.de
> <mailto:daniel_schuchardt@web.de>>:
>
>     Hey Group,
>
>     i know what all will say but i need to recreate the = operator for
>     datatypes varchar and integer in PostgreSQL 8.3.
>
>     Our Software Project has Millions of Lines and so it would be
>     difficult to check all queries and Datatypes. Also it works really
>     fine and we all know the risk of wrong auto casting.
>
>     Anyone knows the Syntax?
>
>
>
> http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html
>
> PS. you could search archives or even use google.
>
>
> --
> Filip Rembiałkowski

Daniel Schuchardt wrote:
> i know what all will say but i need to recreate the = operator for
> datatypes varchar and integer in PostgreSQL 8.3.
>
> Our Software Project has Millions of Lines and so it would be
> difficult
> to check all queries and Datatypes. Also it works really fine
> and we all
> know the risk of wrong auto casting.
>
> Anyone knows the Syntax?

What do you want to achieve?
Maybe there is already a different way to do what you want.

Yours,
Laurenz Albe

On Mon, May 05, 2008 at 11:18:37AM +0200, Daniel Schuchardt wrote:
> Hey Group,
>
> i know what all will say but i need to recreate the = operator for
> datatypes varchar and integer in PostgreSQL 8.3.
>
> Our Software Project has Millions of Lines and so it would be
> difficult to check all queries and Datatypes.

That technical debt is a risk to your whole project, and you need to
dedicate resources to paying it down.

<http://en.wikipedia.org/wiki/Technical_debt>

There are ways to get those automated casts, but they will only make
your situation worse in the long run.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter schrieb:
> That technical debt is a risk to your whole project, and you need to
> dedicate resources to paying it down.
>
> <http://en.wikipedia.org/wiki/Technical_debt>
>
> There are ways to get those automated casts, but they will only make
> your situation worse in the long run.
>
> Cheers,
> David.
>

*g* interesting standpoint and your right but:

it is impossible for us to find all the points where the new 8.3
behavoir would crash at the first time. so our next versions would be
very buggy and our customers wouldn't be happy ;-)
the next problem is that our service personal has to be traineed too;
they dont know much about casting, 81 does it automatically; problems
problems problems.

if it is not possible (i know it is) ;-) to recreate automatic casts in
83 we would not be able to upgrade to 83 the next years. the next
possible date would be in about 3-4 years with the next major release.

PS:
our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
that all use implicit casting.
and: everything is working fine ;-) :-P

so we have to choose another way.


cheers.

Hi,

Daniel Schuchardt wrote:
> David Fetter schrieb:
>> That technical debt is a risk to your whole project, and you need to
>> dedicate resources to paying it down.
>>
>> <http://en.wikipedia.org/wiki/Technical_debt>
>>
>> There are ways to get those automated casts, but they will only make
>> your situation worse in the long run.
>>
>> Cheers,
>> David.
>>
>
> *g* interesting standpoint and your right but:
>
> it is impossible for us to find all the points where the new 8.3
> behavoir would crash at the first time. so our next versions would be
> very buggy and our customers wouldn't be happy ;-)
> the next problem is that our service personal has to be traineed too;
> they dont know much about casting, 81 does it automatically; problems
> problems problems.
>
> if it is not possible (i know it is) ;-) to recreate automatic casts in
> 83 we would not be able to upgrade to 83 the next years. the next
> possible date would be in about 3-4 years with the next major release.
>
> PS:
> our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
> that all use implicit casting.
> and: everything is working fine ;-) :-P
>
> so we have to choose another way.

Well err... implicit table joining is also off per default I believe.
So if you had used it a lot you would have a similar problem.

Comparing int with text in general does not sound like a very good
idea to me. It should be quite easy to write a script to identify
such places so you can either change the datatypes (preferred) or
add the cast. Then rerun your automated regression tests...

Cheers
T.

On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote:
> David Fetter schrieb:
>> That technical debt is a risk to your whole project, and you need
>> to dedicate resources to paying it down.
>>
>> <http://en.wikipedia.org/wiki/Technical_debt>
>>
>> There are ways to get those automated casts, but they will only
>> make your situation worse in the long run.
>
> *g* interesting standpoint and your right but:

No buts.  If you create those automated casts, you are just putting
off the inevitable.

> it is impossible for us to find all the points where the new 8.3
> behavoir would crash at the first time. so our next versions would
> be very buggy and our customers wouldn't be happy ;-) the next
> problem is that our service personal has to be traineed too; they
> dont know much about casting, 81 does it automatically; problems
> problems problems.
>
> if it is not possible (i know it is) ;-) to recreate automatic casts
> in 83 we would not be able to upgrade to 83 the next years. the next
> possible date would be in about 3-4 years with the next major
> release.

You're just confirming the fact that your project is in existential
trouble.

> PS:
> our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
> that all use implicit casting.  and: everything is working fine ;-)
> :-P

How do you know?  8.3 removed the implicit casts precisely because
they were producing results that could most generously be describe as
"surprising."

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

>
> No buts.  If you create those automated casts, you are just putting
> off the inevitable.
>
yes but than it will become a future project. (i think with postgres 8,5
or 8,6 :-P )
>
> You're just confirming the fact that your project is in existential
> trouble.
>
no, we would get trouble if we upgrade. now everybody is happy :-).
btw: our first software release was with postgres 7.3 and since this
time we use this feature (about 6 years?).
> How do you know?  8.3 removed the implicit casts precisely because
> they were producing results that could most generously be describe as
> "surprising."
>
yeah thats an old discussion. we know about this and so we look for it.
it is a powerfull feature in 81 (and earlier) and yes if you use it in
the wrong way it will produce "surprising" results but if you use it in
the right way it is very powerfull. and if we get a suprising behavoir
we do in an explicit cast. done. ;-)
> Cheers,
> David.
>

daniel.

yeah, its clear that an upgrade from 8.1 to 8.3 is impossible for us
without a major relase.
there are to many changes so the whole project has to be rechecked.

another example:

in 83:

postgres=# SELECT 1::INTEGER||1::INTEGER;
ERROR:  operator does not exist: integer || integer at character 18
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.


83 with autocast from peter e:

 ERROR:  operator is not unique: integer || integer


in 81:

postgres=# SELECT 1::INTEGER||1::INTEGER;
 ?column?
----------
 11
(1 row)

Tino Wildenhain schrieb:
> Hi,
>
> Daniel Schuchardt wrote:
>> David Fetter schrieb:
>>> That technical debt is a risk to your whole project, and you need to
>>> dedicate resources to paying it down.
>>>
>>> <http://en.wikipedia.org/wiki/Technical_debt>
>>>
>>> There are ways to get those automated casts, but they will only make
>>> your situation worse in the long run.
>>>
>>> Cheers,
>>> David.
>>>
>>
>> *g* interesting standpoint and your right but:
>>
>> it is impossible for us to find all the points where the new 8.3
>> behavoir would crash at the first time. so our next versions would be
>> very buggy and our customers wouldn't be happy ;-)
>> the next problem is that our service personal has to be traineed too;
>> they dont know much about casting, 81 does it automatically; problems
>> problems problems.
>>
>> if it is not possible (i know it is) ;-) to recreate automatic casts
>> in 83 we would not be able to upgrade to 83 the next years. the next
>> possible date would be in about 3-4 years with the next major release.
>>
>> PS:
>> our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
>> that all use implicit casting.
>> and: everything is working fine ;-) :-P
>>
>> so we have to choose another way.
>
> Well err... implicit table joining is also off per default I believe.
> So if you had used it a lot you would have a similar problem.
>
> Comparing int with text in general does not sound like a very good
> idea to me. It should be quite easy to write a script to identify
> such places so you can either change the datatypes (preferred) or
> add the cast. Then rerun your automated regression tests...
>
> Cheers
> T.
it is not possible to do this. we also used automatic casting as a feature:
if a user starts a search, we never check the data type he puts in. in
81 postgresql automatically casts the user input in dependece of the
column. so all our search masks has to be checked to, edit fields need
to get a data type and so on.

example:

user types 1  -> our application automatically starts a search "LIKE '1%'"
user types 12  -> our application automatically starts a search "LIKE
'12%'"

and so on. and it doenst matter if the searchfield is char or integer,
81 does it automatically.
if it is a good or bad thing to do it is another question. it works in
81 and so we used this feature.

cheers.

David Fetter <david@fetter.org> writes:
> On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote:
>> our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
>> that all use implicit casting.  and: everything is working fine ;-)
>> :-P

> How do you know?  8.3 removed the implicit casts precisely because
> they were producing results that could most generously be describe as
> "surprising."

This should not be underestimated.  From the reports we've seen so far,
a very sizable fraction of people who find this kind of failure with 8.3
find out that their application was doing something unexpected in the
cases where it happened.  If you've got as many failures as you suggest,
I'd be willing to bet that some of them are bugs in your code, not just
reliance on an implicit feature.

            regards, tom lane

Hi,

Daniel Schuchardt wrote:
> Tino Wildenhain schrieb:
>> Hi,
>>
...
>> Comparing int with text in general does not sound like a very good
>> idea to me. It should be quite easy to write a script to identify
>> such places so you can either change the datatypes (preferred) or
>> add the cast. Then rerun your automated regression tests...
>>
>> Cheers
>> T.
> it is not possible to do this. we also used automatic casting as a feature:
> if a user starts a search, we never check the data type he puts in. in
> 81 postgresql automatically casts the user input in dependece of the
> column. so all our search masks has to be checked to, edit fields need
> to get a data type and so on.
>
> example:
>
> user types 1  -> our application automatically starts a search "LIKE '1%'"
> user types 12  -> our application automatically starts a search "LIKE
> '12%'"
>
> and so on. and it doenst matter if the searchfield is char or integer,
> 81 does it automatically.
> if it is a good or bad thing to do it is another question. it works in
> 81 and so we used this feature.

hum. (yes still think there is a lot of improvement possible) but
you could either change the schema to use text instead of int
or easily identify the places where LIKE is used and cast the left hand
expression explicitely to text. When I think about it you should
actually know for sure which places they are since you probably created
functional indexes?

Also it sounds like you are assembling SQL for queries? In this case
its even more easy to put the casts in place (or change the column
datatype). You mentioned support people - do they write raw SQL?

T.

Hi,

Daniel Schuchardt wrote:
...
> in 81:
>
> postgres=# SELECT 1::INTEGER||1::INTEGER;
> ?column?
> ----------
> 11
> (1 row)

*shudder* is this actually a port of an application originally
targeted at M*Sql? ;)

Are you using those columns somewhere with their real type - as
integer? I mean if you use them as text everywhere why not change
the type once?

T.

Tino Wildenhain schrieb:
> Hi,
>
> Daniel Schuchardt wrote:
> ...
>> in 81:
>>
>> postgres=# SELECT 1::INTEGER||1::INTEGER;
>> ?column?
>> ----------
>> 11
>> (1 row)
>
> *shudder* is this actually a port of an application originally
> targeted at M*Sql? ;)
>
> Are you using those columns somewhere with their real type - as
> integer? I mean if you use them as text everywhere why not change
> the type once?
>
> T.
*g*

yes, sure we have to CAST it now. thats no problem. but the problem is
to find all the places where to cast. and you see that there are many
possiblilitys.

another example?:

RAISE NOTICE "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"';  (here LoopDate is a DateTime)

another one:

here we need to add 4 CASTS. you see.....

CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
INTEGER AS $$
DECLARE R INTEGER;
BEGIN
 IF extract(month FROM $1)<11 THEN
     R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
 ELSE
     R:=extract(year FROM $1)||extract(month FROM $1)-1;
 END IF;
 RETURN  R;
END$$LANGUAGE plpgsql IMMUTABLE;

Daniel Schuchardt wrote:
> Tino Wildenhain schrieb:
>> Hi,
>>
>> Daniel Schuchardt wrote:
>> ...
>>> in 81:
>>>
>>> postgres=# SELECT 1::INTEGER||1::INTEGER;
>>> ?column?
>>> ----------
>>> 11
>>> (1 row)
>>
>> *shudder* is this actually a port of an application originally
>> targeted at M*Sql? ;)
>>
>> Are you using those columns somewhere with their real type - as
>> integer? I mean if you use them as text everywhere why not change
>> the type once?
>>
>> T.
> *g*
>
> yes, sure we have to CAST it now. thats no problem. but the problem is
> to find all the places where to cast. and you see that there are many
> possiblilitys.
>
> another example?:
>
> RAISE NOTICE "error during validation % :",
> 'ks:"'||ks||'"@"'||loopdate||'"';  (here LoopDate is a DateTime)
>
> another one:
>
> here we need to add 4 CASTS. you see.....
>
> CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
> INTEGER AS $$
> DECLARE R INTEGER;
> BEGIN
> IF extract(month FROM $1)<11 THEN
>     R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
> ELSE
>     R:=extract(year FROM $1)||extract(month FROM $1)-1;
> END IF;
> RETURN  R;
> END$$LANGUAGE plpgsql IMMUTABLE;

RETURN extract(year FROM $1)*100+extract(month FROM $1)-1;

was too clean and easy? ;))

Looks like a good oportunity to clean up your code before anything
unexpected happens :-)

Cheers
T.

Tom Lane schrieb:
> David Fetter <david@fetter.org> writes:
>
>> On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote:
>>
>>> our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
>>> that all use implicit casting.  and: everything is working fine ;-)
>>> :-P
>>>
>
>
>> How do you know?  8.3 removed the implicit casts precisely because
>> they were producing results that could most generously be describe as
>> "surprising."
>>
>
> This should not be underestimated.  From the reports we've seen so far,
> a very sizable fraction of people who find this kind of failure with 8.3
> find out that their application was doing something unexpected in the
> cases where it happened.  If you've got as many failures as you suggest,
> I'd be willing to bet that some of them are bugs in your code, not just
> reliance on an implicit feature.
>
>             regards, tom lane
>
>
yes true your right but lets make a calculation:

our application runs about 6 Years now so lets say there are 5% queries
that run still in a mistake caus of auto cast.
We have to check about 1200 views, 500 functions, every runtime created
query and so on. Lets say 95% of them run fine now. Now lets think we
check and rewrite all of them. If you let out the time (our customers
wont pay for such changes, they will have more errors the first time
than the last years), we should expect about X % errors because of the
changes (wrong parantheses and so on). So it would be a horror for us.
thats the problem.

examples:

RAISE EXCEPTION "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"';  (KS is DECLARED VARCHAR, LoopDate is
a TIMESTAMP);

stempz:=Round(SUM(COALESCE(ba_efftime, timediff(ba_anf, CAST(now() AS
TIMESTAMP(0) WITHOUT TIME ZONE))))) FROM bdea WHERE
timestamp_to_date(ba_anf)=current_date AND ba_ks=oks AND
*ba_ix||'~'||ba_op* IN (SELECT *a2_ab_ix||'~'||a2_n* FROM ab2_wkstplan
JOIN ab2 ON a2_id=a2w_a2_id WHERE a2w_oks=oks AND a2w_ks=ks AND
a2w_planweek=week);

this are integer fields. but they are unique with "*a2_ab_ix||'~'||a2_n*".

another one:

here we need to add 4 CASTS.
CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
INTEGER AS $$
DECLARE R INTEGER;
BEGIN
 IF extract(month FROM $1)<11 THEN
     R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
 ELSE
     R:=extract(year FROM $1)||extract(month FROM $1)-1;
 END IF;
 RETURN  R;
END$$LANGUAGE plpgsql IMMUTABLE;

Tino Wildenhain schrieb:
>
> RETURN extract(year FROM $1)*100+extract(month FROM $1)-1;
>
> was too clean and easy? ;))
>
> Looks like a good oportunity to clean up your code before anything
> unexpected happens :-)
>
> Cheers
> T.
>


LOL. Yes I don't like such easy things  :-P

But you see i have a function so i simply can change it ;-)

thnx for the hint, i picked up the best example ever :-P

Daniel.

Daniel Schuchardt wrote:
> Tino Wildenhain schrieb:
>>
>> RETURN extract(year FROM $1)*100+extract(month FROM $1)-1;
>>
>> was too clean and easy? ;))
>>
>> Looks like a good oportunity to clean up your code before anything
>> unexpected happens :-)
>>
>> Cheers
>> T.
>>
>
>
> LOL. Yes I don't like such easy things  :-P
 > RAISE NOTICE "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"';  (here LoopDate is a DateTime)

You know you can use more than one % in a raise and it will take care of
the data types?

create function atest() returns integer as $$
declare
   ks integer;
   loopdate timestamp;
begin
   ks := 3;
   loopdate := now();
   raise notice 'blah ks:%@%', ks, loopdate;
   return 1;
end;
$$ language plpgsql;

postgres=# select atest();
NOTICE:  blah ks:3@2008-05-06 09:58:55.812
 atest
-------
     1
(1 row)

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

> another example?:
>
> RAISE NOTICE "error during validation % :", 'ks:"'||ks||'"@"'||
> loopdate||'"';  (here LoopDate is a DateTime)

Ehm... What's wrong with RAISE NOTICE "error during validation
ks:"%"@"%" :', ks, loopdate; ? (I don't quite understand the purpose
of that colon at the end, btw).
Allows you to format the date to your liking too, just add a to_char
(loopdate, <format string>).

I know these were just a few examples of your troubles, but so far it
appears it's desirable to get rid of them for better code.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,481ffc80927661001715755!

Klint Gore schrieb:
> > RAISE NOTICE "error during validation % :",
> 'ks:"'||ks||'"@"'||loopdate||'"';  (here LoopDate is a DateTime)
>
> You know you can use more than one % in a raise and it will take care
> of the data types?
>
yes i know. the real code looks like this:

S:='another ABG found on ks:"'||ks||'"@"'||loopdate||'"';
PERFORM internalcreatemessage(current_user, 'W', S);


greets.

Alban Hertroys schrieb:
>> another example?:
>>
>> RAISE NOTICE "error during validation % :",
>> 'ks:"'||ks||'"@"'||loopdate||'"';  (here LoopDate is a DateTime)
>
> Ehm... What's wrong with RAISE NOTICE "error during validation
> ks:"%"@"%" :', ks, loopdate; ? (I don't quite understand the purpose
> of that colon at the end, btw).
> Allows you to format the date to your liking too, just add a
> to_char(loopdate, <format string>).
>
> I know these were just a few examples of your troubles, but so far it
> appears it's desirable to get rid of them for better code.
>
may be thats a good thing but its not possible. we have to do a major
rollout for this. we have to look through all the code.
and our next and more important project is to become .net compatible. we
have to upgrade our development enviroment because of trouble with
incompatibilities ;-)
so we have to stay on postgresql81 the next years.
if it is nice or not to work with autocasts doesnt matter, it was able
to do it so we used it for many years.

here another nice example:
(old.dbrid is INTEGER)

      EXECUTE 'UPDATE '||old.rc_tablename||' SET wvod=NULL WHERE
dbrid='||old.rc_dbrid;

TIMESTAMP
      f:=EXTRACT(MINUTE FROM new.bd_anf_rund-CAST('0:'||CAST(f3 AS
VARCHAR) AS TIME));

TIMESTAMP too
      new.bd_anf_rund:=timestamp_to_date(new.bd_anf) || ' ' || f4+f2 ||
':' || f1*tplrund;



PS:

i pick up only some string concatanation examples because i can identify
them fast. all other things i cant see so easy.
(varcharfield=integerfield ^^)

PSPS:
don't touch a running system ;-)

Daniel.

demo=# ALTER TABLE a ADD COLUMN d VARCHAR;
ALTER TABLE
demo=# UPDATE a SET d=current_date;
UPDATE 3
demo=# SELECT * FROM a WHERE d=current_date;
ERROR:  operator does not exist: character varying = date at character 24
HINT:  No operator matches the given name and argument type(s). You
might need t
o add explicit type casts.
LINE 1: SELECT * FROM a WHERE d=current_date;


so and now think what takes happen in plpgsql functions if you work with
variables.

DECLARE z INTEGER;

calculations

UPDATE a SET b=z WHERE xyz;

 GET DIAGNOSTICS rows = ROW_COUNT;
 IF rows=0 THEN
 ELSE
    FOR r IN SELECT * FROM a WHERE b=z....
 END IF;



i know that are hypotethical issues but i will show the risk.

Daniel Schuchardt schrieb:
> Hey Group,
>
> i know what all will say but i need to recreate the = operator for
> datatypes varchar and integer in PostgreSQL 8.3.
>
> Our Software Project has Millions of Lines and so it would be
> difficult to check all queries and Datatypes. Also it works really
> fine and we all know the risk of wrong auto casting.
>
> Anyone knows the Syntax?
>
> Thanks a lot for your great work.
>
>
> Daniel.

so it depends on ? if i need an explicit cast?

demo=# CREATE TABLE a (a VARCHAR, b VARCHAR);
CREATE TABLE
demo=# CREATE SEQUENCE test;
CREATE SEQUENCE
demo=# ALTER TABLE a ALTER COLUMN a SET DEFAULT nextval('test');
ALTER TABLE
demo=# INSERT INTO a (b) VALUES ('C');
INSERT 0 1
demo=# SELECT * FROM a;
 a | b
---+---
 1 | C
(1 row)

demo=# INSERT INTO a (b) VALUES (nextval('test'));
INSERT 0 1
demo=# INSERT INTO a (b) VALUES (5);
INSERT 0 1
demo=# SELECT * FROM a WHERE b=5;
ERROR:  operator does not exist: character varying = integer at character 24
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LINE 1: SELECT * FROM a WHERE b=5;
                               ^
demo=# SELECT * FROM a WHERE b='5';
 a | b
---+---
 4 | 5
(1 row)

demo=# UPDATE a SET a=nextval('test'), b=nextval('test');
UPDATE 3
demo=# UPDATE a SET b=nextval('test')+3;
UPDATE 3
demo=# UPDATE a SET b=nextval('test')+3||'~1';
UPDATE 3
demo=# SELECT * FROM a;
 a |  b
---+------
 5 | 20~1
 6 | 21~1
 7 | 22~1
(3 rows)

demo=# UPDATE a SET b=3||'~1';
UPDATE 3
demo=# SELECT * FROM a;
 a |  b
---+-----
 5 | 3~1
 6 | 3~1
 7 | 3~1
(3 rows)

demo=# SELECT * FROM a WHERE b=3||'~1';
 a |  b
---+-----
 5 | 3~1
 6 | 3~1
 7 | 3~1
(3 rows)

demo=# SELECT * FROM a WHERE b LIKE 3||'%';
 a |  b
---+-----
 5 | 3~1
 6 | 3~1
 7 | 3~1
(3 rows)

demo=# SELECT * FROM a WHERE b LIKE 3;
ERROR:  operator does not exist: character varying ~~ integer at
character 25
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LINE 1: SELECT * FROM a WHERE b LIKE 3;
                                ^
demo=# ALTER TABLE a ADD COLUMN c INTEGER;
ALTER TABLE
demo=# UPDATE a SET a=1, c=nextval('test');
UPDATE 3
demo=# SELECT * FROM a WHERE c=1;
 a | b | c
---+---+---
(0 rows)

demo=# SELECT * FROM a WHERE c='1';
 a | b | c
---+---+---
(0 rows)

demo=# SELECT * FROM a WHERE c=a;
ERROR:  operator does not exist: integer = character varying at character 24
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.
LINE 1: SELECT * FROM a WHERE c=a;
                               ^

demo=# SELECT * FROM a WHERE a=1;
ERROR:  operator does not exist: character varying = integer at character 24
HINT:  No operator matches the given name and argument type(s). You
might need t
o add explicit type casts.
LINE 1: SELECT * FROM a WHERE a=1;
                               ^
demo=# SELECT * FROM a WHERE a='1';
 a |  b  | c
---+-----+----
 1 | 3~1 | 23
 1 | 3~1 | 24
 1 | 3~1 | 25
(3 rows)


demo=# CREATE OR REPLACE FUNCTION test() RETURNS VOID AS $$ BEGIN RAISE
EXCEPTION '%', 1||'B'||current_date; RETURN; END$$LANGUAGE plpgsql;
CREATE FUNCTION
demo=# SELECT test();
ERROR:  1B2008-05-06

On Tue, May 06, 2008 at 11:31:55AM +0200, Daniel Schuchardt wrote:
> demo=# SELECT * FROM a WHERE b=3||'~1';
> a |  b
> ---+-----
> 5 | 3~1
> 6 | 3~1
> 7 | 3~1
> (3 rows)

The difference is the use of '||' which will autocast *if* one of the
arguments is text.

I was about to ask it you'd actually tested the examples you posted
because they looked like they should work fine.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Am Dienstag, 6. Mai 2008 schrieb Daniel Schuchardt:
> so it depends on ? if i need an explicit cast?

A type cast can be attempted in three different contexts (see also CREATE CAST
reference page):

- implicitly
- storage assignment
- explicitly

The explicit case is if you call CAST() or ::.  This is always allowed if an
appropriate cast routine is defined.

The implicit case is if an operator or function requires type A and the
expression is of type B.  This cast is only performed if the cast routine is
defined and allowed for the implicit context.  The change in 8.3 was
to "downgrade" many casting functions from implicit to assignment or
explicit.

The storage assignment case is if the value has type A and is about to be
stored into a column of type B.  This is allowed if the cast routine is
allowed for the assignment context.  This is, perhaps surprisingly, a
separate level between implicit and explicit casts.  So in reference to your
subject line, yes, INSERT and UPDATE do have different casting behavior than
SELECT.  (This is not really accurate, because the WHERE clause of an UPDATE
would follow the same rules as a WHERE clause in SELECT.  The assignment
context only applies for values that are really going to be stored.)

This is all in line with the SQL standard.