Re: [SQL] it's not NULL, then what is it?
So they were null, and null turns out to be a seven-character blank string!? Btw, you can change the displayed value of null with \pset null nil and you will seem 4+ million 'nil's in your output Tena Sakai wrote: 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 [email protected] -Original Message- From: Osvaldo Kussama [mailto:[email protected]] 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Xpath() return xml[]
Hi,
I have been developing in postgresql for a very short time. I am
currently having problems that I believe can be resolved with the right
query structure but as of yet have not been able to figure it out.
I am using xpath(xpathExpression, xml, Array[Array[]]) function form
8.3.7 (This is the version that I am currently implementing)
This function returns an xml[] ... However, the problem is that in the
xml array return I get the valid return values that I am looking for but
I also get empty sets. How can I return only the sets that returned
valid
Example of output:
Id | xml[]
1 |
2 |
3 | {}
4 | {}
Etc.
I want to only return the rows that did not return {}
Any help with this would be appreciated
Thank you.
Damian Tamayo
General Dynamics C4 Systems
This email message is for the sole use of the intended recipient(s) and
may contain GDC4S confidential or privileged information. Any
unauthorized review, use, disclosure is prohibited. If you are not an
intended recipient, please contact the sender by reply email and destroy
all copies of the original message.
Re: [SQL] it's not NULL, then what is it?
Hi Rob, > So they were null, Yes! > and null turns out to be a seven-character blank string!? I don't understand how that happens. Mr Tom Lane hinted that it might be a bug in sprintf... > Btw, you can change the displayed value of null with > \pset null nil > and you will seem 4+ million 'nil's in your output That is an excellent trick/skill! canon=# \pset null nil Null display is "nil". canon=# canon=# select maf from gallo.sds_seq_reg_shw canon-# order by maf desc canon-# limit 10; maf - nil nil nil nil nil nil nil nil nil nil (10 rows) canon=# Regards, Tena Sakai [email protected] -Original Message- From: Rob Sargent [mailto:[email protected]] Sent: Wed 7/1/2009 9:36 AM To: Tena Sakai Cc: [email protected] Subject: Re: [SQL] it's not NULL, then what is it? So they were null, and null turns out to be a seven-character blank string!? Btw, you can change the displayed value of null with \pset null nil and you will seem 4+ million 'nil's in your output Tena Sakai wrote: > > 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 > [email protected] > > > > > -Original Message- > From: Osvaldo Kussama [mailto:[email protected]] > 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 >
[SQL] Partitioned tables not using index for min and max 8.2.7?
Hi I am running the following query again a partitioned table in 8.2.7. It does index scans which is unexpected as there are indexes for the log_date column. min(log_date) from data.table "Aggregate (cost=739932.02..739932.02 rows=1 width=8)" " -> Append (cost=0.00..685106.21 rows=21930321 width=8)" "-> Seq Scan on table (cost=0.00..33827.10 rows=1215710 width=8)" "-> Seq Scan on table_yy2009mm03 table (cost=0.00..88056.39 rows=438839 width=8)" "-> Seq Scan on table_yy2009mm04 table (cost=0.00..204606.67 rows=7344967 width=8)" "-> Seq Scan on table_yy2009mm05 table (cost=0.00..159210.91 rows=5735091 width=8)" "-> Seq Scan on table_yy2009mm06 table (cost=0.00..199393.74 rows=7195574 width=8)" "-> Seq Scan on table_yy2009mm07 table (cost=0.00..11.40 rows=140 width=8)" though if i run it only agains the one table it is significantly faster and uses the index select min(log_date) from only data.table "Result (cost=0.06..0.07 rows=1 width=0)" " InitPlan" "-> Limit (cost=0.00..0.06 rows=1 width=8)" " -> Index Scan using idx_table_log_date_only on table (cost=0.00..68272.93 rows=1215710 width=8)" "Filter: (log_date IS NOT NULL)" Am I doing something wrong or is this expected. I tried the old method of SELECT col FROM table ORDER BY col DESC LIMIT 1 But it does not work either. -- Tim Haak Email: [email protected] Tel: +27 12 658 9019 begin:vcard fn:Timothy Haak n:Haak;Timothy email;internet:[email protected] tel;work:+27 12 658 9019 tel;cell:+27 83 778 7100 x-mozilla-html:TRUE version:2.1 end:vcard -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] it's not NULL, then what is it?
"Tena Sakai" writes: >> So they were null, > Yes! >> and null turns out to be a seven-character blank string!? > I don't understand how that happens. Mr Tom Lane > hinted that it might be a bug in sprintf... Well, that was before I read the messages where it turned out that they were actually nulls after all. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Partitioned tables not using index for min and max 8.2.7?
Tim Haak writes: > I am running the following query again a partitioned table in 8.2.7. It > does index scans which is unexpected as there are indexes for the > log_date column. The index min/max optimization only works on single tables at the moment. Sorry. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] it's not NULL, then what is it?
Many thanks, Tom. I wish I had known "\pset null nil" trick. It would have saved a few unnecessary emails. Regards, Tena Sakai [email protected] -Original Message- From: Tom Lane [mailto:[email protected]] Sent: Wed 7/1/2009 10:42 AM To: Tena Sakai Cc: Rob Sargent; [email protected] Subject: Re: [SQL] it's not NULL, then what is it? "Tena Sakai" writes: >> So they were null, > Yes! >> and null turns out to be a seven-character blank string!? > I don't understand how that happens. Mr Tom Lane > hinted that it might be a bug in sprintf... Well, that was before I read the messages where it turned out that they were actually nulls after all. regards, tom lane
