Problem Inserting Postgres

[Jim Pistrang]

Hi Doug,

If you are using the built-in $insert method, you can also use $excludefrominsert to leave the column out of the insert statement.

Jim

>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
>
>_____________________________________________________________
>Manage your list subscriptions at https://lists.omnis-dev.com
>Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 

-- 
Jim Pistrang
JP Computer Resources
413-237-5710
<https://jpcr.com>