[Sussex] Postgresql date fields

[Angelo Servini]

Sorry Paul and others

Your absolutely right, both Oracle and Postgres treat empty strings stored in fields as NULL, and as Geoff Teale pointed out (hangs my head in shame!), however in select conditions - an empty string does not equate to NULL.  So whereas:

	select * from table where field is NULL

will work

	select * from table where field = ''

will not.



> -----Original Message-----
> From: sussex-bounces at mailman.lug.org.uk
> [mailto:sussex-bounces at mailman.lug.org.uk]On Behalf Of Paul Morris
> Sent: Thursday, 27 October 2005 19:47
> To: LUG email list for the Sussex Counties
> Subject: Re: [Sussex] Postgresql date fields
> 
> 
> On Thursday 27 October 2005 11:36, Angelo Servini wrote:
> >
> > However, it may be like ORACLE where NULL values are 
> treated as empty
> > string, so
> >
> it's the other way around, empty strings are treated as NULL 
> values see 
> example below
> 
> SQL> desc test
>  Name                                      Null?    Type
>  ----------------------------------------- -------- 
> ----------------------------
>  TESTID                                    NOT NULL NUMBER(38)
>  TEXT                                               VARCHAR2(100)
> 
> 
> 
> SQL> insert into test values (1,NULL);
> 
> 1 row created.
> 
> SQL> commit;
> 
> Commit complete.
> 
> 
> SQL> select * from test where text is null;
> 
>     TESTID
> ----------
> TEXT
> --------------------------------------------------------------
> ------------------
>          1
> 
> 
> 
> SQL> insert into test values(2,'');
> 
> 1 row created.
> 
> SQL> commit;
> 
> Commit complete.
> 
> SQL> select * from test where text is null;
> 
>     TESTID
> ----------
> TEXT
> --------------------------------------------------------------
> ------------------
>          1
> 
> 
>          2
> 
> 
> 
> SQL> select * from test where text = '';
> 
> no rows selected
> 
> 
> 
> -- 
> Paul
> 
> _______________________________________________
> Sussex mailing list
> Sussex at mailman.lug.org.uk
> Sussex LUG Website: http://www.sussex.lug.org.uk/
> http://mailman.lug.org.uk/mailman/listinfo/sussex
> 

<humour>
I stayed up all night playing poker with tarot cards.  I got a full
house and four people died.
                -- Steven Wright
</humour>

--
Angelo Servini
Programmer/Analyst
Claybrook Computing Ltd
Sutherland House
Russell Way
Crawley
West Sussex.  RH10 1UH
* 01293 604955
* 01293 604099 (Fax)
* angelo.servini at claybrook.co.uk




The above information is confidential to the addressee and may be privileged.  Unauthorised access and use is prohibited. If you received this message in error please tell us by reply (or telephone the sender) and delete all copies from your system.

Internet communications are not secure and therefore this Company does not accept legal responsibility for the contents of this message. This e-mail message has been swept for the presence of detectable computer viruses, but we cannot guarantee that it is virus-free and you should scan this email for viruses prior to opening or saving any attachments.

If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful.

Please note that communications sent by or to any person through our system administrator.

Claybrook Computing Limited (Registered in England and Wales No 1287205) is a subsidiary of Claybrook Computing (Holdings) Limited
Registered Office: Sutherland House, Russell Way, Crawley, West Sussex RH10 1UH

A Xafinity Limited Company