Thread: Inserts with incomplete rows... NOT NULL constraints

Am trying to migrate some old data to a new database schema. I have
dumped the old data as SQL statements. The new datastructure just
contains some added fields to a few tables.

My plan was to just create a new database with the new structure, dump
the content only as SQL insert statements. And just run the statements
contained in the dump on the new database.

The problem am now facing is that postgres will try to insert a NULL
value for fields not specified in the insert query and that are
defined as NOT NULL in the table structure. Is this the correct
behaviour?

I would expect NULL fields not specified in the insert to get NULL
inserted automatically. But that fields which are NOT NULL in the
table structure gets inserted a NULL value too seems odd.

Am loosing my mind in this heat but, you should be able to insert just
half the fields of a table record if the datastructure would allow it?
Wouldnt you?

I feel like a such a noob :(

Please advise..

-- 
L.E.Thorsplass

Am Mittwoch, 11. August 2004 14:35 schrieb Lars Erik Thorsplass:
> I would expect NULL fields not specified in the insert to get NULL
> inserted automatically. But that fields which are NOT NULL in the
> table structure gets inserted a NULL value too seems odd.

More accurately, the default value is inserted, which in turn is null if you 
didn't specify one.  You might find it odd that default values that are 
inconsistent with constraints are allowed, but I don't see any reasonable 
alternative.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Lars Erik Thorsplass wrote:
> The problem am now facing is that postgres will try to insert a NULL
> value for fields not specified in the insert query and that are
> defined as NOT NULL in the table structure. Is this the correct
> behaviour?

Actually, what it's doing is inserting the DEFAULT value for the field 
in question. If you don't specify a DEFAULT, it assumes null.

# CREATE TABLE test_tbl (a integer, b text DEFAULT 'bbb', c text);
# INSERT INTO test_tbl (a) VALUES (1);
# SELECT * FROM test_tbl; a |  b  | c
---+-----+--- 1 | bbb |

If you specify NOT NULL and don't want to provide a value you'll need to 
set a DEFAULT.

--   Richard Huxton  Archonet Ltd

Sorry about that, forgot to send to the list.


---------- Forwarded message ----------
From: Lars Erik Thorsplass <thorsplass@gmail.com>
Date: Wed, 11 Aug 2004 22:18:24 +0200
Subject: Re: [SQL] Inserts with incomplete rows... NOT NULL constraints
To: Peter Eisentraut <peter_e@gmx.net>

> Am Mittwoch, 11. August 2004 14:35 schrieb Lars Erik Thorsplass:
> > I would expect NULL fields not specified in the insert to get NULL
> > inserted automatically. But that fields which are NOT NULL in the
> > table structure gets inserted a NULL value too seems odd.
>
> More accurately, the default value is inserted, which in turn is null if you
> didn't specify one.  You might find it odd that default values that are
> inconsistent with constraints are allowed, but I don't see any reasonable
> alternative.

Thanks for clearing that up. Guess I'll work around my migration
problems with placeholder default values.

I tried another approach at the same time. I tried to alter the tables
that had changed between versions of the sql structure. The problem
with this is that postgres is unable to add a field to a table with a
NOT NULL constraint. You can add a NOT NULL constraint to the field
afterwards but now all records in the table have a NULL value in the
field. So you wont be able to do that until you change all the values.
No default value was specified for the new field. As with the other
approach this might also work if I enter some default values.

Best regards,

L.E.Thorsplass



-- 
L.E.Thorsplass