pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInserts with incomplete rows... NOT NULL constraints
4+ messages / 3 participants
[nested] [flat]
* Inserts with incomplete rows... NOT NULL constraints
@ 2004-08-11 12:35 Lars Erik Thorsplass <thorsplass@gmail.com>
2004-08-11 13:02 ` Re: Inserts with incomplete rows... NOT NULL constraints Peter Eisentraut <peter_e@gmx.net>
2004-08-11 13:34 ` Re: Inserts with incomplete rows... NOT NULL constraints Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 4+ messages in thread
From: Lars Erik Thorsplass @ 2004-08-11 12:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inserts with incomplete rows... NOT NULL constraints
2004-08-11 12:35 Inserts with incomplete rows... NOT NULL constraints Lars Erik Thorsplass <thorsplass@gmail.com>
@ 2004-08-11 13:02 ` Peter Eisentraut <peter_e@gmx.net>
2004-08-11 20:19 ` Fwd: Inserts with incomplete rows... NOT NULL constraints Lars Erik Thorsplass <thorsplass@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Peter Eisentraut @ 2004-08-11 13:02 UTC (permalink / raw)
To: Lars Erik Thorsplass <thorsplass@gmail.com>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Fwd: Inserts with incomplete rows... NOT NULL constraints
2004-08-11 12:35 Inserts with incomplete rows... NOT NULL constraints Lars Erik Thorsplass <thorsplass@gmail.com>
2004-08-11 13:02 ` Re: Inserts with incomplete rows... NOT NULL constraints Peter Eisentraut <peter_e@gmx.net>
@ 2004-08-11 20:19 ` Lars Erik Thorsplass <thorsplass@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Lars Erik Thorsplass @ 2004-08-11 20:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Inserts with incomplete rows... NOT NULL constraints
2004-08-11 12:35 Inserts with incomplete rows... NOT NULL constraints Lars Erik Thorsplass <thorsplass@gmail.com>
@ 2004-08-11 13:34 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2004-08-11 13:34 UTC (permalink / raw)
To: Lars Erik Thorsplass <thorsplass@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-08-11 20:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-11 12:35 Inserts with incomplete rows... NOT NULL constraints Lars Erik Thorsplass <thorsplass@gmail.com>
2004-08-11 13:02 ` Peter Eisentraut <peter_e@gmx.net>
2004-08-11 20:19 ` Lars Erik Thorsplass <thorsplass@gmail.com>
2004-08-11 13:34 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox