Re: [SQL] Dates and NULL's`

2011-05-10 Thread Lew
On 05/10/2011 12:48 PM, John Fabiani wrote: Hi, Maybe this is a dumb question but if I have a date field that contains a NULL will it show up when I ask for a where date range for the same date field. Where mydate>= "2011/04/01"::date and mydate<= "2011/04/30"::date With the above where will th

Re: [SQL] Dates and NULL's`

2011-05-10 Thread Theodore Petrosky
If you think of it more like a null is an unknown. two unknowns can never be the same. they are unknown. and a known date can never equal an UNknown date. if the unknown date is equal to a date, then it is known my head hurts!! Ted --- On Tue, 5/10/11, Brent Dombrowski wrote: > From: Br

Re: [SQL] Dates and NULL's`

2011-05-10 Thread Emi Lu
Where mydate>= "2011/04/01"::date and mydate<= "2011/04/30"::date With the above where will the NULL's be selected Here is what I get when I try: spi=> SELECT NULL::DATE>= '2011-04-01'::DATE AND NULL::DATE<= '2011-04-30'::DATE; ?column? -- (1 row) spi=> SELECT (NULL::DATE>=

Re: [SQL] Dates and NULL's`

2011-05-10 Thread Brent Dombrowski
On May 10, 2011, at 9:48 AM, John Fabiani wrote: > Hi, > Maybe this is a dumb question but if I have a date field that contains a NULL > will it show up when I ask for a where date range for the same date field. > > Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date > > With the

Re: [SQL] Dates and NULL's`

2011-05-10 Thread Emi Lu
if I have a date field that contains a NULL will it show up when I ask for a where date range for the same date field. Where mydate>= "2011/04/01"::date and mydate<= "2011/04/30"::date With the above where will the NULL's be selected I ask because I was always told that a NULL matches every

Re: [SQL] Sorting Issue

2011-05-10 Thread Scott Marlowe
On Tue, May 10, 2011 at 11:45 AM, Samuel Gendler wrote: > > > On Tue, May 10, 2011 at 9:47 AM, Tom Lane wrote: >> >> "Ozer, Pam" writes: >> > Isn't this the English standard for collation?  Or is this a non-c >> > locale as mentioned below?  Is there anyway around this? >> >> >        LC_COLLATE

Re: [SQL] Sorting Issue

2011-05-10 Thread Samuel Gendler
On Tue, May 10, 2011 at 9:47 AM, Tom Lane wrote: > "Ozer, Pam" writes: > > Isn't this the English standard for collation? Or is this a non-c > > locale as mentioned below? Is there anyway around this? > > >LC_COLLATE = 'en_US.utf8' > > en_US is probably using somebody's idea of "dictio

Re: [SQL] Dates and NULL's`

2011-05-10 Thread John Fabiani
On Tuesday, May 10, 2011 10:30:58 am Richard Broersma wrote: > On Tue, May 10, 2011 at 10:24 AM, John Fabiani wrote: > >> spi=> SELECT NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= > >> '2011-04-30'::DATE; > >> ?column? > >> -- > >> > >> (1 row) > > > > It looks like you are saying

Re: [SQL] Dates and NULL's`

2011-05-10 Thread Richard Broersma
On Tue, May 10, 2011 at 10:24 AM, John Fabiani wrote: >> spi=> SELECT NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= >> '2011-04-30'::DATE; >>  ?column? >> -- >> >> (1 row) > It looks like you are saying the NULLS will be returned too The WHERE clause will only return rows is the ar

Re: [SQL] Dates and NULL's`

2011-05-10 Thread John Fabiani
On Tuesday, May 10, 2011 10:16:21 am Richard Broersma wrote: > On Tue, May 10, 2011 at 9:48 AM, John Fabiani wrote: > > Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date > > With the above where will the NULL's be selected > > Here is what I get when I try: > > spi=> SELECT

Re: [SQL] Dates and NULL's`

2011-05-10 Thread Richard Broersma
On Tue, May 10, 2011 at 9:48 AM, John Fabiani wrote: > Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date > With the above where will the NULL's be selected Here is what I get when I try: spi=> SELECT NULL::DATE >= '2011-04-01'::DATE AND NULL::DATE <= '2011-04-30'::DATE; ?

Re: [SQL] Sorting Issue

2011-05-10 Thread Edward W. Rouse
Looks like the sort is removing the spaces before sorting. cxh cxlm cxlp etc... Edward W. Rouse -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Ozer, Pam Sent: Monday, May 09, 2011 3:39 PM To: em...@encs.concordia.ca Cc: pgsql

[SQL] Dates and NULL's`

2011-05-10 Thread John Fabiani
Hi, Maybe this is a dumb question but if I have a date field that contains a NULL will it show up when I ask for a where date range for the same date field. Where mydate >= "2011/04/01"::date and mydate<= "2011/04/30"::date With the above where will the NULL's be selected I ask because I wa

Re: [SQL] Sorting Issue

2011-05-10 Thread Tom Lane
"Ozer, Pam" writes: > Isn't this the English standard for collation? Or is this a non-c > locale as mentioned below? Is there anyway around this? >LC_COLLATE = 'en_US.utf8' en_US is probably using somebody's idea of "dictionary order", which I believe includes ignoring spaces

Re: [SQL] Sorting Issue

2011-05-10 Thread Ozer, Pam
Isn't this the English standard for collation? Or is this a non-c locale as mentioned below? Is there anyway around this? LC_COLLATE = 'en_US.utf8' Thanks Pam -Original Message- From: Ozer, Pam Sent: Monday, May 09, 2011 3:13 PM To: 'Tom Lane'; Samuel Gendler Cc: em.

Re: [SQL] Sorting Issue

2011-05-10 Thread Ozer, Pam
I was wrong it is LC_COLLATE = 'en_US.utf8' -Original Message- From: Ozer, Pam Sent: Monday, May 09, 2011 3:13 PM To: 'Tom Lane'; Samuel Gendler Cc: em...@encs.concordia.ca; pgsql-sql@postgresql.org Subject: RE: [SQL] Sorting Issue The collate setting is LC_COLL

[SQL] view table with foreign key relative values

2011-05-10 Thread fpirotti
Hello List, I have searched extensively for the following question: Is it possible to view a table which has a column with a foreign key constraint with the values of that column automatically substituted with the corresponding values of the lookup table? Or is the only way to make my own view and