Postgres Pro
Downloads
Home   >   mailing lists

Thread: rename a table

rename a table

From
Kostis Mentzelos
Date:
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.

Re: rename a table

From
Kostis Mentzelos
Date:
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.
> >

Re: rename a table

From
Dennis
Date:
// 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.
>

Re: rename a table

From
"Thalis A. Kalfigopoulos"
Date:
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.

Re: rename a table

From
Kostis Mentzelos
Date:
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.

Re: Re: rename a table

From
"Ross J. Reedstrom"
Date:
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