Thread: no records returned
running - (PostgreSQL) 7.1.3 I have loaded a table with stock market data. - - - ZRAN |2003-09-29| 20030929| 731487| 20.81| 20. ZRAN |2003-09-30| 20030930| 731488| 19.43| 20.1 ZRAN |2003-10-01| 20031001| 731489| 19.82| 19.9 ZRAN |2003-10-02| 20031002| 731490| 19.56| 20.3 ZRAN |2003-10-03| 20031003| 731491| 20.25| 21. (609398 rows) pma=> select count(*) from stck_dta_tbl_oprtnl; count ------ 609398 When I do a select * from stck_dta_tbl_oprtnl; I get the whole table just fine; but when I try and retrieve just 1 record which is out put when doing the select * ie: pma=> select * from stck_dta_tbl_oprtnl where stck_sym = 'ZRAN'; stck_sym|dta_date|dta_date_num|dta_date_dys|opn|hi|lw|cls|vol|unk --------+--------+------------+------------+---+--+--+---+---+--- (0 rows) I get 0 rows, and no errors from postmaster. Is there a limit to the number of rows in a table? I have also tried building a unique index composed of the stck_sym and dta_date but I still get 0 rows. I have also noticed that when setting up a cursor that the first fetch does NOT get the first record in the table, it also returns nothing with no errors from the postmaster. I am wondering if these bugs are related. Thanks all for your help!!!! Lynn
On Fri, Oct 24, 2003 at 04:27:16PM -0700, Lynn.Tilby@asu.edu wrote: ... > ZRAN |2003-10-03| 20031003| 731491| 20.25| 21. > (609398 rows) > pma=> select * from stck_dta_tbl_oprtnl where stck_sym = 'ZRAN'; How about pma=> select * from stck_dta_tbl_oprtnl where stck_sym like 'ZRAN%'; ? (What is the definition of your table?) Cheers, Patrick
is it possible that there are spaces on the end? what type is stck_sym? if it's varchar or text the padding won't be removed automatically. example: basement=# create table test (t varchar(6)); CREATE TABLE basement=# INSERT into test values( 'ZRAN '); INSERT 92249850 1 basement=# select * from test where t = 'ZRAN'; t --- (0 rows) basement=# On Oct 24, 2003, at 5:27 PM, Lynn.Tilby@asu.edu wrote: > > running - (PostgreSQL) 7.1.3 > > I have loaded a table with stock market data. > > - > - > - > ZRAN |2003-09-29| 20030929| 731487| 20.81| 20. > ZRAN |2003-09-30| 20030930| 731488| 19.43| 20.1 > ZRAN |2003-10-01| 20031001| 731489| 19.82| 19.9 > ZRAN |2003-10-02| 20031002| 731490| 19.56| 20.3 > ZRAN |2003-10-03| 20031003| 731491| 20.25| 21. > (609398 rows) > > pma=> select count(*) from stck_dta_tbl_oprtnl; > count > ------ > 609398 > > When I do a select * from stck_dta_tbl_oprtnl; I get the whole table > just fine; but when I try and retrieve just 1 record which is out > put when doing the select * ie: > > pma=> select * from stck_dta_tbl_oprtnl where stck_sym = 'ZRAN'; > stck_sym|dta_date|dta_date_num|dta_date_dys|opn|hi|lw|cls|vol|unk > --------+--------+------------+------------+---+--+--+---+---+--- > (0 rows) > > I get 0 rows, and no errors from postmaster. > > Is there a limit to the number of rows in a table? > I have also tried building a unique index composed of the stck_sym and > dta_date but I still get 0 rows. > > I have also noticed that when setting up a cursor that the first fetch > does NOT get the first record in the table, it also returns nothing > with > no errors from the postmaster. I am wondering if these bugs are > related. > > Thanks all for your help!!!! > > Lynn > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Are the fields you are usiong varchar or char. If they are char fields they will be getting padded if you do not have the exact amount in the field. i.e. char(5) da will be padded da and 0s on the end up to the sizeof the char field. If varchar then you will be ok. If you do have a char then use the trim function to do the selects HTH Darren On Fri, 24 Oct 2003 Lynn.Tilby@asu.edu wrote: > > running - (PostgreSQL) 7.1.3 > > I have loaded a table with stock market data. > > - > - > - > ZRAN |2003-09-29| 20030929| 731487| 20.81| 20. > ZRAN |2003-09-30| 20030930| 731488| 19.43| 20.1 > ZRAN |2003-10-01| 20031001| 731489| 19.82| 19.9 > ZRAN |2003-10-02| 20031002| 731490| 19.56| 20.3 > ZRAN |2003-10-03| 20031003| 731491| 20.25| 21. > (609398 rows) > > pma=> select count(*) from stck_dta_tbl_oprtnl; > count > ------ > 609398 > > When I do a select * from stck_dta_tbl_oprtnl; I get the whole table > just fine; but when I try and retrieve just 1 record which is out > put when doing the select * ie: > > pma=> select * from stck_dta_tbl_oprtnl where stck_sym = 'ZRAN'; > stck_sym|dta_date|dta_date_num|dta_date_dys|opn|hi|lw|cls|vol|unk > --------+--------+------------+------------+---+--+--+---+---+--- > (0 rows) > > I get 0 rows, and no errors from postmaster. > > Is there a limit to the number of rows in a table? > I have also tried building a unique index composed of the stck_sym and > dta_date but I still get 0 rows. > > I have also noticed that when setting up a cursor that the first fetch > does NOT get the first record in the table, it also returns nothing with > no errors from the postmaster. I am wondering if these bugs are related. > > Thanks all for your help!!!! > > Lynn > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Darren Ferguson
On Sat, Oct 25, 2003 at 08:19:49PM -0700, Lynn.Tilby@asu.edu wrote:
...
> Could you explain to a newbie WHY the like and % at
> the end of the literal works when the normal select does
> not?
> > > ZRAN |2003-10-03| 20031003| 731491| 20.25| 21.
12345678
From that row, it looks as though your table definition says the first
column is a CHAR(8), rather than say varchar(8) or text. This means that
ZRAN is padded to 8 characters with spaces:
test=# create table zran (a char(8), b varchar(8), c text);
CREATE TABLE
test=# insert into zran values ('ZRAN','ZRAN','ZRAN');
INSERT 28334686 1
test=# select * from zran;
a | b | c
----------+------+------
ZRAN | ZRAN | ZRAN
(1 row)
test=# select * from zran where a='ZRAN ';
a | b | c
----------+------+------
ZRAN | ZRAN | ZRAN
(1 row)
Because we were guessing your table definition, we couldn't just add 4 spaces
after ZRAN, so we suggested "where a like 'ZRAN%'". LIKE understands % to
mean "0 or more anything", so it would match 'ZRANNN' too, which might not
be what you want.. Most sensible might be to change your table definition..
Cheers,
Patrick
Darren, Thanks for the thoughts... stck_sym is a varchar the rest are int's and floats's. Do you think that the size of the table could be part of the problem, at 600000+ records? Lynn > exact amount in the field. i.e. > > char(5) > > da will be padded da and 0s on the end up to the sizeof the char > field. > > If varchar then you will be ok. > > If you do have a char then use the trim function to do the selects > > HTH > Darren > > On Fri, 24 Oct 2003 Lynn.Tilby@asu.edu wrote: > > > > > running - (PostgreSQL) 7.1.3 > > > > I have loaded a table with stock market data. > > > > - > > - > > - > > ZRAN |2003-09-29| 20030929| 731487| 20.81| 20. > > ZRAN |2003-09-30| 20030930| 731488| 19.43| 20.1 > > ZRAN |2003-10-01| 20031001| 731489| 19.82| 19.9 > > ZRAN |2003-10-02| 20031002| 731490| 19.56| 20.3 > > ZRAN |2003-10-03| 20031003| 731491| 20.25| 21. > > (609398 rows) > > > > pma=> select count(*) from stck_dta_tbl_oprtnl; > > count > > ------ > > 609398 > > > > When I do a select * from stck_dta_tbl_oprtnl; I get the whole table > > just fine; but when I try and retrieve just 1 record which is out > > put when doing the select * ie: > > > > pma=> select * from stck_dta_tbl_oprtnl where stck_sym = 'ZRAN'; > > stck_sym|dta_date|dta_date_num|dta_date_dys|opn|hi|lw|cls|vol|unk > > --------+--------+------------+------------+---+--+--+---+---+--- > > (0 rows) > > > > I get 0 rows, and no errors from postmaster. > > > > Is there a limit to the number of rows in a table? > > I have also tried building a unique index composed of the stck_sym > and > > dta_date but I still get 0 rows. > > > > I have also noticed that when setting up a cursor that the first > fetch > > does NOT get the first record in the table, it also returns nothing > with > > no errors from the postmaster. I am wondering if these bugs are > related. > > > > Thanks all for your help!!!! > > > > Lynn > > > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > -- > Darren Ferguson > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
Hi All, Solved this problem with a tip from James Moe <jimoe@sohnen-moe.com> as follows: Try this: select * from stck_dta_tbl_oprtnl where stck_sym like 'ZRAN%'; It works PERFECTLY.... THANKS James!!! Lynn Quoting Brian Hirt <bhirt@mobygames.com>: > is it possible that there are spaces on the end? what type is > stck_sym? if it's varchar or text the padding won't be removed > automatically. > > example: > > basement=# create table test (t varchar(6)); > CREATE TABLE > basement=# INSERT into test values( 'ZRAN '); > INSERT 92249850 1 > basement=# select * from test where t = 'ZRAN'; > t > --- > (0 rows) > > basement=# > > > On Oct 24, 2003, at 5:27 PM, Lynn.Tilby@asu.edu wrote: > > > > > running - (PostgreSQL) 7.1.3 > > > > I have loaded a table with stock market data. > > > > - > > - > > - > > ZRAN |2003-09-29| 20030929| 731487| 20.81| 20. > > ZRAN |2003-09-30| 20030930| 731488| 19.43| 20.1 > > ZRAN |2003-10-01| 20031001| 731489| 19.82| 19.9 > > ZRAN |2003-10-02| 20031002| 731490| 19.56| 20.3 > > ZRAN |2003-10-03| 20031003| 731491| 20.25| 21. > > (609398 rows) > > > > pma=> select count(*) from stck_dta_tbl_oprtnl; > > count > > ------ > > 609398 > > > > When I do a select * from stck_dta_tbl_oprtnl; I get the whole table > > just fine; but when I try and retrieve just 1 record which is out > > put when doing the select * ie: > > > > pma=> select * from stck_dta_tbl_oprtnl where stck_sym = 'ZRAN'; > > stck_sym|dta_date|dta_date_num|dta_date_dys|opn|hi|lw|cls|vol|unk > > --------+--------+------------+------------+---+--+--+---+---+--- > > (0 rows) > > > > I get 0 rows, and no errors from postmaster. > > > > Is there a limit to the number of rows in a table? > > I have also tried building a unique index composed of the stck_sym > and > > dta_date but I still get 0 rows. > > > > I have also noticed that when setting up a cursor that the first > fetch > > does NOT get the first record in the table, it also returns nothing > > with > > no errors from the postmaster. I am wondering if these bugs are > > related. > > > > Thanks all for your help!!!! > > > > Lynn > > > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > >
Patrick, James Moe <jimoe@sohnen-moe.com> also suggested this... It WORKS!!! Could you explain to a newbie WHY the like and % at the end of the literal works when the normal select does not? Thanks, Lynn Quoting Patrick Welche <prlw1@newn.cam.ac.uk>: > On Fri, Oct 24, 2003 at 04:27:16PM -0700, Lynn.Tilby@asu.edu wrote: > ... > > ZRAN |2003-10-03| 20031003| 731491| 20.25| 21. > > (609398 rows) > > > pma=> select * from stck_dta_tbl_oprtnl where stck_sym = 'ZRAN'; > > How about > > pma=> select * from stck_dta_tbl_oprtnl where stck_sym like 'ZRAN%'; > > ? (What is the definition of your table?) > > Cheers, > > Patrick >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Sat, 25 Oct 2003 20:19:49 -0700 (MST), Lynn.Tilby@asu.edu wrote: >Could you explain to a newbie WHY the like and % at >the end of the literal works when the normal select does >not? > The "%" is a wildcard character in SQL, the same as "*" in many file systems. It matches 0 or more characters. Also "_" matches any single character, similar to "?", or "." in regular expressions. There is likely one or more spaces after the visible characters. Using a "%" after ZRAN matches ZRAN plus anything else following that pattern. The implementation of the LIKE operator is a somewhat uneven in my experience. Depending on the database it can only be used to match strings, or to do a case-insensitive compare, or is equivalent to "=" with "=" extended to string operations. In most cases, though, the best practice is to use it for strings especially when using regular expressions like "%"; and to use "=" for numeric and exact matching. - -- jimoe at sohnen-moe dot com pgp/gpg public key: http://www.keyserver.net/en/ -----BEGIN PGP SIGNATURE----- Version: PGPfreeware 5.0 OS/2 for non-commercial use Comment: PGP 5.0 for OS/2 Charset: cp850 wj8DBQE/nE7isxxMki0foKoRAkJjAJ9lpqeC8y+Go0tXclwIM8XzvaRd1QCgtn8i 26WzEwf8HNPY5iqY6ZckrmY= =DY5x -----END PGP SIGNATURE-----