Обсуждение: Using SELECT WHERE
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; 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
----- Original Message ----- From: "Michal Lijowski" <michal@cvu.wustl.edu> To: <pgsql-novice@postgresql.org> Sent: Thursday, April 15, 2004 9:03 PM Subject: [NOVICE] Using SELECT WHERE > RabStudies=> SELECT RabNo, ImplantDate, Comments FROM RabStudiesInfo > where implantdate <> 0001-01-01; You have to quote the date: ... where implantdate <> '0001-01-01'; chris
Michal Lijowski wrote: >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'; >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
On Thu, Apr 15, 2004 at 14:03:03 -0500, Michal Lijowski <michal@cvu.wustl.edu> wrote: > 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.
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: > 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
Michal Lijowski <michal@cvu.wustl.edu> writes:
> I made a database and I would like to select entries
> which have data not equal to the specified date.
> RabStudies=> SELECT RabNo, ImplantDate, Comments FROM RabStudiesInfo
> where implantdate <> 0001-01-01;
What you have on the right there is an integer expression with a value
of -1 (one minus one minus one). You need to put quotes around it to
make it be treated as a date constant:
where implantdate <> '0001-01-01';
Just FYI, pretty much any non-numeric literal has to be quoted as if it
were a string. Postgres usually infers the specific type from context
--- here, since you're comparing to a column of type date, the
unspecified-type literal will be presumed to be a date. You can add an
explicit cast if you need to force the literal to be converted to a
specific datatype.
where implantdate <> cast('0001-01-01' as date);
where implantdate <> '0001-01-01'::date;
The CAST syntax is SQL-standard, the :: syntax is a Postgres-ism.
regards, tom lane
On Thu, 15 Apr 2004, Michal Lijowski wrote: > 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).
Hey Michal, Well, modify your select statement formatting the implant date including the time and you'll answer your question yourself ;-) What I'm saying is that a date field also includes time information and this is causing your results. Cheers, Chris Michal Lijowski wrote: >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; > >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 > > > > > > > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > >