Thread: How to handle a requirement for nextval
I am still struggling on a few issues, have got my first little database up and running, and am quite happy with PostGres, but I cannot figure out how to create a table which can have a field that autoincrements.. I am sure that somewhere in the oid terminology it is built in, but I want to have a counter on some table entries.. ie... CREATE TABLE clothing_type ( type_id INT NOT NULL, description TEXT, for_sex CHAR ); INSERT INTO clothing_type ( description, for_sex ) VALUES ( 'pants', 'm' ); What I want is that the Primary Key (Only Key) be type_id, and int, and the first item that I insert should have type_id as '1', next will be '2' etc.. I could have every insert into this table include a type_id, but that seems unessary. Can I have something like default='nextval' ?
I have got the same trouble. I found a first inellegant solution. Is to create a function which make an increment like that : create function last98_20_rubriques() returns int4 as 'Select max(ref98_20_rubriques) as next from DB98_20_rubriques;' language 'sql'; But I read yesterday that I have to use e SEQUENCE like : create table toto (id int4 PRIMARY KEY, test varchar); create sequence step1 increment 1 start 1000 minvalue 1000 maxvalue 999999; And after that I have normaly to do : insert into toto (id,test) values (step1.nextval,'sdf'); But It's don't work. If you can go further send me a mail (and sorry for my very bad english). JK The Web Administrator wrote: > I am still struggling on a few issues, have got my first little database > up and running, and am quite happy with PostGres, but I cannot figure > out how to create a table which can have a field that autoincrements.. > I am sure that somewhere in the oid terminology it is built in, but I > want to have a counter on some table entries.. ie... > CREATE TABLE clothing_type ( > type_id INT NOT NULL, > description TEXT, > for_sex CHAR > ); > INSERT INTO clothing_type ( > description, > for_sex > ) VALUES ( > 'pants', > 'm' > ); > > What I want is that the Primary Key (Only Key) be type_id, and int, and > the first item that I insert should have type_id as '1', next will be > '2' etc.. > I could have every insert into this table include a type_id, but that > seems unessary. > Can I have something like default='nextval' ?
At 23:33 +0300 on 18/5/98, The Web Administrator wrote: > What I want is that the Primary Key (Only Key) be type_id, and int, and > the first item that I insert should have type_id as '1', next will be > '2' etc.. > I could have every insert into this table include a type_id, but that > seems unessary. > Can I have something like default='nextval' ? This is so much a faq, that I went to look at the FAQ. In fact, there is a question there which is similar to this one, but I think its phrasing defeats newbies rather than helps them. I for one have never encountered fields of type SERIAL, and people might not think it's the same sort of question. The answer is even more of a problem, because the most commonly used method of doing this is summarised in "look at the create_sequence manual", whereas the two less recommended methods (using OIDs and using an auto-incrementing function) are discussed in detail. Bruce, don't you agree? Perhaps change the phrasing of the question to "How do I create an auto-incrementing field?" As for the answer itself, here it is: In order to create an auto-incrementing field - one which will automatically receive the value 1 for the first row inserted, 2 for the second, and so on - you have to define a sequence. For example: CREATE SEQUENCE emp_no; Then you define your table. Assuming you want an employee table in which the emp_id field is autoincrementing, here is what you write: CREATE TABLE emp ( emp_id int4 DEFAULT nextval( 'emp_no' ) NOT NULL -- Other fields here ); Following that, when you want to insert a row, insert values for all other fields except the emp_id field. It will insert its own value automatically. For more information, read the man page "create_sequence". Herouth
On Tue, 19 May 1998, Jerome Knobl wrote: > I have got the same trouble. I found a first inellegant solution. Is to > create a function which make an increment like that : > create function last98_20_rubriques() returns int4 > as 'Select max(ref98_20_rubriques) as next from DB98_20_rubriques;' > language 'sql'; > > But I read yesterday that I have to use e SEQUENCE like : > > create table toto (id int4 PRIMARY KEY, test varchar); > create sequence step1 increment 1 start 1000 minvalue 1000 maxvalue > 999999; > > And after that I have normaly to do : > insert into toto (id,test) values (step1.nextval,'sdf'); > But It's don't work. > If you can go further send me a mail (and sorry for my very bad english). insert into toto (id,test) values (nextval('step1'),'sdf'); Jose'
On Mon, 18 May 1998, The Web Administrator wrote: > I am still struggling on a few issues, have got my first little database > up and running, and am quite happy with PostGres, but I cannot figure > out how to create a table which can have a field that autoincrements.. > I am sure that somewhere in the oid terminology it is built in, but I > want to have a counter on some table entries.. ie... > CREATE TABLE clothing_type ( > type_id INT NOT NULL, > description TEXT, > for_sex CHAR > ); > INSERT INTO clothing_type ( > description, > for_sex > ) VALUES ( > 'pants', > 'm' > ); > > What I want is that the Primary Key (Only Key) be type_id, and int, and > the first item that I insert should have type_id as '1', next will be > '2' etc.. > I could have every insert into this table include a type_id, but that > seems unessary. > Can I have something like default='nextval' ? CREATE SEQUENCE serial START 101; CREATE TABLE distributors ( did DECIMAL(03) DEFAULT NEXTVAL('serial'), name VARCHAR(40) ); -- Use sequence in insert: -- INSERT INTO distributors VALUES (NEXTVAL('serial')); Jose'
Ok, first of all you have given me the EXACT answer I was looking for. But trust me, I poured over the docs, and there was no ref to a man page on 'create_sequence', otherwise I would have jumped all over it.. (I am using 6.2, that might be the problem :>) Yes, I saw the OID thing, but that didn't seem too standard SQL. The biggest problem I see in PostGres is the wide mixture of docs. Maybe all the examples and readme's should be put together in a more common package. Possibly a doc package? Herouth Maoz wrote: > At 23:33 +0300 on 18/5/98, The Web Administrator wrote: > > > What I want is that the Primary Key (Only Key) be type_id, and int, and > > the first item that I insert should have type_id as '1', next will be > > '2' etc.. > > I could have every insert into this table include a type_id, but that > > seems unessary. > > Can I have something like default='nextval' ? > > In order to create an auto-incrementing field - one which will > automatically receive the value 1 for the first row inserted, 2 for the > second, and so on - you have to define a sequence. For example: > > CREATE SEQUENCE emp_no; > > Then you define your table. Assuming you want an employee table in which > the emp_id field is autoincrementing, here is what you write: > > CREATE TABLE emp > ( > emp_id int4 > DEFAULT nextval( 'emp_no' ) > NOT NULL > -- Other fields here > ); > >
Update FAQ. See web site for new heading. > > At 23:33 +0300 on 18/5/98, The Web Administrator wrote: > > > > What I want is that the Primary Key (Only Key) be type_id, and int, and > > the first item that I insert should have type_id as '1', next will be > > '2' etc.. > > I could have every insert into this table include a type_id, but that > > seems unessary. > > Can I have something like default='nextval' ? > > This is so much a faq, that I went to look at the FAQ. In fact, there is a > question there which is similar to this one, but I think its phrasing > defeats newbies rather than helps them. I for one have never encountered > fields of type SERIAL, and people might not think it's the same sort of > question. The answer is even more of a problem, because the most commonly > used method of doing this is summarised in "look at the create_sequence > manual", whereas the two less recommended methods (using OIDs and using an > auto-incrementing function) are discussed in detail. > > Bruce, don't you agree? Perhaps change the phrasing of the question to "How > do I create an auto-incrementing field?" -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)