How unique PK value in Postgres?

[Doug Easterbrook]

oh, and I forgot.



when you insert a row in the table either
1) leave out the primary key -or-
2) use default as its value.

eg

this will get you a record inserted that gives you back the primary key without specifiying it in the insert statement

insert into F_DEFAULT (D_FIELD1,D_FIELD2) values (1,2) returning D_KEY


or same thing with specifying D_KEY in the insert statement… to get the default value (the nect key), you put DEFAULT as the value to use for the field.


insert into F_DEFAULT (D_KEY,D_FIELD1,D_FIELD2) values (DEFAULT, 1,2) returning D_KEY




extend the defaults for things like date fields.  imagine D_DATE_ENTERED

ALTER TABLE IF EXISTS public.f_default
    ADD COLUMN d_date_entered timestamp with time zone DEFAULT now();


so if the default is                  DEFAULT now()
then when the field is inserted into the database, it gets a timestamp of the current time.

a very convenient way to set values in the database so that it doesn’t matter where the record is entered, some applicable defaults are given to the data records, without specifying the field.


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

> On February 1, 2022, at 1:16 PM, Doug Easterbrook via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
> 
> you have to create a sequence key generator for each primay key you want.
> 
> 
> imagine a table called F_DEFAULT
> and a primary key of D_KEY
> 
> 
> 
> 
> you create a sequence generator.  our naming convention is table_key or.    ‘f_default_d_seq_key'
> 
> CREATE SEQUENCE IF NOT EXISTS f_default_d_seq_key
>    INCREMENT 1
>    START 1
>    MINVALUE 1
>    MAXVALUE 9223372036854775807
>    CACHE 1;
> 
> 
> 
> 
> then you give the database column a default of ’nextval’ that references the unique key generator.
> 
> 
> ALTER TABLE IF EXISTS f_default
>    ADD COLUMN d_seq integer NOT NULL DEFAULT nextval('f_default_d_seq_key'::regclass);
> 
> 
> 
> 
> 
> 
> 
> 
> Doug Easterbrook
> Arts Management Systems Ltd.
> mailto:doug at artsman.com
> http://www.artsman.com
> Phone (403) 650-1978
> 
>> On February 1, 2022, at 12:45 PM, Das Goravani <goravanis at gmail.com> wrote:
>> 
>> 
>> Hello,
>> 
>> When inserting, how do you get Postgres to assign a new, unique, value in the PK column?
>> 
>> In SQLite you just pass a #NULL in that column and SQLite assigns a value.
>> 
>> I was importing data into postgres very recently and no matter what I put in the PK column it would not accept it as cause for assigning a value. I tried 0, and NULL and of course a value. It used the values I passed.. they were from SQLite and they were in order, unique, so it worked out OK. 
>> 
>> But in general new work when inserting how do you get it to assign the value?
>> 
>> Do you use $excludefrominserts ?  That didn’t seem to work on a Smartlist with $doinserts(). I tried it, it came up with errors for the PK field anyway, even though I excluded it. 
>> 
>> ???
>> 
>> Thanks
>> 
>> Das Goravani
>> 
>> 
>> _____________________________________________________________
>> 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