Received: from qw01.qwsecure.com (qw01.qwsecure.com [209.98.210.57]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA46180 for ; Mon, 18 Oct 1999 09:47:51 -0400 (EDT) (envelope-from mwright@pro-ns.net) Received: from markw_compaq (204.124.82.48 [204.124.82.48]) by qw01.qwsecure.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2232.9) id 4W4RZ6CX; Mon, 18 Oct 1999 09:12:47 -0500 Message-ID: <001e01bf196f$5c1bc010$c62812ac@markw_compaq> From: "Mark Wright" To: Subject: Re: [SQL] NOT NULL and postgres 6.5.2 Date: Mon, 18 Oct 1999 08:47:41 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3612.1700 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3612.1700 From: Oliver Elphick To: Stephan Bauer Cc: pgsql-sql@postgreSQL.org Date: Saturday, October 16, 1999 1:58 PM Subject: Re: [SQL] NOT NULL and postgres 6.5.2 >Stephan Bauer wrote: > >Hello all, > > > >I have an table like this: > > > >CREATE TABLE anzeigen ( > >... > > > > name varchar(30) NOT NULL, > >... > >); > > > >The following statement _will_ be inserted: > > > >INSERT INTO anzeigen ( ... name ...) > >VALUES ( ..., '', ...); > > > >What is my problem? > >An empty string is not the same as null. It is a real value whereas null >is an unknown/undefined/irrelevant value. Stephan, I bet you're coming from the Oracle world, where '' is the same as NULL. (No lie. Check this out: SQL> create table test_null (id number, name varchar(20) not null); Table created. SQL> insert into test_null values (1, ''); insert into test_null values (1, '') * ERROR at line 1: ORA-01400: cannot insert NULL into ("SYSTEM"."TEST_NULL"."NAME") This little bug can be _really_ irritating. Mark.