pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNULL becomes default
5+ messages / 4 participants
[nested] [flat]
* NULL becomes default
@ 2006-08-17 18:11 Julian Scarfe <julian@avbrief.com>
0 siblings, 2 replies; 5+ messages in thread
From: Julian Scarfe @ 2006-08-17 18:11 UTC (permalink / raw)
To: pgsql-sql
A surrogate key has been introduced on a table with modifiers:
Column | Type |
Modifiers
-------------+-----------------------------+------------------------------------------------------------
new_id | integer | not null default
nextval(('some_id_seq'::text)::regclass)
However, some legacy software inserts into the table by first getting column
names and then composing an INSERT for all the columns, filling in any
undefined values with NULLs.
Thus when it attempts to insert a row I get a:
ERROR: null value in column "new_id" violates not-null constraint
Thus I would like NULLs in such INSERTs to be treated as DEFAULT rather than
violating the constraint. Is there an easy way to do that at the database
level?
Thanks
Julian
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULL becomes default
@ 2006-08-17 18:22 Sumeet <asumeet@gmail.com>
parent: Julian Scarfe <julian@avbrief.com>
1 sibling, 0 replies; 5+ messages in thread
From: Sumeet @ 2006-08-17 18:22 UTC (permalink / raw)
To: Julian Scarfe <julian@avbrief.com>; +Cc: pgsql-sql
Hi Julian u need to reset your sequence, i had run through the same problem.
On 8/17/06, Julian Scarfe <julian@avbrief.com> wrote:
>
> A surrogate key has been introduced on a table with modifiers:
>
> Column | Type |
> Modifiers
>
> -------------+-----------------------------+------------------------------------------------------------
> new_id | integer | not null default
> nextval(('some_id_seq'::text)::regclass)
>
> However, some legacy software inserts into the table by first getting
> column
> names and then composing an INSERT for all the columns, filling in any
> undefined values with NULLs.
>
> Thus when it attempts to insert a row I get a:
>
> ERROR: null value in column "new_id" violates not-null constraint
To fix this, you need to do something like:
SELECT setval('public.fdata _fid_seq', max(fid)+1) FROM fdata;
This will make sure that the next *value* your *sequence* generates is
greater than any key that already exists in the table.
>> taken from tom lane.
--
Thanks,
Sumeet.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULL becomes default
@ 2006-08-19 07:51 Markus Schaber <schabi@logix-tt.com>
parent: Julian Scarfe <julian@avbrief.com>
1 sibling, 1 reply; 5+ messages in thread
From: Markus Schaber @ 2006-08-19 07:51 UTC (permalink / raw)
To: Julian Scarfe <julian@avbrief.com>; +Cc: pgsql-sql
Hi, Julian,
Julian Scarfe wrote:
> A surrogate key has been introduced on a table with modifiers:
>
> Column | Type | Modifiers
> -------------+-----------------------------+------------------------------------------------------------
>
> new_id | integer | not null default
> nextval(('some_id_seq'::text)::regclass)
>
> However, some legacy software inserts into the table by first getting
> column names and then composing an INSERT for all the columns, filling
> in any undefined values with NULLs.
>
> Thus when it attempts to insert a row I get a:
>
> ERROR: null value in column "new_id" violates not-null constraint
>
> Thus I would like NULLs in such INSERTs to be treated as DEFAULT rather
> than violating the constraint. Is there an easy way to do that at the
> database level?
Did you try a "before insert" trigger that checks new_id for null
values, and replaces it with nextval()?
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULL becomes default
@ 2006-08-20 10:34 Julian Scarfe <julian@avbrief.com>
parent: Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 5+ messages in thread
From: Julian Scarfe @ 2006-08-20 10:34 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-sql
>> Thus I would like NULLs in such INSERTs to be treated as DEFAULT rather
>> than violating the constraint. Is there an easy way to do that at the
>> database level?
From: "Markus Schaber" <schabi@logix-tt.com>
> Did you try a "before insert" trigger that checks new_id for null
> values, and replaces it with nextval()?
So thinking about this a little more, it requires me to be able to change a
column value to DEFAULT (unless I implement the default from scratch in the
trigger, but that feels messy). I don't have much experience of triggers:
can I do that?
Thanks
Julian
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NULL becomes default
@ 2006-08-21 10:27 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Julian Scarfe <julian@avbrief.com>
0 siblings, 0 replies; 5+ messages in thread
From: Andrew Sullivan @ 2006-08-21 10:27 UTC (permalink / raw)
To: pgsql-sql
On Sun, Aug 20, 2006 at 11:34:48AM +0100, Julian Scarfe wrote:
> So thinking about this a little more, it requires me to be able to change a
> column value to DEFAULT (unless I implement the default from scratch in the
> trigger, but that feels messy). I don't have much experience of triggers:
> can I do that?
DEFAULT won't work for this case, I don't think; you want to set it
to the nextval() of the sequence or whatever it was you were trying
to do. But it's not messy: this is precisely the sort of thing
BEFORE triggers are good at.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-08-21 10:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-17 18:11 NULL becomes default Julian Scarfe <julian@avbrief.com>
2006-08-17 18:22 ` Sumeet <asumeet@gmail.com>
2006-08-19 07:51 ` Markus Schaber <schabi@logix-tt.com>
2006-08-20 10:34 ` Julian Scarfe <julian@avbrief.com>
2006-08-21 10:27 ` Andrew Sullivan <ajs@crankycanuck.ca>
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