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
