How unique PK value in Postgres?

Alan Davey david.a.davey at gmail.com
Tue Feb 1 21:47:19 UTC 2022


You can declare the column data type as "serial" and avoid having to create
the generator.  You should still add the PK constraint syntax:

CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL
);


On Tue, Feb 1, 2022 at 4:35 PM Das Goravani <goravanis at gmail.com> wrote:

>
> Man that’s complicated, and you have to do it for every table too.. wow..
> I wish it would default to this behavior.
>
> I kind of can’t believe it..
>
> Oh well I guess if you want power you then have to deal with it.
>
> Kind of makes you "have to have" an Omnis window where you have table
> creation set up.. a window where you enter the name of your table and your
> columns, and then you have code which creates the table, creates the
> columns, and then does this procedure to add the PK behavior…
>
> I’m used to dragging schemas onto "Tables" in the SQL Browser to create
> tables.. and I’m used to PK operating automatically.
>
> SQLIte spoils you in this way.
>
> What about indexes? Are they created in the same way as SQLite? You create
> them with a single line of SQL.
>
> Just "wow".
>
>
>
> > On Feb 1, 2022, at 4: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
>
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>


More information about the omnisdev-en mailing list