Thread: Bug or my crooked hands?
Hello All, I have the following situation: CREATE TABLE "Contacts" ( "Contact" Serial, "StuffTheir" Text, "TheDate" datetime DEFAULT TEXT 'now', "Subj" Text, "CustomerId" int4, "Agent" int4, "ActionType" int2, "Done" bool default false not null, "ExecutionDate" datetime, "Whom" int4 ); Then I have inserted data from text file and do a query mdb=> select Contact from Contacts; Contacts ----------- -2112563299 -2111287024 -2110307960 -2110004687 -2109773688 -2109611925 -2108616455 ... mdb=> select Contact from Contacts where Contact=-2112563299; Contacts ------- (0 rows) What is this and how to treat it? Best regards, Yury ICQ 11831432 mailto:yura@vpcit.ru
> Hello All, > > I have the following situation: > > CREATE TABLE "Contacts" ( > "Contact" Serial, > "StuffTheir" Text, > "TheDate" datetime DEFAULT TEXT 'now', > "Subj" Text, > "CustomerId" int4, > "Agent" int4, > "ActionType" int2, > "Done" bool default false not null, > "ExecutionDate" datetime, > "Whom" int4 > ); > > Then I have inserted data from text file and do a query > > mdb=> select Contact from Contacts; > Contacts > ----------- > -2112563299 > -2111287024 > -2110307960 > -2110004687 > -2109773688 > -2109611925 > -2108616455 > ... How did you get negative values in a Serial - field?? Serial-fields default to positive numbers....
Hello J., Once, Wednesday, January 19, 2000, 4:19:12 PM, you wrote: >> Hello All, >> >> I have the following situation: >> >> CREATE TABLE "Contacts" ( >> "Contact" Serial, >> "StuffTheir" Text, >> "TheDate" datetime DEFAULT TEXT 'now', >> "Subj" Text, >> "CustomerId" int4, >> "Agent" int4, >> "ActionType" int2, >> "Done" bool default false not null, >> "ExecutionDate" datetime, >> "Whom" int4 >> ); >> >> Then I have inserted data from text file and do a query >> >> mdb=> select Contact from Contacts; >> Contacts >> ----------- >> -2112563299 >> -2111287024 >> -2110307960 >> -2110004687 >> -2109773688 >> -2109611925 >> -2108616455 >> ... JR> How did you get negative values in a Serial - field?? JR> Serial-fields default to positive numbers.... I have copied the data from MS Access table and there where negative values. I can't change data in field Contacts because they are using in other tables. -- Best regards, Yury ICQ 11831432 mailto:yura@vpcit.ru
Hello, On Wed, 19 Jan 2000, Yury Don wrote: > >> "Contact" Serial, > JR> How did you get negative values in a Serial - field?? > JR> Serial-fields default to positive numbers.... > > I have copied the data from MS Access table and there where negative > values. I can't change data in field Contacts because they are using > in other tables. I'd go along with the previous statement - a serial should only be a positive number. What was the schema for the table in MS Access? You may have to change that (by copying everything into a new table), and then moving it into Postgres. What are you using this column for anyway? Is it just an identifier? Regards, Mark. -- Mark Jewiss Knowledge Matters Limited http://www.knowledge.com
----- Original Message ----- From: Yury Don <yura@vpcit.ru> To: pgsql-general <pgsql-general@postgreSQL.org> Sent: Wednesday, January 19, 2000 11:32 AM Subject: Re[2]: [GENERAL] Bug or my crooked hands? > >> I have the following situation: > >> > >> CREATE TABLE "Contacts" ( > >> "Contact" Serial, [snipped fields] > >> ); > >> > >> Then I have inserted data from text file and do a query > >> > >> mdb=> select Contact from Contacts; > >> Contacts > >> ----------- > >> -2112563299 [snip] > >> ... > > JR> How did you get negative values in a Serial - field?? > JR> Serial-fields default to positive numbers.... > > I have copied the data from MS Access table and there where negative > values. I can't change data in field Contacts because they are using > in other tables. Speaking of which, what range have you got on that sequence (serial type = sequence AFAIK)? It couldn't be that postgres is looking at your WHERE and deciding that the value is out of range, so there can't be any records to return. What happens if you insert a new record and then try to look for that? -- Richard Huxton Archonet Ltd.
Hello Richard, Once, Thursday, January 20, 2000, 3:04:43 PM, you wrote: RH> ----- Original Message ----- RH> From: Yury Don <yura@vpcit.ru> RH> To: pgsql-general <pgsql-general@postgreSQL.org> RH> Sent: Wednesday, January 19, 2000 11:32 AM RH> Subject: Re[2]: [GENERAL] Bug or my crooked hands? >> >> I have the following situation: >> >> >> >> CREATE TABLE "Contacts" ( >> >> "Contact" Serial, RH> [snipped fields] >> >> ); >> >> >> >> Then I have inserted data from text file and do a query >> >> >> >> mdb=> select Contact from Contacts; >> >> Contacts >> >> ----------- >> >> -2112563299 RH> [snip] >> >> ... >> >> JR> How did you get negative values in a Serial - field?? >> JR> Serial-fields default to positive numbers.... >> >> I have copied the data from MS Access table and there where negative >> values. I can't change data in field Contacts because they are using >> in other tables. RH> Speaking of which, what range have you got on that sequence (serial type = RH> sequence AFAIK)? RH> It couldn't be that postgres is looking at your WHERE and deciding that the RH> value is out of range, so there can't be any records to return. RH> What happens if you insert a new record and then try to look for that? RH> -- RH> Richard Huxton RH> Archonet Ltd. RH> ************ I am already found that this is seems a bug in postgres with using of indexes on int4. I have created the table CREATE TABLE "tt" ( "cc" int4); COPY "tt" FROM stdin; -2112563299 -2111287024 -2110307960 ..... 2146589610 2146589611 2146589612 \. About 30 000 records totally Then I am doing the following: mdb=> select cc from tt where cc = -2112563299; cc ----------- -2112563299 (1 row) mdb=> create index i_tt_cc on tt (cc); CREATE mdb=> select cc from tt where cc = -2112563299; cc (0 rows) mdb=> drop index i_tt_cc; DROP mdb=> select cc from tt where cc = -2112563299; cc ----------- -2112563299 (1 row) So, whith index postgresql works incorectly in this situation. But when there are less records in the table (about 12 000), everything works well. I tested this on 3 different computers on Debian Linux 2.1 with postgresql 6.5.2 and 6.5.3. Best regards, Yury ICQ 11831432 mailto:yura@vpcit.ru