CREATE TABLE initial value for PRIMARY KEY

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)---------------------------

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:

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