Thread: 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