newRecord and default values

[Sander Plas]

My PostgreSQL table definition:
---------------------------------------------------------------------------------------------------------
Table "public.product_group"
Column | Type |
Modifiers
--------+--------------------
+------------------------------------------------------------
id | integer | not null default
nextval('seq_product_group_id'::regclass)
name | character varying | not null
ord | integer |
type | product_group_type |
Indexes:
"product_group_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "product" CONSTRAINT "product_product_group_id_fkey" FOREIGN
KEY (product_group_id) REFERENCES product_group(id)
---------------------------------------------------------------------------------------------------------

java/jOOQ code:
---------------------------------------------------------------------------------------------------------
ProductGroupRecord pgr =
query().newRecord(ProductGroup.PRODUCT_GROUP);
pgr.setName("...vul in...");
pgr.store();
---------------------------------------------------------------------------------------------------------

PostgreSQL log:
---------------------------------------------------------------------------------------------------------
2011-05-03 15:27:15 CEST LOG: execute <unnamed>: insert into
public.product_group (id, name, ord, type) values (null, $1, null,
null)
2011-05-03 15:27:15 CEST DETAIL: parameters: $1 = '...vul in...'
2011-05-03 15:27:15 CEST ERROR: null value in column "id" violates
not-null constraint
2011-05-03 15:27:15 CEST STATEMENT: insert into public.product_group
(id, name, ord, type) values (null, $1, null, null)
---------------------------------------------------------------------------------------------------------

As you can see, i don't explicitly call pgr.setId(null) in my code,
but jOOQ does explicitly set id to null in the insert query. This
means that PostgreSQL won't use the 'default' value (sequence) that is
set on the 'id' column.

Is this intended behaviour?

Would it be possible (and a good idea?) to leave out columns that
haven't been defined explicitly from the generated SQL to allow the
database to fill in default values?

[Lukas Eder]

Hi Sander,

The reason why I designed it like this is out of mere habit, there is
no real need for setting all fields in the INSERT statement. I am
working mostly with Oracle and in our application we usually manually
set the ID that we previously fetched from a sequence, or we set it in
a trigger, such that nulls are not possible, even if set explicitly...

Your use-case with the "default" value is very common, though and
should be considered. I think I can fix this by 1.5.9

I have created a defect in Trac
https://sourceforge.net/apps/trac/jooq/ticket/479

I'll double check if any of the integration tests (in any RDBMS) would
fail, if not all fields were set explicitly. But I think your solution
is better.

Cheers
Lukas

[Sander Plas]

Great, thanks a lot!

[Lukas Eder]

Done on trunk. The next SNAPSHOT will include this fix

2011/5/3 Sander Plas <sande...@gmail.com>:
> Great, thanks a lot!