Обсуждение: CREATE TABLE initial value for PRIMARY KEY

Hello Postgres community

Is there a formal way to set an initial value for a PRIMARY KEY
when CREATE TABLE ?  (This would be some large number,
typically.)

Or is it only possible to do this by first creating the table, and
then inserting a bogus record forcing the initial value by
specification (and then, presumably, deleting this bogus
record)  ?

Maurice Yarrow

Use a sequence and set the initial value of the sequence.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Maurice Yarrow
> Sent: Friday, October 27, 2006 11:51 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] CREATE TABLE initial value for PRIMARY KEY
>
> Hello Postgres community
>
> Is there a formal way to set an initial value for a PRIMARY KEY
> when CREATE TABLE ?  (This would be some large number,
> typically.)
>
> Or is it only possible to do this by first creating the table, and
> then inserting a bogus record forcing the initial value by
> specification (and then, presumably, deleting this bogus
> record)  ?
>
> Maurice Yarrow
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Maurice Yarrow <yarrow@best.com> writes:
> Is there a formal way to set an initial value for a PRIMARY KEY
> when CREATE TABLE ?

If it's a SERIAL column, you use setval() on the underlying sequence
before you start inserting data.

            regards, tom lane

> Is there a formal way to set an initial value for a PRIMARY KEY
> when CREATE TABLE ?  (This would be some large number,
> typically.)

I am not sure exact what you are looking for, but have you already looked at the default clause of
the create table statement?

http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html#AEN43517

Regards,

Richard Broersma Jr.

> I thought about  using a DEFAULT value, but I had presumed
> that this was only for repeated intializations.  So then is it the
> case that a
> CREATE TABLE mytable ( id INTEGER PRIMARY KEY DEFAULT 100000, ...
> only applies this default to the very first row of such a table, and then
> sensibly, increments from there ?
> (Guess I could easily try this out...)

Ah, I think I know what you are looking for. You want an auto-incrementing number. There are
special sudo-data-types called serial bigserial.  These are really auto-incrementing
integers/bigintegers. For more details on how to use this see:

http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL

Also, when relying, don't forget to reply also to the list that way everyone can participate.

Regards,

Richard Broersma Jr.

Hello Richard

Thanks for the tip.
So it turned out to be possible to do it like this:

CREATE SEQUENCE id_seq;
SELECT setval('id_seq',100111);
CREATE TABLE customer( id INTEGER DEFAULT nextval('id_seq'), name
VARCHAR(30) );

INSERT INTO customer (name) VALUES ('SomeName');
INSERT INTO customer (name) VALUES ('SomeOtherName');

Then
SELECT * FROM customer;
   id   |     name
--------+---------------
 100112 | SomeName
 100113 | SomeOtherName
(2 rows)

And it's that "setval" that is critical.

Note also that alternatively it can be done as follows:
CREATE TABLE customer ( id SERIAL, name VARCHAR(30) );
SELECT setval('customer_id_seq',100111);

INSERT INTO customer (name) VALUES ('SomeName');
INSERT INTO customer (name) VALUES ('SomeOtherName');

Then
SELECT * FROM customer;
   id   |     name
--------+---------------
 100112 | SomeName
 100113 | SomeOtherName
(2 rows)

Thanks again for the suggestion.  Ultimately, for the exact
syntaxes I went to Momjian's book:
(7.4  Creating Sequences,  7.5  Using Sequences to Number Rows)

Maurice Yarrow

Richard Broersma Jr wrote:

>>I thought about  using a DEFAULT value, but I had presumed
>>that this was only for repeated intializations.  So then is it the
>>case that a
>>CREATE TABLE mytable ( id INTEGER PRIMARY KEY DEFAULT 100000, ...
>>only applies this default to the very first row of such a table, and then
>>sensibly, increments from there ?
>>(Guess I could easily try this out...)
>>
>>
>
>Ah, I think I know what you are looking for. You want an auto-incrementing number. There are
>special sudo-data-types called serial bigserial.  These are really auto-incrementing
>integers/bigintegers. For more details on how to use this see:
>
>http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
>
>Also, when relying, don't forget to reply also to the list that way everyone can participate.
>
>Regards,
>
>Richard Broersma Jr.
>
>
>
>

Maurice Yarrow wrote:

> So it turned out to be possible to do it like this:
>
> CREATE SEQUENCE id_seq;
> SELECT setval('id_seq',100111);

FYI, you could have done this:

   CREATE SEQUENCE id_seq START 100111;

- John D. Burger
   MITRE