Problem Inserting Postgres

[Doug Easterbrook]

based on the two e3mails (this and the nect one)

CUST_ID is part of ‘dtCustomers’ in the insert statement.

> Do tStatObjPGRef.$execdirect(con('INSERT INTO dtcustomers ',$ctask.tSessObjPGRef.$insertnames(iro),' RETURNING  cust_id')) Returns #F


and  it is telling you that cust_id is NULL. (I assume the first column)


> ERROR:  null value in column "cust_id" of relation "dtcustomers" violates not-null constraint
> DETAIL:  Failing row contains (null, null, null, null, null, null, null, null, null, null, null, D, null).




so, the deal is that you are trying to force insert a NULL into cust_id.  which is ok as far as omnis goes.      but as far as postgres goes, you have defined its as a serial.     being a ’serial’, postgres would have created a NOT NULL constraint on the column, since its not very useful to have null primary keys.

you can check it in pgadmin by looking at the column definition.   ours look like below (we don’t use serial as a type directly, but for historical reasons, we build our own as integers.     non the less, per an ealrier set of emails to the list on this topic, SERIAL type fields will build something like this

ALTER TABLE IF EXISTS public.f_acct_chart
    ADD COLUMN ca_seq integer NOT NULL DEFAULT nextval('f_acct_chart_ca_seq_key'::regclass);


NOTE … NOT NULL on the field.



so, how do you make postgres user the next serial number when inserting a record.

One of TWO WAYS

OPTION 1:  leave cust_id out of the sql statement

eg

INSERT INTO dtcustomers (cust_firstname,cust_lastname) VALUES (‘das’,’Goravani' ) RETURNING cust_id


OR

Option 2, use the DEFAULT keyword as the value of the field to tell postgres to use the default value for the field.

eg

INSERT INTO dtcustomers (cust_id,cust_firstname,cust_lastname) VALUES (DEFAULT, ‘das’,’Goravani' ) RETURNING cust_id



Any FIELD not EXPLICITLY SPECIFIED in the insert statement takes default values you have specified in the database.  Example, if you have a cust_date_entered and the  field default is NOW() in the database, then if you  do sometihng like


INSERT INTO dtcustomers (cust_firstname,cust_lastname) VALUES (‘das’,’Goravani' ) RETURNING cust_id,cust_date_entered

you’ll get cust_date_entered returned to you as the current server date .. i.e. now()


but, if you include it in the insert statement (and cust_id), it will set the date that you specifiy

INSERT INTO dtcustomers (cust_id,cust_firstname,cust_lastname,cust_date_entered)
VALUES (DEFAULT,‘das’,’Goravani’ , TIMESTAMP ‘2022-07-07 4:00:00') RETURNING cust_id,cust_date_entered


if you tell sql to use DEFAULT for the date, you’ll get back now

INSERT INTO dtcustomers (cust_id,cust_firstname,cust_lastname,cust_date_entered)
VALUES (DEFAULT,‘das’,’Goravani’ , DEFAULT) RETURNING cust_id,cust_date_entered




the important learning

1) leave out a field from the insert statement if you want the default from the database on insert
2) use the field if you want to set a specific value
3) use the field in the insert statement AND use DEFAULT for its value, if you want to have the database use the default.










Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Jul 9, 2022, at 10:07 AM, Das Goravani <goravanis at gmail.com> wrote:
> 
> 
> Dear List,
> 
> I am on my first postgres INSERT in a new app and new database table.
> I am using Postgres
> I created the table by drag drop schema onto session
> This is my INSERT line:
> 
> Do tStatObjPGRef.$execdirect(con('INSERT INTO dtcustomers ',$ctask.tSessObjPGRef.$insertnames(iro),' RETURNING  cust_id')) Returns #F
> 
> This is what it says in Native Error Text:
> 
> ERROR:  null value in column "cust_id" of relation "dtcustomers" violates not-null constraint
> DETAIL:  Failing row contains (null, null, null, null, null, null, null, null, null, null, null, D, null).
> 
> The problem with that error is that cust_id is the primary key and I have excluded it from insert just prior and I check SQLText and indeed it is NOT in the list of fields to INSERT nor in the values of course but I am RETURNING cust_id…
> 
> I am hoping Postgres will auto increment it.
> 
> When you drag drop schema onto session and it is Postgres, the PK column is automatically marked as a SERIAL column.
> 
> By excluding cust_id from the list of fields to insert, I thought it did the default action for the excluded column which is nextval correct?
> 
> So I thought I was doing all the right things.
> 
> This is my SQL and it appears correct to me but it doesn’t jive with the error being given above which seems to be saying I AM TRYING TO PASS the cust_id field in my SQL but I’m NOT
> 
> Any ideas?
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com