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

Re: altering a starting value of "serial" macro

From: Prabu Subroto <prabu_subroto(at)yahoo(dot)com>
To: Postgres General Milis <pgsql-general(at)postgresql(dot)org>
Subject: Re: altering a starting value of "serial" macro
Date: 2004-07-28 13:17:54
Message-ID: 20040728131754.92129.qmail@web41807.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
OK I did it :
create sequence sales_salesid_seq;
alter table sales alter column salesid set default
nextval('sales_salesid_seq');

but a new problem comes, because the table "sales" is
not empty. if the sequence counter reach a value that
already exists in the table "sales" than of course
comes this error message :
"
kv=# insert into sales (firstname) values ('baru5');
ERROR:  duplicate key violates unique constraint
"sales_pkey"
"

so now I think the only one solution is to set the
starting counter for the "serial" macro, for instance
to : "501" (the maximum current values of column
salesid is 500).

Anybody has a solution?

Thank you very much in advance.
--- Prabu Subroto <prabu_subroto(at)yahoo(dot)com> wrote:
> Dear Scott...
> 
> My God.... so I can not use "alter table" to define
> a
> column with int data type?
> 
> Here is the detail condition:
> I have created a table "sales". And I forgot to
> define
> auto_increment for primary key "salesid" (int4). the
> table has already contented the data.
> 
> I built an application with Qt. I thougt that I can
> define a column with auto_increment function
> afterall.
> 
> I want my application program only has to insert
> "firstname", "lastname" etc. And the database server
> (postgres) will put the increment value into the
> salesid automatically.
> 
> If I read your suggestion, that means...I have drop
> the column "salesid" and re-create the column
> "salesid". and it means, I will the data in the
> current "salesid" column.
> 
> Do you have further suggestion?
> 
> Thank you very much in advance.
> --- Scott Marlowe <smarlowe(at)qwest(dot)net> wrote:
> > On Tue, 2004-07-27 at 11:16, Prabu Subroto wrote:
> > > Dear my friends...
> > > 
> > > I am using postgres 7.4 and SuSE 9.1.
> > > 
> > > I want to use auto_increment as on MySQL. I look
> > up
> > > the documentation on www.postgres.com and I
> found
> > > "serial" .
> > > 
> > > But I don't know how to create auto_increment.
> > > here is my try:
> > > "
> > > kv=# alter table sales alter column salesid int4
> > > serial;
> > > ERROR:  syntax error at or near "int4" at
> > character 40
> > > "
> > 
> > Serial is a "macro" that makes postgresql do a
> > couple of things all at
> > once.  Let's take a look at the important parts of
> > that by running a
> > create table with a serial keyword, and then
> > examining the table, shall
> > we?
> > 
> > est=> create table test (id serial primary key,
> info
> > text);
> > NOTICE:  CREATE TABLE will create implicit
> sequence
> > "test_id_seq" for
> > "serial" column "test.id"
> > NOTICE:  CREATE TABLE / PRIMARY KEY will create
> > implicit index
> > "test_pkey" for table "test"
> > CREATE TABLE
> > test=> \d test
> >                            Table "public.test"
> >  Column |  Type   |                      Modifiers
> >
>
--------+---------+------------------------------------------------------
> >  id     | integer | not null default
> > nextval('public.test_id_seq'::text)
> >  info   | text    |
> > Indexes:
> >     "test_pkey" primary key, btree (id)
> > 
> > test=> \ds
> >              List of relations
> >  Schema |    Name     |   Type   |  Owner
> > --------+-------------+----------+----------
> >  public | test_id_seq | sequence | smarlowe
> > (1 row)
> > 
> > Now, as well as creating the table and sequence,
> > postgresql has, in the
> > background, created a dependency for the sequence
> on
> > the table.  This
> > means that if we drop the table, the sequence
> > created by the create
> > table statement will disappear as well.
> > 
> > Now, you were close, first you need to add a
> column
> > of the proper type,
> > create a sequence and tell the table to use that
> > sequence as the
> > default.  Let's assume I'd made the table test
> like
> > this:
> > 
> > test=> create table test (info text);
> > CREATE TABLE
> > test=>
> > 
> > And now I want to add an auto incrementing column.
> 
> > We can't just add a
> > serial because postgresql doesn't support setting
> > defaults in an alter
> > table, so we just add an int4, make a sequence,
> and
> > assign the default:
> > 
> > test=> alter table test add id int4 unique;
> > NOTICE:  ALTER TABLE / ADD UNIQUE will create
> > implicit index
> > "test_id_key" for table "test"
> > ALTER TABLE
> > test=> create sequence test_id_seq;
> > CREATE SEQUENCE
> > test=> alter table test alter column id set
> default
> > nextval('test_id_seq'::text);
> > ALTER TABLE
> > 
> > 
> > Now, if you have a bunch of already existing rows,
> > like this:
> > 
> > test=> select * from test;
> >  info | id
> > ------+----
> >  abc  |
> >  def  |
> > (2 rows)
> > 
> > then you need to populate those rows id field to
> put
> > in a sequence, and
> > that's pretty easy, actually:
> > 
> > est=> update test set id=DEFAULT;
> > UPDATE 2
> > test=> select * from test;
> >  info | id
> > ------+----
> >  abc  |  1
> >  def  |  2
> > (2 rows)
> > 
> > test=>
> > 
> > And there you go!
> > 
> > 
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> > 
> >               
> > http://www.postgresql.org/docs/faqs/FAQ.html
> > 
> 
> 
> 
> 		
> __________________________________
> Do you Yahoo!?
> Y! Messenger - Communicate in real time. Download
> now. 
> http://messenger.yahoo.com
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 



		
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

In response to

Responses

pgsql-general by date

Next:From: Prabu SubrotoDate: 2004-07-28 13:20:43
Subject: Re: altering a table to set serial function
Previous:From: Doug McNaughtDate: 2004-07-28 13:15:21
Subject: Re: altering a table to set serial function

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