 | |
problem copying a table
comp.databases.postgresql comp.databases.postgresql
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
problem copying a table -
10-10-2006
, 03:09 AM
#2
| |||
| |||
|
|
I want to copy a table with constraints, indexes, .... But with the "SELECT * INTO MyTableCopy FROM MyTable" the column's properties aren't copied (as the NOT NULL property). Can someone help me please ? |
Something like
pg_dump -f out.sql -s -n myschema -t mytable
Then edit out.sql and change names etc. appropriately.
Run the file as SQL script to create the new table.
There is also CREATE TABLE ... LIKE ...
but this will only copy NOT NULL constraints and column defaults.
Yours,
Laurenz Albe
#3
| |||
| |||
|
|
Manu <zzzzaaaaa (AT) blabla (DOT) com> wrote: I want to copy a table with constraints, indexes, .... But with the "SELECT * INTO MyTableCopy FROM MyTable" the column's properties aren't copied (as the NOT NULL property). Can someone help me please ? I would export the table with pg_dump. Something like pg_dump -f out.sql -s -n myschema -t mytable Then edit out.sql and change names etc. appropriately. Run the file as SQL script to create the new table. There is also CREATE TABLE ... LIKE ... but this will only copy NOT NULL constraints and column defaults. Yours, Laurenz Albe |
#4
| |||
| |||
|
|
I tried with CREATE TABLE ... LIKE ... but it doesn't run : CREATE TABLE MyTable LIKE MyOldTable; ERROR: parser: parse error at or near "like" at character 23 Is the syntax correct ? |
CREATE TABLE MyTable (LIKE MyOldTable);
If you want to copy column defaults, use the INCLUDING DEFAULTS clause.
From 8.2 on there will also be an INCLUDING CONSTRAINTS clause to copy
constraints.
Yours,
Laurenz Albe
#5
| |||
| |||
|
|
Manu <zzzzaaaaa (AT) blabla (DOT) com> wrote: I tried with CREATE TABLE ... LIKE ... but it doesn't run : CREATE TABLE MyTable LIKE MyOldTable; ERROR: parser: parse error at or near "like" at character 23 Is the syntax correct ? No, it should be CREATE TABLE MyTable (LIKE MyOldTable); If you want to copy column defaults, use the INCLUDING DEFAULTS clause. From 8.2 on there will also be an INCLUDING CONSTRAINTS clause to copy constraints. Yours, Laurenz Albe |
#6
| |||
| |||
|
|
CREATE TABLE MyTable (LIKE MyOldTable); It doesn't work. I have the message : ERROR: LIKE in table definitions not yet supported |
You can either create the table by hand or use pg_dump as explained
in my first reply.
You can also try a client tool like pgAdminIII that can generate a
CREATE TABLE statement for a table.
Yours,
Laurenz Albe
#7
| |||
| |||
|
|
Manu <zzzzaaaaa (AT) blabla (DOT) com> wrote: CREATE TABLE MyTable (LIKE MyOldTable); It doesn't work. I have the message : ERROR: LIKE in table definitions not yet supported Then your PostgreSQL version is probably too old. You can either create the table by hand or use pg_dump as explained in my first reply. You can also try a client tool like pgAdminIII that can generate a CREATE TABLE statement for a table. Yours, Laurenz Albe |
#8
| |||
| |||
|
|
Manu <zzzzaaaaa (AT) blabla (DOT) com> wrote: CREATE TABLE MyTable (LIKE MyOldTable); It doesn't work. I have the message : ERROR: LIKE in table definitions not yet supported Then your PostgreSQL version is probably too old. Or it's a typo -( misplaced -: try |
HansH
#9
| |||
| |||
|
|
Or it's a typo -( misplaced -: try CREATE TABLE MyTable LIKE ( MyOldTable ); |
Is there any version of PostgreSQL that supports that syntax?
Yours,
Laurenz Albe
#10
| |||
| |||
|
|
HansH <hans (AT) somewhere (DOT) invalid> wrote: Or it's a typo -( misplaced -: try CREATE TABLE MyTable LIKE ( MyOldTable ); Your are guessing, right? Is there any version of PostgreSQL that supports that syntax? Yours, Laurenz Albe |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.