Thread: select
how would i select all rows where a boolean value is neither t nor f.. ? ie if someone inserted without setting the boolean tag. Jeff MacDonald, ----------------------------------------------------- PostgreSQL Inc | Hub.Org Networking Services jeff@pgsql.com | jeff@hub.org www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 ----------------------------------------------------- Fascimile : 1 902 542 5386 IRC Nick : bignose
Hi, AFAIK, you can't enter a null value in a bool field, it has to be 1 or 0. \Indraneel On Sat, 23 Sep 2000, Jeff MacDonald wrote: > how would i select all rows where a boolean value is neither > t nor f.. ? > > ie if someone inserted without setting the boolean tag. > > Jeff MacDonald, > /************************************************************************. # Indraneel Majumdar ¡ E-mail: indraneel@123india.com # # Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com # # Centre for DNA Fingerprinting and Diagnostics, # # Hyderabad, India - 500076 # `************************************************************************/
bignose=# create table test ( bignose(# val1 int4, bignose(# val2 boolean); CREATE bignose=# bignose=# insert into test (val1) values (56); INSERT 322762 1 bignose=# bignose=# select * from test;val1 | val2 ------+------ 56 | (1 row) ta da :) On Sat, 23 Sep 2000, Indraneel Majumdar wrote: > Hi, > > AFAIK, you can't enter a null value in a bool field, it has to be 1 or 0. > > \Indraneel > > On Sat, 23 Sep 2000, Jeff MacDonald wrote: > > > how would i select all rows where a boolean value is neither > > t nor f.. ? > > > > ie if someone inserted without setting the boolean tag. > > > > Jeff MacDonald, > > > > /************************************************************************. > # Indraneel Majumdar � E-mail: indraneel@123india.com # > # Bioinformatics Unit (EMBNET node), � URL: http://scorpius.iwarp.com # > # Centre for DNA Fingerprinting and Diagnostics, # > # Hyderabad, India - 500076 # > `************************************************************************/ > Jeff MacDonald, ----------------------------------------------------- PostgreSQL Inc | Hub.Org Networking Services jeff@pgsql.com | jeff@hub.org www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 ----------------------------------------------------- Fascimile : 1 902 542 5386 IRC Nick : bignose
I'd assume this would work: select * from table where booleanfield is null; Stephan Szabo sszabo@bigpanda.com On Sat, 23 Sep 2000, Jeff MacDonald wrote: > how would i select all rows where a boolean value is neither > t nor f.. ? > > ie if someone inserted without setting the boolean tag.
bingo, thaks On Sat, 23 Sep 2000, Stephan Szabo wrote: > > I'd assume this would work: > select * from table where booleanfield is null; > > Stephan Szabo > sszabo@bigpanda.com > > On Sat, 23 Sep 2000, Jeff MacDonald wrote: > > > how would i select all rows where a boolean value is neither > > t nor f.. ? > > > > ie if someone inserted without setting the boolean tag. > > Jeff MacDonald, ----------------------------------------------------- PostgreSQL Inc | Hub.Org Networking Services jeff@pgsql.com | jeff@hub.org www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 ----------------------------------------------------- Fascimile : 1 902 542 5386 IRC Nick : bignose
Hi, there, <p>only possible is null, so select blah from tableblah where field is null; <p>Jeff MacDonald wrote: <blockquotetype="CITE">how would i select all rows where a boolean value is neither <br />t nor f.. ? <p>ie if someone insertedwithout setting the boolean tag. <p>Jeff MacDonald, <p>----------------------------------------------------- <br/>PostgreSQL Inc | Hub.Org Networking Services <br />jeff@pgsql.com | jeff@hub.org <br />www.pgsql.com | www.hub.org <br />1-902-542-0713 | 1-902-542-3657 <br />-----------------------------------------------------<br />Fascimile : 1 902 542 5386 <br />IRC Nick : bignose</blockquote><pre>-- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com</pre>
roberto wrote:
> Dear friends,
> I have this table
>
> table work{
> day date,
> hour integer,
> }
>
> select * from work;
>
> date | text
> -----------
> 1-1-2003 1
> 1-1-2003 1
> 2-1-2003 5
> 3-1-2003 10
> 5-1-2003 15
>
> how can i obtain this?
>
> date | text
> -----------
> 1-1-2003 2
> 2-1-2003 5
> 3-1-2003 10
> 4-1-2003 null
> 5-1-2003 15
> 6-1-2003 null
>
>
First , you need a sequence of days. Just create
a function like this:
CREATE OR REPLACE FUNCTION dateseq ( DATE , DATE )
RETURNS SETOF DATE
LANGUAGE 'plpgsql'
AS ' DECLARE bdate ALIAS FOR $1 ; edate ALIAS FOR $2 ; cdate DATE ; BEGIN cdate := bdate; WHILE cdate
<=edate LOOP RETURN NEXT cdate ; cdate := CAST ( cdate + interval ''1 day'' AS date ); END LOOP;
RETURN; END;
';
The function is like a table/view , where the fist function argument
is the start date , the second argument is the end date.
Now try :
SELECT ds.day, sum(w.hour)
FROM dateseq( '2003-1-1', '2003-1-6' ) AS ds (day)
LEFT JOIN work w ON ds.day=w.day
GROUP BY ds.day;
Regards, Janko
--
Janko Richter