now i'm really confused. insert/update does autocast, where sometimes. - Mailing list pgsql-general

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