[SQL] except on nulls?

[Daniel Kalchev]

I had the following weird experience. Hopefuly iy's my ignorance on the matter 
(but I sort of think this worked before...)

On PostgreSQL 7.0.2, I have an database with two tables, holding different 
versions of the data set (some rows missing in one of the tables). Example is:

CREATE TABLE "test1" (
"f1" text,
"f2" date,
"f3" "_text",
"f4" int4,
"f5" character varying(16)
);

Then table test2 with the same definition.

SELECT f1 FROM test2 EXCEPT SELECT f1 from test1;

produced empty result, although test2 contained few more rows.

Closer examination showed that both tables contained an 'null' row, with all 
fields null.

After removing both rows, the query would return the proper results.

Another query,

SELECT * from test2 EXCEPT SELECT * from test1;

failed too, giving the following error:

ERROR:  Unable to identify an operator '<>' for types '_text' and '_text'
You will have to retype this query using an explicit cast

Any idea why this is happening?

Daniel

Re: [SQL] Extracting data by months

[Daniel Kalchev]

Actually, PostgreSQL has specific function for this purpose. The query would 
be:

SELECT * FROM table WHERE date_part('month', date_field) = '8';

(example to select month 8)

If you use date_trunc, the selection will be by month/year - that is, 
date_trunc('month', date_field) will always result in the 1st monthday of the 
month. The year will be preserved.

Daniel

>>>John McKown said:
 > This might seem rather silly, but could you simply do something like:
 > 
 > select * from database
 >where date_field >= '01/01/2000'::date
 >and   date_field < '02/01/2000'::date;
 > 
 > Of course, if date_field could contain many different years, then this
 > would not get you the result you wanted.
 > 
 > John
 > 
 > On Thu, 3 Aug 2000, Antti Linno wrote:
 > 
 > > Lo.
 > > 
 > >  I'm in dire need of knowledge, how to extract data by month. Monthday
 > > and year arent' important, those I can't give from perl script, but what
 > > I do give to postgres are the numbers of the months. Date field is in
 > > timestamp. I thought about date_trunc, but I can't think of, how to get
 > > data with it. In script will be 12 queries, for each month, to get the
 > > news from that month.  
 > > Btw, is there somewhere a manual about date_trunc, the docs that come with
 > > RH6.1 distribution are somewhat short and lacking explanation of
 > > date_trunc.
 > > Antti
 > > 
 > > 
 > 

Re: [SQL] Extracting data by months

[Daniel Kalchev]

This is not exactly what the original question was about. You select the month 
is one specific year only.

What you do it however easier achieved by:

SELECT datums FROM jaunumi WHERE date_trunc('month', datums) = '2000-08-01';

datetime is an type full of tricks. :-)

Daniel

>>>"Sandis" said:
 > I do it like this:
 > 
 > SELECT datums FROM jaunumi
 > WHERE date_part('year',datetime(datums)) = '2000' AND
 >   date_part('month',datetime(datums)) = '08';
 > 
 > Where datums is a timestamp field.
 > 
 > Regards,
 > Sandis Jerics
 > www.mediaparks.lv
 > 
 > > This might seem rather silly, but could you simply do something like:
 > >
 > > select * from database
 > >where date_field >= '01/01/2000'::date
 > >and   date_field < '02/01/2000'::date;
 > >
 > > Of course, if date_field could contain many different years, then this
 > > would not get you the result you wanted.
 > >
 > > John
 > >
 > > On Thu, 3 Aug 2000, Antti Linno wrote:
 > >
 > > > Lo.
 > > >
 > > >  I'm in dire need of knowledge, how to extract data by month. Monthday
 > > > and year arent' important, those I can't give from perl script, but what
 > > > I do give to postgres are the numbers of the months. Date field is in
 > > > timestamp. I thought about date_trunc, but I can't think of, how to get
 > > > data with it. In script will be 12 queries, for each month, to get the
 > > > news from that month.
 > > > Btw, is there somewhere a manual about date_trunc, the docs that come
 > with
 > > > RH6.1 distribution are somewhat short and lacking explanation of
 > > > date_trunc.
 > > > Antti
 > > >
 > > >
 > >
 >