Postgres Pro
Downloads
Home   >   mailing lists

Thread: Replace Old Table with New

Replace Old Table with New

From
"David Lebling"
Date:
I have a set of tables that periodically need to be thrown away and
completely rebuilt with new data. I'm using JDBC to interact with
PostgreSQL.

Is there a straightforward and efficient way to do this?

I tried using the commit/rollback methods in JDBC but blew Java memory out
of the water (these are decent-sized but not enormous datasets: 60,000
records more or less).

I investigated using 'alter table ... rename to ...' but it didn't seem to
also rename the associated indexes, and before actually implementing code to
do it I want to ask if that would actually work. (If it did it would be the
best way to go, as I have plenty of disk space available for an extra temp
copy of the database).

The tables are very simple, with primary keys and in one case a supplemental
additional index.

Any advice would be appreciated.

    Dave Lebling
    Ucentric Systems, LLC

Re: Replace Old Table with New

From
Tom Lane
Date:
"David Lebling" <dlebling@ucentric.com> writes:
> I investigated using 'alter table ... rename to ...' but it didn't seem to
> also rename the associated indexes, and before actually implementing code to
> do it I want to ask if that would actually work.

ALTER RENAME just changes the name of the given table.  If you want to
rename indexes, use ALTER RENAME to rename them, too.

            regards, tom lane

Re: Replace Old Table with New

From
"David Lebling"
Date:
What command would achieve this? I'm using a somewhat dusty PostgreSql
(6.5.3).

>ALTER TABLE an-index RENAME TO an-index-new-name;
ERROR: TypeRename: type an-index not defined

and there is no ALTER INDEX.

    Dave Lebling

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
> ALTER RENAME just changes the name of the given table.  If you want to
> rename indexes, use ALTER RENAME to rename them, too.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: Replace Old Table with New

From
Tom Lane
Date:
"David Lebling" <dlebling@ucentric.com> writes:
> What command would achieve this? I'm using a somewhat dusty PostgreSql
> (6.5.3).

>> ALTER TABLE an-index RENAME TO an-index-new-name;
> ERROR: TypeRename: type an-index not defined

In less-dusty Postgreses, ALTER TABLE RENAME works on indexes.  I'd
recommend an update for many reasons besides this one ;-)

            regards, tom lane