Re: Unable to insert row into table with serialized primary key

On Thu, February 11, 2021 11:35, James B. Byrne via Aubit4gl-discuss wrote:
> in Exercise 9 of Informix by Example one is required to insert rows into a
> table (customers) having a serialized primary key:
>
> CREATE TABLE public.customer
> (
>     customer_num integer NOT NULL
>         DEFAULT nextval('customer_customer_num_seq'::regclass),
>     . . .
>     CONSTRAINT customer_pkey PRIMARY KEY (customer_num),
>     . . .
>
> The code provided in the exercise is:
> WHENEVER ERROR CONTINUE
>     INSERT INTO CUSTOMER
>       VALUES (
>         0,
>         gr_customer.fname,
>         gr_customer.lname,
>         gr_customer.company,
>         gr_customer.address1,
>         gr_customer.address2,
>         gr_customer.city,
>         gr_customer.state,
>         gr_customer.zipcode,
>         gr_customer.phone
>         )
>   WHENEVER ERROR STOP
>
>   IF (status < 0) THEN
>     ERROR status USING "-<<<<<<<<<<<", ": Unable to complete customer insert."
>
> This always returns "-239" (duplicate key violates unique constraint) from
> 'postgres8' once the first new record has been added.  That first row is
> assigned the value 0 indicating that the serialization is not being used.  The
> SQLCA array that is returned is empty.
>
> How does one insert rows into tables with serialized columns using Aubit4GL?
>
>

I am aware of this:

With Informix, you supply a zero as the SERIAL value, and the engine
replaces the zero with the next serial number. With PostgreSQL, you
don’t supply a value and the engine supplies the next serial number
as a default. If you supply a zero, it is accepted!

However, removing the value 0 from the element list does not work either.

-1: Unable to complete customer insert.

-- 
***          e-Mail is NOT a SECURE channel          ***
        Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrne                mailto:[email protected]
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3