Обсуждение: rename a table
Hi all, I want to rename a table from DATA to 'unique' every month and then recreate table DATA. (a number of applications works with table DATA) DATA contains among the others a SERIAL field. create table DATA (a int, b serial,...); I tried ALTER TABLE DATA RENAME TO XXXX but when I recreate table DATA I get this error: relation 'data_b_seq' already exists. Is there any way to rename sequences? Is it better to create the new table, copy 1.000.000 to the new table and then delete them from DATA and then VACCUM the database? kostis. --------------------------------------------------------------- Why do I need to do such a thing? I think that I need to do this because I have 1.000.000 tuples/month and I don't want to work into a very big table. So 'I said' I have to find a way to move table DATA to something else and then recreate table DATA. This way I am going to work into a table of maximum 1.000.000 tuples at the end of the month.
I have just realized that I don't need the sequence any more, because when I rename the table I will never insert a raw in it. Thanks. Dennis wrote: > > // first simply > drop sequence data_b_seq; > > // then create the table as you would. before. > > -Dennis > > Kostis Mentzelos wrote: > > > Hi all, > > > > I want to rename a table from DATA to 'unique' every month > > and then recreate table DATA. (a number of applications works > > with table DATA) > > > > DATA contains among the others a SERIAL field. > > > > create table DATA (a int, b serial,...); > > > > I tried ALTER TABLE DATA RENAME TO XXXX but > > when I recreate table DATA I get this error: > > relation 'data_b_seq' already exists. > > > > Is there any way to rename sequences? > > Is it better to create the new table, copy 1.000.000 to the new > > table and then delete them from DATA and then VACCUM the > > database? > > > > kostis. > > > > --------------------------------------------------------------- > > Why do I need to do such a thing? > > I think that I need to do this because I have 1.000.000 > > tuples/month and I don't want to work into a very big table. > > So 'I said' I have to find a way to move table DATA to something > > else and then recreate table DATA. This way I am going to > > work into a table of maximum 1.000.000 tuples at the end of > > the month. > >
// first simply drop sequence data_b_seq; // then create the table as you would. before. -Dennis Kostis Mentzelos wrote: > Hi all, > > I want to rename a table from DATA to 'unique' every month > and then recreate table DATA. (a number of applications works > with table DATA) > > DATA contains among the others a SERIAL field. > > create table DATA (a int, b serial,...); > > I tried ALTER TABLE DATA RENAME TO XXXX but > when I recreate table DATA I get this error: > relation 'data_b_seq' already exists. > > Is there any way to rename sequences? > Is it better to create the new table, copy 1.000.000 to the new > table and then delete them from DATA and then VACCUM the > database? > > kostis. > > --------------------------------------------------------------- > Why do I need to do such a thing? > I think that I need to do this because I have 1.000.000 > tuples/month and I don't want to work into a very big table. > So 'I said' I have to find a way to move table DATA to something > else and then recreate table DATA. This way I am going to > work into a table of maximum 1.000.000 tuples at the end of > the month. >
Only declare the attribute 'b' as serial the first time. From then on, whenever you recreate table DATA just declare: b
intdefault nextval('data_b_seq'::text)
cheers,
thalis
On Thu, 21 Jun 2001, Kostis Mentzelos wrote:
> Hi all,
>
> I want to rename a table from DATA to 'unique' every month
> and then recreate table DATA. (a number of applications works
> with table DATA)
>
> DATA contains among the others a SERIAL field.
>
> create table DATA (a int, b serial,...);
>
> I tried ALTER TABLE DATA RENAME TO XXXX but
> when I recreate table DATA I get this error:
> relation 'data_b_seq' already exists.
>
> Is there any way to rename sequences?
> Is it better to create the new table, copy 1.000.000 to the new
> table and then delete them from DATA and then VACCUM the
> database?
>
> kostis.
In order to rename the table safely, I want to REVOKE INSERT on that table. But there is a problem because if there are users connected to database then REVOKE command wait until they disconnect. How can I found out who are connected to database? Can I kill them? -- I found out that SERIAL type creating a unique index. (RTFM) I also found out that using ALTER TABLE x RENAME TO y I can rename primary keys and indexes.
On Mon, Jun 25, 2001 at 12:21:14AM +0300, Kostis Mentzelos wrote: > In order to rename the table safely, I want to REVOKE INSERT on that > table. > > But there is a problem because if there are users connected to > database then REVOKE command wait until they disconnect. > How can I found out who are connected to database? Can I kill them? > > -- > > I found out that SERIAL type creating a unique index. (RTFM) > I also found out that using ALTER TABLE x RENAME TO y I can rename > primary > keys and indexes. Be careful combining these two: the serial type creates a sequence as well as a unique index, the name of which is hardcoded into the DEFAULT clause for the table. Rename the table, and you'll need to leave the sequence alone, since the DEFAULT clause can't be dynamically changed (yet). Ross