The following bug has been logged online:

Bug reference: 1816
Logged by: Rafael BArrios
Email address: rbarrios@emdis.net.co
PostgreSQL version: 8.02
Operating system: FEdora Core 2
Description: Insert null values on a null field
Details:

I have a table wich is referenced as foreing key for others, the problem is,
in my case my table is composed of 4 fields, 2 of them are FK but all of
them are not null by definition. If i insert the data for the FK and leave
the other 2 empty (null), the DB accept and insert the record without any
problem.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Rafael BArrios wrote:
> Description: Insert null values on a null field
>
> I have a table wich is referenced as foreing key for others, the problem is,
> in my case my table is composed of 4 fields, 2 of them are FK but all of
> them are not null by definition. If i insert the data for the FK and leave
> the other 2 empty (null), the DB accept and insert the record without any
> problem.

Can you provide definitions of both tables and and example of an insert
that should fail.

--
Richard Huxton
Archonet Ltd

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

Rafael Barrios wrote:
> Richard, thanks for your response.
> here is te information you request:

Don't forget to cc: the mailing list.

> CREATE TABLE tbarrio (
> bar_coddep character varying(2) NOT NULL,
> bar_codmun character varying(3) NOT NULL,
> bar_codbarrio character varying(3) NOT NULL,
> bar_barrio character varying(40) NOT NULL,
> bar_zongeografica character varying(1) NOT NULL
> );
>
> ALTER TABLE ONLY tbarrio
> ADD CONSTRAINT pk_tbarrio PRIMARY KEY (bar_coddep, bar_codmun, bar_codbarrio);
>
> ALTER TABLE ONLY tbarrio
> ADD CONSTRAINT fk_tbarrio_tmunicipio FOREIGN KEY (bar_coddep, bar_codmun)
> REFERENCES tmunicipio(mun_coddep, mun_codmun) ON UPDATE CASCADE;

> failling Insert: (adds records to table 'barrio' observe empty values..)
>
> INSERT INTO tbarrio VALUES ('05', '001', '', '', '');

These aren't null - they are empty strings. You haven't forbidden empty
strings.

You'll want constraint(s) to do something like:

ALTER TABLE tbarrio
ADD CONSTRAINT no_empty_codbarrio
CHECK (bar_codbarrio > '');

http://www.postgresql.org/docs/8.0/s...ltertable.html

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thanks for your valuable response.
---------- Original Message -----------
From: Richard Huxton <dev@archonet.com>
To: Rafael Barrios <rbarrios@emdis.net.co>, pgsql-bugs@postgresql.org
Sent: Thu, 11 Aug 2005 08:14:13 +0100
Subject: Re: [BUGS] BUG #1816: Insert null values on a null field

> Rafael Barrios wrote:
> > Richard, thanks for your response.
> > here is te information you request:
>
> Don't forget to cc: the mailing list.
>
> > CREATE TABLE tbarrio (
> > bar_coddep character varying(2) NOT NULL,
> > bar_codmun character varying(3) NOT NULL,
> > bar_codbarrio character varying(3) NOT NULL,
> > bar_barrio character varying(40) NOT NULL,
> > bar_zongeografica character varying(1) NOT NULL
> > );
> >
> > ALTER TABLE ONLY tbarrio
> > ADD CONSTRAINT pk_tbarrio PRIMARY KEY (bar_coddep, bar_codmun,
bar_codbarrio);
> >
> > ALTER TABLE ONLY tbarrio
> > ADD CONSTRAINT fk_tbarrio_tmunicipio FOREIGN KEY (bar_coddep, bar_codmun)
> > REFERENCES tmunicipio(mun_coddep, mun_codmun) ON UPDATE CASCADE;
>
> > failling Insert: (adds records to table 'barrio' observe empty values..)
> >
> > INSERT INTO tbarrio VALUES ('05', '001', '', '', '');
>
> These aren't null - they are empty strings. You haven't forbidden
> empty strings.
>
> You'll want constraint(s) to do something like:
>
> ALTER TABLE tbarrio
> ADD CONSTRAINT no_empty_codbarrio
> CHECK (bar_codbarrio > '');
>
> http://www.postgresql.org/docs/8.0/s...ltertable.html
>
> HTH
> --
> Richard Huxton
> Archonet Ltd
------- End of Original Message -------


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org