Thread: CREATE TABLE initial value for PRIMARY KEY

CREATE TABLE initial value for PRIMARY KEY

From
Maurice Yarrow
Date:
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



Re: CREATE TABLE initial value for PRIMARY KEY

From
"Dann Corbit"
Date:
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

Re: CREATE TABLE initial value for PRIMARY KEY

From
Tom Lane
Date:
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

Re: CREATE TABLE initial value for PRIMARY KEY

From
Richard Broersma Jr
Date:
> 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.

Re: CREATE TABLE initial value for PRIMARY KEY

From
Richard Broersma Jr
Date:
> 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.



Re: CREATE TABLE initial value for PRIMARY KEY

From
Maurice Yarrow
Date:
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.
>
>
>
>



Re: CREATE TABLE initial value for PRIMARY KEY

From
"John D. Burger"
Date:
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