Dear Sirs,

We are working on Postgresql for one of our on going project.  We have come across a situation as mentioned below.

1. We have to create a table with sequence dynamically from the front end ( through a JSP code)
2. An option for the user is given to change the name of the table in the front end.  During this process, we change the name of the table and its relative sequence too.  But the table which was first created with a sequence has its property written in its property field.  Now if we change that property in that field, will the sequence gets disturbed??

For say, I create a table 'addrtech'  which has two fields, empid and empname
2. The emp id has a sequence created dynamically. so a sequence name   addrtech_empid_seq  is created.
3. The field empid  in addrtech has in its DEFAULT column as " nextval('"addrtech_empid_seq"'::text) "
4. Now I rename addrtech to addrtechnology
5.  So the table is altered
6. The sequence is also altered as addrtechnology_empid_seq and the new table is addrtechnology 

But the doubt now is the field which has the sequence i.e the empid which has nextval('"addrtech_empid_seq"'::text) .  The property if changed to  nextval('"addrtechnology_empid_seq"'::text) will the seqeunce remain the same.  Meaning if it had some few million records in the orginial table addrtech and if additional data is entere to addrtechnology will the sequence continue.

Please clarify this query of mine at the earliest so that we can incorporate the same in our codes


Your early reply is solicitated

regards
Andy




--------------------------------------------------------------------------------





Dear Sirs,
 
We are working on Postgresql for one of our on going project.  We have 
come across a situation as mentioned below.
 
1. We have to create a table with sequence dynamically from the front end ( 
through a JSP code)
2. An option for the user is given to change the name of the table in the 
front end.  During this process, we change the name of the table and its 
relative sequence too.  But the table which was first created with a 
sequence has its property written in its property field.  Now if we change 
that property in that field, will the sequence gets disturbed??
 
For say, I create a table 'addrtech'  which has two fields, empid and 
empname
2. The emp id has a sequence created dynamically. so a sequence 
name   addrtech_empid_seq  is 
created.
3. The field empid  in addrtech has in its DEFAULT 
column as 
" nextval('"addrtech_empid_seq"'::text) "
4. Now I rename addrtech to addrtechnology
5.  So the table is altered
6. The sequence is also altered as addrtechnology_empid_seq 
and the new table is addrtechnology 
 
But the doubt now is the field which has the sequence i.e the empid which 
has nextval('"addrtech_empid_seq"'::text) .  The 
property if changed 
to  nextval('"addrtechnology_empid_seq"'::text) will 
the seqeunce remain the same.  Meaning if it had some few million records 
in the orginial table addrtech and if additional data is entere to 
addrtechnology will the sequence continue.
 
Please clarify this query of mine at the earliest so that we can 
incorporate the same in our codes
 
 
Your early reply is solicitated
 
regards
Andy
Fri, 21 Mar 2003 20:08:59 +0530
"Anand B Kumar" <akumar@addr.com>
Please clarify with regard to Renaming a Sequence
On Fri, Mar 21, 2003 at 08:08:59PM +0530, Anand B Kumar wrote:
> Dear Sirs,
> 
> We are working on Postgresql for one of our on going project.  We have come across a situation as mentioned below.
> 
> 1. We have to create a table with sequence dynamically from the front end ( through a JSP code)
> 2. An option for the user is given to change the name of the table in the front end.  During this process, we change the name of the table and its relative sequence too.  But the table which was first created with a sequence has its property written in its property field.  Now if we change that property in that field, will the sequence gets disturbed??
> 
> For say, I create a table 'addrtech'  which has two fields, empid and empname
> 2. The emp id has a sequence created dynamically. so a sequence name   addrtech_empid_seq  is created.
> 3. The field empid  in addrtech has in its DEFAULT column as " nextval('"addrtech_empid_seq"'::text) "
> 4. Now I rename addrtech to addrtechnology
> 5.  So the table is altered
> 6. The sequence is also altered as addrtechnology_empid_seq and the new table is addrtechnology 
> 
> But the doubt now is the field which has the sequence i.e the empid which has nextval('"addrtech_empid_seq"'::text) .  The property if changed to  nextval('"addrtechnology_empid_seq"'::text) will the seqeunce remain the same.  Meaning if it had some few million records in the orginial table addrtech and if additional data is entere to addrtechnology will the sequence continue.
> 
> Please clarify this query of mine at the earliest so that we can incorporate the same in our codes

The database won't update the default for you, you'll have to do that
yourself with ALTER TABLE SET DEFAULT.

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> IT is not something like pizza that you order in at one o'clock in 
> the morning. - John Loebenstein, St George CIO
Sat, 22 Mar 2003 17:51:48 +1100
Martijn van Oosterhout <kleptog@svana.org>
Re: Please clarify with regard to Renaming a Sequence
Please reply to the list, not just me. On the list many people can answer
your question.

If you rename a sequence it doesn't affect any other property (including
where it is upto). Surely you could have tried this out and seen how it
works?

On Sat, Mar 22, 2003 at 12:33:59PM +0530, Anand B Kumar wrote:
> Martijn
> 
> Thanx for your reply.  But my question was to know, whether the sequence
> would be maintained if we rename the sequence to a new name!!  We are
> equally aware of the alter command, but let us know about the continuity of
> the sequence
> 
> ----- Original Message -----
> From: "Martijn van Oosterhout" <kleptog@svana.org>
> To: "Anand B Kumar" <akumar@addr.com>
> Cc: <pgsql-general@postgresql.org>; <pgsql-hackers@postgresql.org>
> Sent: Saturday, March 22, 2003 12:21 PM
> Subject: Re: [GENERAL] Please clarify with regard to Renaming a Sequence
> 
> 

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> IT is not something like pizza that you order in at one o'clock in 
> the morning. - John Loebenstein, St George CIO
Sat, 22 Mar 2003 19:22:56 +1100
Martijn van Oosterhout <kleptog@svana.org>
Re: Please clarify with regard to Renaming a Sequence
>From what I can see the sequence name is not changed when you rename a
table

test=# alter table fooseq rename to fooseq1;
ALTER TABLE
test=# \d fooseq1
                          Table "public.fooseq1"
 Column |  Type   |                       Modifiers
--------+---------+--------------------------------------------------------
 id     | integer | not null default
nextval('public.fooseq_id_seq'::text)

so everything should work as expected.

Dave

On Fri, 2003-03-21 at 09:38, Anand B Kumar wrote:
> Dear Sirs,
>  
> We are working on Postgresql for one of our on going project.  We have
> come across a situation as mentioned below.
>  
> 1. We have to create a table with sequence dynamically from the front
> end ( through a JSP code)
> 2. An option for the user is given to change the name of the table in
> the front end.  During this process, we change the name of the table
> and its relative sequence too.  But the table which was first created
> with a sequence has its property written in its property field.  Now
> if we change that property in that field, will the sequence gets
> disturbed??
>  
> For say, I create a table 'addrtech'  which has two fields, empid and
> empname
> 2. The emp id has a sequence created dynamically. so a sequence
> name   addrtech_empid_seq  is created.
> 3. The field empid  in addrtech has in its DEFAULT column as
> " nextval('"addrtech_empid_seq"'::text) "
> 4. Now I rename addrtech to addrtechnology
> 5.  So the table is altered
> 6. The sequence is also altered as addrtechnology_empid_seq and the
> new table is addrtechnology 
>  
> But the doubt now is the field which has the sequence i.e the empid
> which has nextval('"addrtech_empid_seq"'::text) .  The property if
> changed to  nextval('"addrtechnology_empid_seq"'::text) will the
> seqeunce remain the same.  Meaning if it had some few million records
> in the orginial table addrtech and if additional data is entere to
> addrtechnology will the sequence continue.
>  
> Please clarify this query of mine at the earliest so that we can
> incorporate the same in our codes
>  
>  
> Your early reply is solicitated
>  
> regards
> Andy
>  
>  
> 
> ______________________________________________________________________
> Upgrade Your Email - Click here!
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting


22 Mar 2003 10:12:08 -0500
Dave Cramer <dave@fastcrypt.com>
Re: [HACKERS] Please clarify with regard to Renaming a Sequence
Dear Dave,

Thanx a lot for your help.  But still, the doubt lies, whether we can change
the property of a table which has bulk recods on it!!!  Does pgsql support
that??  Even though u alter the name of the sequence, we need to change the
sequence name ( meaning the property in the default field) in the altered
table also, which should now point to the new altered sequence.

Please advice
thanx
Andy


----- Original Message -----
From: "Dave Cramer" <dave@fastcrypt.com>
To: "Anand B Kumar" <akumar@addr.com>
Cc: <pgsql-general@postgresql.org>; <pgsql-hackers@postgresql.org>
Sent: Saturday, March 22, 2003 8:42 PM
Subject: Re: [HACKERS] Please clarify with regard to Renaming a Sequence


> >From what I can see the sequence name is not changed when you rename a
> table
>
> test=# alter table fooseq rename to fooseq1;
> ALTER TABLE
> test=# \d fooseq1
>                           Table "public.fooseq1"
>  Column |  Type   |                       Modifiers
> --------+---------+-------------------------------------------------------
-
>  id     | integer | not null default
> nextval('public.fooseq_id_seq'::text)
>
> so everything should work as expected.
>
> Dave
>
> On Fri, 2003-03-21 at 09:38, Anand B Kumar wrote:
> > Dear Sirs,
> >
> > We are working on Postgresql for one of our on going project.  We have
> > come across a situation as mentioned below.
> >
> > 1. We have to create a table with sequence dynamically from the front
> > end ( through a JSP code)
> > 2. An option for the user is given to change the name of the table in
> > the front end.  During this process, we change the name of the table
> > and its relative sequence too.  But the table which was first created
> > with a sequence has its property written in its property field.  Now
> > if we change that property in that field, will the sequence gets
> > disturbed??
> >
> > For say, I create a table 'addrtech'  which has two fields, empid and
> > empname
> > 2. The emp id has a sequence created dynamically. so a sequence
> > name   addrtech_empid_seq  is created.
> > 3. The field empid  in addrtech has in its DEFAULT column as
> > " nextval('"addrtech_empid_seq"'::text) "
> > 4. Now I rename addrtech to addrtechnology
> > 5.  So the table is altered
> > 6. The sequence is also altered as addrtechnology_empid_seq and the
> > new table is addrtechnology
> >
> > But the doubt now is the field which has the sequence i.e the empid
> > which has nextval('"addrtech_empid_seq"'::text) .  The property if
> > changed to  nextval('"addrtechnology_empid_seq"'::text) will the
> > seqeunce remain the same.  Meaning if it had some few million records
> > in the orginial table addrtech and if additional data is entere to
> > addrtechnology will the sequence continue.
> >
> > Please clarify this query of mine at the earliest so that we can
> > incorporate the same in our codes
> >
> >
> > Your early reply is solicitated
> >
> > regards
> > Andy
> >
> >
> >
> > ______________________________________________________________________
> > Upgrade Your Email - Click here!
> --
> Dave Cramer <dave@fastcrypt.com>
> Cramer Consulting
>
>


Mon, 24 Mar 2003 09:50:44 +0530
"Anand B Kumar" <akumar@addr.com>
Re: [HACKERS] Please clarify with regard to Renaming a Sequence
Postgres manages sequences using DEFAULT, which means that if you insert
a row with the sequence column set to DEFAULT, or omit the row in the
insert, then the default value will be inserted. This is usually
nextval('sequence_name'). There is no underlying magic other than this.
So, as long as you ensure that the new sequence you create has the
correct start value, and you change the default constraint on the column
you are free to do as you wish with the sequences.

Have a look at what pg_dump does with sequences and tables.

Dave

On Sun, 2003-03-23 at 23:20, Anand B Kumar wrote:
> Dear Dave,
> 
> Thanx a lot for your help.  But still, the doubt lies, whether we can change
> the property of a table which has bulk recods on it!!!  Does pgsql support
> that??  Even though u alter the name of the sequence, we need to change the
> sequence name ( meaning the property in the default field) in the altered
> table also, which should now point to the new altered sequence.
> 
> Please advice
> thanx
> Andy
> 
> 
> ----- Original Message -----
> From: "Dave Cramer" <dave@fastcrypt.com>
> To: "Anand B Kumar" <akumar@addr.com>
> Cc: <pgsql-general@postgresql.org>; <pgsql-hackers@postgresql.org>
> Sent: Saturday, March 22, 2003 8:42 PM
> Subject: Re: [HACKERS] Please clarify with regard to Renaming a Sequence
> 
> 
> > >From what I can see the sequence name is not changed when you rename a
> > table
> >
> > test=# alter table fooseq rename to fooseq1;
> > ALTER TABLE
> > test=# \d fooseq1
> >                           Table "public.fooseq1"
> >  Column |  Type   |                       Modifiers
> > --------+---------+-------------------------------------------------------
> -
> >  id     | integer | not null default
> > nextval('public.fooseq_id_seq'::text)
> >
> > so everything should work as expected.
> >
> > Dave
> >
> > On Fri, 2003-03-21 at 09:38, Anand B Kumar wrote:
> > > Dear Sirs,
> > >
> > > We are working on Postgresql for one of our on going project.  We have
> > > come across a situation as mentioned below.
> > >
> > > 1. We have to create a table with sequence dynamically from the front
> > > end ( through a JSP code)
> > > 2. An option for the user is given to change the name of the table in
> > > the front end.  During this process, we change the name of the table
> > > and its relative sequence too.  But the table which was first created
> > > with a sequence has its property written in its property field.  Now
> > > if we change that property in that field, will the sequence gets
> > > disturbed??
> > >
> > > For say, I create a table 'addrtech'  which has two fields, empid and
> > > empname
> > > 2. The emp id has a sequence created dynamically. so a sequence
> > > name   addrtech_empid_seq  is created.
> > > 3. The field empid  in addrtech has in its DEFAULT column as
> > > " nextval('"addrtech_empid_seq"'::text) "
> > > 4. Now I rename addrtech to addrtechnology
> > > 5.  So the table is altered
> > > 6. The sequence is also altered as addrtechnology_empid_seq and the
> > > new table is addrtechnology
> > >
> > > But the doubt now is the field which has the sequence i.e the empid
> > > which has nextval('"addrtech_empid_seq"'::text) .  The property if
> > > changed to  nextval('"addrtechnology_empid_seq"'::text) will the
> > > seqeunce remain the same.  Meaning if it had some few million records
> > > in the orginial table addrtech and if additional data is entere to
> > > addrtechnology will the sequence continue.
> > >
> > > Please clarify this query of mine at the earliest so that we can
> > > incorporate the same in our codes
> > >
> > >
> > > Your early reply is solicitated
> > >
> > > regards
> > > Andy
> > >
> > >
> > >
> > > ______________________________________________________________________
> > > Upgrade Your Email - Click here!
> > --
> > Dave Cramer <dave@fastcrypt.com>
> > Cramer Consulting
> >
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-- 
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting


24 Mar 2003 13:21:48 -0500
Dave Cramer <dave@fastcrypt.com>
Re: Please clarify with regard to Renaming a Sequence