Re: CREATE TABLE initial value for PRIMARY KEY

> 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