ansaurus

Question

Answer 1

A:

Not sure about postgres, but in sql server you can generate the create script and rename it to the new table you would like to make. Perhaps theres a similar way of doing things in postgres?

Ben Cull 2009-08-03 02:24:35

Answer 2

A:

This previous SO question may be of help:

http://stackoverflow.com/questions/198141/copy-a-table-including-indexes-in-postgres

Amber 2009-08-03 02:25:25

Answer 3

+1 A:

Take a look at pgAdmin - by far the easiest way to do what you want.
Right-click on table, Scripts - Create.

ChssPly76 2009-08-03 02:28:11

I only have access to phpPgAdmin; I don't own the server.

Shadow 2009-08-03 02:32:28

Fair enough. In phpPgAdmin: navigate to the table, click Export, select "Structure Only" and you have your script

ChssPly76 2009-08-03 02:35:34

I'm pretty sure their must be a bug in this install - it just shows a blank page in the right frame when I do that :/

Shadow 2009-08-03 02:47:30

Did you try both "show" or "download" options? If both don't work then yes, it might be a bug. If so, you'll need to do it via SQL, take a look at link in Dav's answer.

ChssPly76 2009-08-03 02:52:11

Download just gives me an empty file.

Shadow 2009-08-03 03:24:54

Most likely an installation error - perhaps phppgadmin is unable to access the pg_dump binary which it uses to get the structure.

Magnus Hagander 2009-08-03 08:55:29

@Magnus - good call. There was a recent change to the server which required me to switch to absolute paths in exec(). I'll notify the appropriate people.

Shadow 2009-08-21 02:57:14

Answer 4

+2 A:

Well, the closest you can get with SQL is:

create table new (
    like old
    including defaults
    including constraints
    including indexes
);

But it will not copy everything. The most important things that are missing are FOREIGN KEYs. Also - triggers are also not copied. Not sure about other things.

Another way is to dump the table structure, change it's name in dump, and load it again:

pg_dump -s -t old databases | sed 's/old/new/g' | psql

But beware, that such simplistic sed will also change old to new in other places (for example if you have in your table column named "is_scolded" it will become "is_scnewed").

The question really is rather: why do you need it - because for various purposes, I would use different techniques.

depesz 2009-08-03 06:51:26

ansaurus

tags:

views:

answers:

Copy table structure into new table

related questions