Skip site navigation (1) Skip section navigation (2)

Re: CREATE TABLE initial value for PRIMARY KEY

From: Maurice Yarrow <yarrow(at)best(dot)com>
To: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
Cc: General PostgreSQL List <pgsql-general(at)postgresql(dot)org>
Subject: Re: CREATE TABLE initial value for PRIMARY KEY
Date: 2006-10-27 23:03:25
Message-ID: 4542903D.5080207@best.com (view raw or flat)
Thread:
Lists: pgsql-general
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.
>
>
>  
>



In response to

Responses

pgsql-general by date

Next:From: Sandro DentellaDate: 2006-10-27 23:14:34
Subject: Re: Simple OUTER JOIN doubt
Previous:From: Stephan SzaboDate: 2006-10-27 22:35:54
Subject: Re: Wrong record type - caused by SELECT order ???

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group