now i'm really confused. insert/update does autocast, where sometimes.

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