Compare with CHAR (more info) 
Author Message
 Compare with CHAR (more info)

Hi.

I couldn't see any reason why Postgres (7.1.3) act in this way:

SELECT * FROM g_city WHERE g_city_code LIKE 'AWA'  -- 1 result row
SELECT * FROM g_city WHERE g_city_code = 'AWA'  -- 0 result row

This has happen to a couple of row (not all) and when I execute UPDATE with
same values the row seems to be ok again (second example works).

Dump of table structure:

CREATE TABLE g_city (
 g_city_id integer DEFAULT nextval('"g_city_g_city_id_seq"'::text) NOT NULL,
 g_city_code character(3),
 g_country_code character(2),
 name character varying(50),
 active boolean,
 created timestamp with time zone DEFAULT now(),
 updated timestamp with time zone DEFAULT now(),
 a_person_id integer,
 Constraint g_city_pkey Primary Key (g_city_id)
);

CREATE UNIQUE INDEX g_city_g_city_code_key on g_city using btree (
g_city_code bpchar_ops );
CREATE  INDEX g_city_g_country_code_idx on g_city using btree (
g_country_code bpchar_ops );

CREATE CONSTRAINT TRIGGER fk_g_city_g_countr AFTER INSERT OR UPDATE ON
g_city  FROM g_country NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins" ('fk_g_city_g_countr', 'g_city',
'g_country', 'FULL', 'g_country_code', 'g_country_code');
CREATE CONSTRAINT TRIGGER fk_s_airport_g_city AFTER DELETE ON g_city  FROM
s_airport NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_cascade_del" ('fk_s_airport_g_city', 's_airport', 'g_city', 'FULL',
'g_city_code', 'g_city_code');
CREATE CONSTRAINT TRIGGER fk_s_airport_g_city AFTER UPDATE ON g_city  FROM
s_airport NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_noaction_upd" ('fk_s_airport_g_city', 's_airport', 'g_city',
'FULL', 'g_city_code', 'g_city_code');
CREATE CONSTRAINT TRIGGER fk_g_city_a_peopl AFTER INSERT OR UPDATE ON g_city
FROM a_person NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins" ('fk_g_city_a_peopl', 'g_city', 'a_person',
'FULL', 'a_person_id', 'a_person_id');
CREATE CONSTRAINT TRIGGER g_town_g_city_code_g_city_fk AFTER DELETE ON
g_city  FROM g_town NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del" ('g_town_g_city_code_g_city_fk', 'g_town',
'g_city', 'FULL', 'g_city_code', 'g_city_code');
CREATE CONSTRAINT TRIGGER g_town_g_city_code_g_city_fk AFTER UPDATE ON
g_city  FROM g_town NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd" ('g_town_g_city_code_g_city_fk', 'g_town',
'g_city', 'FULL', 'g_city_code', 'g_city_code');
CREATE CONSTRAINT TRIGGER s_package_g_city_rt_g_city_code_g_city_fk AFTER
DELETE ON g_city  FROM s_package_g_city_rt NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del"
('s_package_g_city_rt_g_city_code_g_city_fk', 's_package_g_city_rt',
'g_city', 'FULL', 'g_city_code', 'g_city_code');
CREATE CONSTRAINT TRIGGER s_package_g_city_rt_g_city_code_g_city_fk AFTER
UPDATE ON g_city  FROM s_package_g_city_rt NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"
('s_package_g_city_rt_g_city_code_g_city_fk', 's_package_g_city_rt',
'g_city', 'FULL', 'g_city_code', 'g_city_code');
CREATE CONSTRAINT TRIGGER s_lodging_contract_g_city_rt_g_city_code_g_city_fk
AFTER DELETE ON g_city  FROM s_lodging_contract_g_city_rt NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_del"
('s_lodging_contract_g_city_rt_g_city_code_g_city_fk',
's_lodging_contract_g_city_rt', 'g_city', 'FULL', 'g_city_code',
'g_city_code');
CREATE CONSTRAINT TRIGGER s_lodging_contract_g_city_rt_g_city_code_g_city_fk
AFTER UPDATE ON g_city  FROM s_lodging_contract_g_city_rt NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"
('s_lodging_contract_g_city_rt_g_city_code_g_city_fk',
's_lodging_contract_g_city_rt', 'g_city', 'FULL', 'g_city_code',
'g_city_code');

kind regards
Tomas



Sun, 25 Jul 2004 17:44:13 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. How to compare VARCHAR2 and CHAR?

2. Uppercase/Lowercase in comparing chars

3. Comparing RAW datatype with char buffers

4. IDS7.3 - Compared costs between CHAR and VARCHAR ?

5. Comparing char fields as dates

6. TEXT datatype: compared to CHAR and VARCHAR

7. Comparing Text Datatypes (stings upto 65000 chars) with Case sensitive results

8. Comparing a Null field with a char field

9. Need info on comparing memo fields

10. Comparing ODBMs -- Need Info

11. Urgent: Need info on LONG var char

12. Equivalents for CHAR(10) + CHAR(13)?


 
Powered by phpBB® Forum Software