Re: BUG #1816: Insert null values on a null field - Mailing list pgsql-bugs

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/static/sql-altertable.html

HTH
--
   Richard Huxton
   Archonet Ltd