Re: ALTER TABLE ... REPLACE WITH

On 12/14/10 11:43 AM, Simon Riggs wrote:
> On Tue, 2010-12-14 at 11:34 -0800, Josh Berkus wrote:
>
>> In order for REPLACE WITH to be really useful, though, we need a
>> command cloning at table design with *all* constraints, FKs, keys, and
>> indexes. Currently, I still don't think we have that ... do we?
>
> Being able to vary the indexes when we REPLACE is a good feature.
>
> We only need to check that datatypes and constraints match.

No, you're missing my point ... currently we don't have a command which
says "make an identical clone of this table". CREATE TABLE AS allows us
to copy all of the data for the table, but not the full table design.
CREATE TABLE LIKE gives us most of the design (although it still won't
copy FKs) but won't copy the data.

However, for the usual do-si-do case, you need to populate the data
using a query and not clone all the data. What you'd really need is
something like:

CREATE TABLE new_table LIKE old_table ( INCLUDING ALL ) FROM SELECT ...

.. which would create the base tabledef, copy in the data from the
query, and then apply all the constraints, indexes, defaults, etc.

Without some means of doing a clone of the table in a single command,
you've eliminated half the scripting work, but not helped at all with
the other half.

Actually, you know what would be ideal?

REPLACE TABLE old_table WITH SELECT ...

Give it some thought ...

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com