Re: Using SELECT WHERE

#1

On Thu, 15 Apr 2004, Michal Lijowski wrote:

Quote:

I made a database and I would like to select entries
which have data not equal to the specified date.
I have postgresql-7.3.4-11 on Red Hat Fedora Core 1.

Here is the command

RabStudies=> SELECT RabNo, ImplantDate, Comments FROM RabStudiesInfo
where implantdate <> 0001-01-01;

You forgot to quote the date, what you're getting is
Date <> Integer (1-1-1).

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2

On Thu, Apr 15, 2004 at 14:03:03 -0500,
Michal Lijowski <michal (AT) cvu (DOT) wustl.edu> wrote:

Quote:

I made a database and I would like to select entries
which have data not equal to the specified date.
I have postgresql-7.3.4-11 on Red Hat Fedora Core 1.

Here is the command

RabStudies=> SELECT RabNo, ImplantDate, Comments FROM RabStudiesInfo
where implantdate <> 0001-01-01;

I believe that you just want to quote the constant. 0001-01-01 is being
treated as 0 and that is somehow being compared to the date. I am not
sure exactly what is going on as integers don't seem to promote to
date or time. EXPLAIN VERBOSE shpws what's happening but I don't know
the oids of operators by heart so it isn't immediately obvious to me
what is happening, but you can use that to check it out yourself if
you want.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#3

The given expression could be interpreted as a timestamp.
Try casting it explicitly to date:

.... WHERE implantdate!='0001-01-01'::date ...

but that's just a guess.

HTH
Michal Lijowski wrote:

Quote:

RabStudies=> SELECT RabNo, ImplantDate, Comments FROM RabStudiesInfo
where implantdate <> 0001-01-01;

and I get

rabno | implantdate | comments

307 | 2004-02-23 | No mimetic No target
309 | 2004-02-23 | No mimetic No target; Frame 12 study terminated
314 | 2004-02-27 | No mimetic No target
311 | 2004-02-27 | No mimetic No target
310 | 2004-02-26 | No mimetic No target No decoy
315 | 0001-01-01 | No Tumor
320 | 0001-01-01 | No Tumor
316 | 0001-01-01 | No Tumor
313 | 0001-01-01 | No Tumor

Why entries with implandate are listed?
It is the same when I use != instead of <>.

--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#4

Michal Lijowski wrote:

Quote:

I made a database and I would like to select entries
which have data not equal to the specified date.
I have postgresql-7.3.4-11 on Red Hat Fedora Core 1.

Here is the command

RabStudies=> SELECT RabNo, ImplantDate, Comments FROM RabStudiesInfo
where implantdate <> 0001-01-01;

Try quoting the date:

SELECT RabNo, ImplantDate, Comments FROM RabStudiesInfo where implantdate <> '0001-01-01';

Quote:

and I get

rabno | implantdate | comments

307 | 2004-02-23 | No mimetic No target
309 | 2004-02-23 | No mimetic No target; Frame 12 study terminated
314 | 2004-02-27 | No mimetic No target
311 | 2004-02-27 | No mimetic No target
310 | 2004-02-26 | No mimetic No target No decoy
315 | 0001-01-01 | No Tumor
320 | 0001-01-01 | No Tumor
316 | 0001-01-01 | No Tumor
313 | 0001-01-01 | No Tumor

Why entries with implandate are listed?
It is the same when I use != instead of <>.

Thanks,

Michal

Ron

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Using SELECT WHERE

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: Using SELECT WHERE - 04-20-2004 , 11:58 AM

Re: Using SELECT WHERE - 04-20-2004 , 01:04 PM

Re: Using SELECT WHERE - 04-20-2004 , 01:06 PM

Re: Using SELECT WHERE - 04-20-2004 , 04:58 PM