[SQL] fsync and Windows XP
What is the trick for getting the fsync feature to work with Windows XP. I've turned off disk caching and set fsync = on in my postgresql.conf, but records are still not immediately written to disk. Any help would be appreciated.
[SQL] it's not NULL, then what is it?
Hi Everybody, I have a table called gallo.sds_seq_reg_shw, which is like: canon=# \d gallo.sds_seq_reg_shw Table "gallo.sds_seq_reg_shw" Column | Type | Modifiers --+-+--- name | text| response | text| n| integer | source | text| test | text| ref | text| value| real| pvalue.term | real| stars.term | text| gtclass.test | text| fclass.test | text| gtclass.ref | text| fclass.ref | text| markerid | integer | maf | real| chromosome | text| physicalposition | integer | id | text| ctrast | text| I am intereseted in the column maf (which is real): canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf asc; maf - 0.000659631 0.000659631 0.000659631 0.000659631 . (trunacated for the interest of breivity) . Another way to look at this column is: canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf desc; maf - . (trunacated for the interest of breivity) . These rows shown are blanks, as far as I can tell. But... canon=# select count(maf) from gallo.sds_seq_reg_shw; count --- 67284 (1 row) canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf ISNULL; count --- 0 (1 row) canon=# canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf NOTNULL; count --- 67284 (1 row) My confusion is that if they are real and not null, what are they? How would I construct a query to do something like: select count(maf) from gallo.sds_seq_reg_shw where maf ISBLANK; Thank you in advance. Regards, Tena Sakai tsa...@gallo.ucsf.edu
Re: [SQL] it's not NULL, then what is it?
Tena Sakai wrote: Hi Everybody, I have a table called gallo.sds_seq_reg_shw, which is like: canon=# \d gallo.sds_seq_reg_shw Table "gallo.sds_seq_reg_shw" Column | Type | Modifiers --+-+--- name | text| response | text| n| integer | source | text| test | text| ref | text| value| real| pvalue.term | real| stars.term | text| gtclass.test | text| fclass.test | text| gtclass.ref | text| fclass.ref | text| markerid | integer | maf | real| chromosome | text| physicalposition | integer | id | text| ctrast | text| I am intereseted in the column maf (which is real): canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf asc; maf - 0.000659631 0.000659631 0.000659631 0.000659631 . (trunacated for the interest of breivity) . Another way to look at this column is: canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf desc; maf - . (trunacated for the interest of breivity) . These rows shown are blanks, as far as I can tell. But... canon=# select count(maf) from gallo.sds_seq_reg_shw; count --- 67284 (1 row) canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf ISNULL; count --- 0 (1 row) canon=# canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf NOTNULL; count --- 67284 (1 row) My confusion is that if they are real and not null, what are they? How would I construct a query to do something like: select count(maf) from gallo.sds_seq_reg_shw where maf ISBLANK; Thank you in advance. Regards, Tena Sakai tsa...@gallo.ucsf.edu Maybe something like select ']' || maf::text || '[' -- just to see where the value start/stops or select length(maf::text) but I suspect you're getting NAN or something unprintable in your environment? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] it's not NULL, then what is it?
Just out of curiosity did you try maf = 0? Edward W. Rouse From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Tena Sakai Sent: Tuesday, June 30, 2009 6:03 PM To: pgsql-sql@postgresql.org Subject: [SQL] it's not NULL, then what is it? Hi Everybody, I have a table called gallo.sds_seq_reg_shw, which is like: canon=# \d gallo.sds_seq_reg_shw Table "gallo.sds_seq_reg_shw" Column | Type | Modifiers --+-+--- name | text| response | text| n| integer | source | text| test | text| ref | text| value| real| pvalue.term | real| stars.term | text| gtclass.test | text| fclass.test | text| gtclass.ref | text| fclass.ref | text| markerid | integer | maf | real| chromosome | text| physicalposition | integer | id | text| ctrast | text| I am intereseted in the column maf (which is real): canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf asc; maf - 0.000659631 0.000659631 0.000659631 0.000659631 . (trunacated for the interest of breivity) . Another way to look at this column is: canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf desc; maf - . (trunacated for the interest of breivity) . These rows shown are blanks, as far as I can tell. But... canon=# select count(maf) from gallo.sds_seq_reg_shw; count --- 67284 (1 row) canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf ISNULL; count --- 0 (1 row) canon=# canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf NOTNULL; count --- 67284 (1 row) My confusion is that if they are real and not null, what are they? How would I construct a query to do something like: select count(maf) from gallo.sds_seq_reg_shw where maf ISBLANK; Thank you in advance. Regards, Tena Sakai tsa...@gallo.ucsf.edu
Re: [SQL] it's not NULL, then what is it?
... canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf ISNULL; count --- 0 (1 row) I believe count will only count not-null anyway so this will always return zero. Try count(*) instead of count(maf). Here's an example: st...@[local]=> select * from barr; LOG: duration: 0.226 ms a | b ---+--- a | b c | d | e (3 rows) st...@[local]=> select coalesce(a, 'a is null'), coalesce(b, 'b is null') from barr; LOG: duration: 0.283 ms coalesce | coalesce ---+-- a | b c | d a is null | e (3 rows) st...@[local]=> select count(a) from barr; LOG: duration: 0.339 ms count --- 2 (1 row) st...@[local]=> select count(*) from barr where a isnull; LOG: duration: 0.350 ms count --- 1 (1 row) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] it's not NULL, then what is it?
Steve Crawford wrote on 01.07.2009 00:39: canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf ISNULL; I believe count will only count not-null Correct SELECT count(some_col) FROM some_table; is the same as SELECT count(*) FROM some_table WHERE some_col IS NOT NULL; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] it's not NULL, then what is it?
Hi Rob, > Maybe something like > select ']' || maf::text || '[' -- just to see where the value > start/stops It prints many (1,132,691 to be exact) lines consisting of 7 space characters followed by many lines like: ]0.0106383[ ]0.0106383[ ]0.0106383[ > or > select length(maf::text) This results in many lines of 7 space characters, followed by a bunch of 9's, 10's, 8's... > but I suspect you're getting NAN or something unprintable in your > environment? Yes, me too. But, canon=# select maf canon-# from gallo.sds_seq_reg_shw canon-# where maf = NAN; ERROR: column "nan" does not exist LINE 3: where maf = NAN; ^ What can I put to the right of equal sign to make the query work? Regards, Tena Sakai tsa...@gallo.ucsf.edu -Original Message- From: Rob Sargent [mailto:robjsarg...@gmail.com] Sent: Tue 6/30/2009 3:24 PM To: Tena Sakai Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] it's not NULL, then what is it? Tena Sakai wrote: > > Hi Everybody, > > I have a table called gallo.sds_seq_reg_shw, > which is like: > > canon=# \d gallo.sds_seq_reg_shw >Table "gallo.sds_seq_reg_shw" > Column | Type | Modifiers > --+-+--- >name | text| >response | text| >n| integer | >source | text| >test | text| >ref | text| >value| real| >pvalue.term | real| >stars.term | text| >gtclass.test | text| >fclass.test | text| >gtclass.ref | text| >fclass.ref | text| >markerid | integer | >maf | real| >chromosome | text| >physicalposition | integer | >id | text| >ctrast | text| > > I am intereseted in the column maf (which is real): > > canon=# select maf from gallo.sds_seq_reg_shw > canon-# order by maf asc; >maf > - >0.000659631 >0.000659631 >0.000659631 >0.000659631 > . > (trunacated for the interest of breivity) > . > > Another way to look at this column is: > > canon=# select maf from gallo.sds_seq_reg_shw > canon-# order by maf desc; >maf > - > > > > . > (trunacated for the interest of breivity) > . > > These rows shown are blanks, as far as I can tell. > But... > > canon=# select count(maf) from gallo.sds_seq_reg_shw; >count > --- >67284 > (1 row) > > canon=# select count(maf) from gallo.sds_seq_reg_shw > canon-# where maf ISNULL; >count > --- >0 > (1 row) > > canon=# > canon=# select count(maf) from gallo.sds_seq_reg_shw > canon-# where maf NOTNULL; >count > --- >67284 > (1 row) > > My confusion is that if they are real and not null, > what are they? How would I construct a query to do > something like: > > select count(maf) >from gallo.sds_seq_reg_shw > where maf ISBLANK; > > Thank you in advance. > > Regards, > > Tena Sakai > tsa...@gallo.ucsf.edu > Maybe something like select ']' || maf::text || '[' -- just to see where the value start/stops or select length(maf::text) but I suspect you're getting NAN or something unprintable in your environment?
Re: [SQL] it's not NULL, then what is it?
Hi Edward, > Just out of curiosity did you try maf = 0? Yes, and this is what I get: canon=# select maf canon-# from gallo.sds_seq_reg_shw canon-# where maf = 0; maf - (0 rows) Regards, Tena Sakai tsa...@gallo.ucsf.edu -Original Message- From: pgsql-sql-ow...@postgresql.org on behalf of Edward W. Rouse Sent: Tue 6/30/2009 3:22 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] it's not NULL, then what is it? Just out of curiosity did you try maf = 0? Edward W. Rouse From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Tena Sakai Sent: Tuesday, June 30, 2009 6:03 PM To: pgsql-sql@postgresql.org Subject: [SQL] it's not NULL, then what is it? Hi Everybody, I have a table called gallo.sds_seq_reg_shw, which is like: canon=# \d gallo.sds_seq_reg_shw Table "gallo.sds_seq_reg_shw" Column | Type | Modifiers --+-+--- name | text| response | text| n| integer | source | text| test | text| ref | text| value| real| pvalue.term | real| stars.term | text| gtclass.test | text| fclass.test | text| gtclass.ref | text| fclass.ref | text| markerid | integer | maf | real| chromosome | text| physicalposition | integer | id | text| ctrast | text| I am intereseted in the column maf (which is real): canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf asc; maf - 0.000659631 0.000659631 0.000659631 0.000659631 . (trunacated for the interest of breivity) . Another way to look at this column is: canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf desc; maf - . (trunacated for the interest of breivity) . These rows shown are blanks, as far as I can tell. But... canon=# select count(maf) from gallo.sds_seq_reg_shw; count --- 67284 (1 row) canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf ISNULL; count --- 0 (1 row) canon=# canon=# select count(maf) from gallo.sds_seq_reg_shw canon-# where maf NOTNULL; count --- 67284 (1 row) My confusion is that if they are real and not null, what are they? How would I construct a query to do something like: select count(maf) from gallo.sds_seq_reg_shw where maf ISBLANK; Thank you in advance. Regards, Tena Sakai tsa...@gallo.ucsf.edu
Re: [SQL] it's not NULL, then what is it?
Hi Steve, > I believe count will only count not-null anyway > so this will always return zero. Understood. But that doesn't help me... What I need is a query expression that I can substitute for isblabla below: select maf from gallo.sds_seq_reg_shw where maf isblabla; Regards, Tena Sakai tsa...@gallo.ucsf.edu -Original Message- From: Steve Crawford [mailto:scrawf...@pinpointresearch.com] Sent: Tue 6/30/2009 3:39 PM To: Tena Sakai Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] it's not NULL, then what is it? ... > > > canon=# select count(maf) from gallo.sds_seq_reg_shw > canon-# where maf ISNULL; >count > --- >0 > (1 row) > I believe count will only count not-null anyway so this will always return zero. Try count(*) instead of count(maf). Here's an example: st...@[local]=> select * from barr; LOG: duration: 0.226 ms a | b ---+--- a | b c | d | e (3 rows) st...@[local]=> select coalesce(a, 'a is null'), coalesce(b, 'b is null') from barr; LOG: duration: 0.283 ms coalesce | coalesce ---+-- a | b c | d a is null | e (3 rows) st...@[local]=> select count(a) from barr; LOG: duration: 0.339 ms count --- 2 (1 row) st...@[local]=> select count(*) from barr where a isnull; LOG: duration: 0.350 ms count --- 1 (1 row)
Re: [SQL] it's not NULL, then what is it?
Hi Osvaldo, > Try: > SELECT count(*) FROM gallo.sds_seq_reg_shw; > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL; > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL; > Don't use count(maf), use count(*). Indeed! canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw; count - 4645647 (1 row) canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL; count - 4578363 (1 row) canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL; count --- 67284 (1 row) $ dc 4578363 67284 + p q 4645647 $ Many thanks, Osvald. Regards, Tena Sakai tsa...@gallo.ucsf.edu -Original Message- From: Osvaldo Kussama [mailto:osvaldo.kuss...@gmail.com] Sent: Tue 6/30/2009 6:49 PM To: Tena Sakai Subject: Re: [SQL] it's not NULL, then what is it? 2009/6/30 Tena Sakai : > Hi Everybody, > > I have a table called gallo.sds_seq_reg_shw, > which is like: > > canon=# \d gallo.sds_seq_reg_shw > Table "gallo.sds_seq_reg_shw" > Column | Type | Modifiers > --+-+--- > name | text | > response | text | > n | integer | > source | text | > test | text | > ref | text | > value | real | > pvalue.term | real | > stars.term | text | > gtclass.test | text | > fclass.test | text | > gtclass.ref | text | > fclass.ref | text | > markerid | integer | > maf | real | > chromosome | text | > physicalposition | integer | > id | text | > ctrast | text | > > I am intereseted in the column maf (which is real): > > canon=# select maf from gallo.sds_seq_reg_shw > canon-# order by maf asc; > maf > - > 0.000659631 > 0.000659631 > 0.000659631 > 0.000659631 > . > (trunacated for the interest of breivity) > . > > Another way to look at this column is: > > canon=# select maf from gallo.sds_seq_reg_shw > canon-# order by maf desc; > maf > - > > > > . > (trunacated for the interest of breivity) > . > > These rows shown are blanks, as far as I can tell. > But... > > canon=# select count(maf) from gallo.sds_seq_reg_shw; > count > --- > 67284 > (1 row) > > canon=# select count(maf) from gallo.sds_seq_reg_shw > canon-# where maf ISNULL; > count > --- > 0 > (1 row) > > canon=# > canon=# select count(maf) from gallo.sds_seq_reg_shw > canon-# where maf NOTNULL; > count > --- > 67284 > (1 row) > > My confusion is that if they are real and not null, > what are they? How would I construct a query to do > something like: > > select count(maf) > from gallo.sds_seq_reg_shw > where maf ISBLANK; > Try: SELECT count(*) FROM gallo.sds_seq_reg_shw; SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL; SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL; Don't use count(maf), use count(*). Osvaldo
Re: [SQL] it's not NULL, then what is it?
"Tena Sakai" writes: >>> My confusion is that if they are real and not null, >>> what are they? Good question. So far as I can see these must be some value that sprintf() is printing as spaces; but not NaN and not Infinity because float4out checks for those first. I would argue that this must be a bug in sprintf. What platform are you running on exactly? And for that matter, what PG version is this? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] it's not NULL, then what is it?
Hi Tom, > What platform are you running on exactly? It is redhat linux running on Dell hardware. uname -a returns: Linux vixen.egcrc.org 2.6.9-78.0.1.ELsmp #1 SMP Tue Jul 22 18:01:05 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux > And for that matter, what PG version is this? It is 8.3.6. Regards, Tena Sakai tsa...@gallo.ucsf.edu -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tue 6/30/2009 7:17 PM To: Tena Sakai Cc: Edward W. Rouse; pgsql-sql@postgresql.org Subject: Re: [SQL] it's not NULL, then what is it? "Tena Sakai" writes: >>> My confusion is that if they are real and not null, >>> what are they? Good question. So far as I can see these must be some value that sprintf() is printing as spaces; but not NaN and not Infinity because float4out checks for those first. I would argue that this must be a bug in sprintf. What platform are you running on exactly? And for that matter, what PG version is this? regards, tom lane
