Thread: select an entry with a NULL date field
Hello, I have a problem with PostgreSQL when I try to select or delete an entry with an empty date. That's a typical entry Table tbl_date ----------------------------- entry_id 154 date_01 2000-01-15 date_02 this date is NULL name my_test ----------------------------- I want to select every entry containing date_02 as NULL I tried : >select entry_id from tbl_date where date_02=NULL; ERROR: parser: parse error at or near "null" >select entry_id from tbl_date where date_02=''; Not work, that's a wrong date format >select entry_id from tbl_date where date_02=""; Not work, "" considered as an attribute Thank you for your time! -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Marc Andre Paquin
Try: select entry_id from tbl_date where date02 is null; Kate Web Manager wrote: > Hello, > > I have a problem with PostgreSQL when I try to select or delete an entry > with an empty date. That's a typical entry > > Table tbl_date > ----------------------------- > entry_id 154 > date_01 2000-01-15 > date_02 this date is NULL > name my_test > ----------------------------- > > I want to select every entry containing date_02 as NULL > I tried : > >select entry_id from tbl_date where date_02=NULL; > ERROR: parser: parse error at or near "null" > >select entry_id from tbl_date where date_02=''; > Not work, that's a wrong date format > >select entry_id from tbl_date where date_02=""; > Not work, "" considered as an attribute > > Thank you for your time! > -- > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Marc Andre Paquin -- ================================================= Katherine (Kate) L. Collins Senior Software Engineer/Meteorologist Weather Services International (WSI Corporation) 4 Federal Street Billerica, MA 01821 EMAIL: klcollins@wsicorp.com PHONE: (978) 670-5110 FAX: (978) 670-5100 http://www.intellicast.com
Re: select an entry with a NULL date field
From
"
Date:
Try select entry_id from tbl_date where date_02 is NULL; miguel sofer > >Hello, > > >I have a problem with PostgreSQL when I try to select or delete an entry >with an empty date. That's a typical entry > >Table tbl_date >----------------------------- >entry_id 154 >date_01 2000-01-15 >date_02 this date is NULL >name my_test >----------------------------- > >I want to select every entry containing date_02 as NULL >I tried : >>select entry_id from tbl_date where date_02=NULL; >ERROR: parser: parse error at or near "null" >>select entry_id from tbl_date where date_02=''; >Not work, that's a wrong date format >>select entry_id from tbl_date where date_02=""; >Not work, "" considered as an attribute > >Thank you for your time! >-- >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ >Marc Andre Paquin >
The syntax you're looking for is: SELECT entry_id FROM tbl_date WHERE date_02 IS NULL; Mark Web Manager wrote: > > Hello, > > I have a problem with PostgreSQL when I try to select or delete an entry > with an empty date. That's a typical entry > > Table tbl_date > ----------------------------- > entry_id 154 > date_01 2000-01-15 > date_02 this date is NULL > name my_test > ----------------------------- > > I want to select every entry containing date_02 as NULL > I tried : > >select entry_id from tbl_date where date_02=NULL; > ERROR: parser: parse error at or near "null" > >select entry_id from tbl_date where date_02=''; > Not work, that's a wrong date format > >select entry_id from tbl_date where date_02=""; > Not work, "" considered as an attribute > > Thank you for your time! > -- > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Marc Andre Paquin
Comparing anything = NULL (if it would work) would always false, at least that's how other servers treat it. You have to use IS NULL. select entry_id from tbl_date where date_02 IS NULL; Henry -----Original Message----- From: Web Manager [mailto:web@inter-resa.com] Sent: Monday, August 14, 2000 9:03 AM To: pgsql-sql@postgresql.org Subject: [SQL] select an entry with a NULL date field Hello, I have a problem with PostgreSQL when I try to select or delete an entry with an empty date. That's a typical entry Table tbl_date ----------------------------- entry_id 154 date_01 2000-01-15 date_02 this date is NULL name my_test ----------------------------- I want to select every entry containing date_02 as NULL I tried : >select entry_id from tbl_date where date_02=NULL; ERROR: parser: parse error at or near "null" >select entry_id from tbl_date where date_02=''; Not work, that's a wrong date format >select entry_id from tbl_date where date_02=""; Not work, "" considered as an attribute Thank you for your time! -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Marc Andre Paquin
Use select entry_id from tbl_date where date_02 is null; NULL = NULL is undefined according to SQL standards. -- Jesus Aneiros Sosa mailto:aneiros@jagua.cfg.sld.cu http://jagua.cfg.sld.cu/~aneiros On Mon, 14 Aug 2000, Web Manager wrote: > ERROR: parser: parse error at or near "null" > >select entry_id from tbl_date where date_02=''; > Not work, that's a wrong date format > >select entry_id from tbl_date where date_02=""; > Not work, "" considered as an attribute > > Thank you for your time! > -- > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Marc Andre Paquin >
I think undefined is the exact result for that comparison, therefore the returned error. False value will not be an error. -- Jesus Aneiros Sosa mailto:aneiros@jagua.cfg.sld.cu http://jagua.cfg.sld.cu/~aneiros On Mon, 14 Aug 2000, Henry Lafleur wrote: > Comparing anything = NULL (if it would work) would always false, at least > that's how other servers treat it. You have to use IS NULL. > > select entry_id from tbl_date where date_02 IS NULL; > > Henry > > > -----Original Message----- > From: Web Manager [mailto:web@inter-resa.com] > Sent: Monday, August 14, 2000 9:03 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] select an entry with a NULL date field > > > Hello, > > > I have a problem with PostgreSQL when I try to select or delete an entry > with an empty date. That's a typical entry > > Table tbl_date > ----------------------------- > entry_id 154 > date_01 2000-01-15 > date_02 this date is NULL > name my_test > ----------------------------- > > I want to select every entry containing date_02 as NULL > I tried : > >select entry_id from tbl_date where date_02=NULL; > ERROR: parser: parse error at or near "null" > >select entry_id from tbl_date where date_02=''; > Not work, that's a wrong date format > >select entry_id from tbl_date where date_02=""; > Not work, "" considered as an attribute > > Thank you for your time! > -- > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Marc Andre Paquin >