How to copy structure and contents of a table, but with separate sequence?
Postgres 10 or later
Postgres 10 introduced IDENTITY
columns conforming to the SQL standard (with minor extensions). The ID column of your table would look something like:
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
Syntax in the manual.
Using this instead of a traditional serial
column avoids your problem with sequences. IDENTITY
columns use exclusive, dedicated sequences automatically, even when the specification is copied with LIKE
. The manual:
Any identity specifications of copied column definitions will only be
copied ifINCLUDING IDENTITY
is specified. A new sequence is created
for each identity column of the new table, separate from the sequences
associated with the old table.
And:
INCLUDING ALL
is an abbreviated form ofINCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS
.
The solution is simpler now:
CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING ALL);
INSERT INTO t_mytable TABLE mytable;
SELECT setval(pg_get_serial_sequence('t_mytable', 'id'), max(id)) FROM tbl;
As demonstrated, you can still use setval()
to set the sequence's current value. A single SELECT
does the trick. pg_get_serial_sequence()
]6 gets the name of the sequence.
db<>fiddle here
Related:
- How to reset postgres' primary key sequence when it falls out of sync?
- Is there a shortcut for SELECT * FROM?
- Creating a PostgreSQL sequence to a field (which is not the ID of the record)
Original (old) answer
You can take the create script from a database dump or a GUI like pgAdmin (which reverse-engineers database object creation scripts), create an identical copy (with separate sequence for the serial
column), and then run:
INSERT INTO new_tbl
SELECT * FROM old_tbl;
The copy cannot be 100% identical if both tables reside in the same schema. Obviously, the table name has to be different. Index names would conflict, too. Retrieving serial numbers from the same sequence would probably not be in your best interest, either. So you have to (at least) adjust the names.
Placing the copy in a different schema avoids all of these conflicts. While you create a temporary table from a regular table like you demonstrated, that's automatically the case since temp tables reside in their own temporary schema.
Or look at Francisco's answer for DDL code to copy directly.
Create table (structure) from existing table
Try:
Select * Into <DestinationTableName> From <SourceTableName> Where 1 = 2
Note that this will not copy indexes, keys, etc.
If you want to copy the entire structure, you need to generate a Create Script of the table. You can use that script to create a new table with the same structure. You can then also dump the data into the new table if you need to.
If you are using Enterprise Manager, just right-click the table and select copy to generate a Create Script.
Copy data into another table
If both tables are truly the same schema:
INSERT INTO newTable
SELECT * FROM oldTable
Otherwise, you'll have to specify the column names (the column list for newTable
is optional if you are specifying a value for all columns and selecting columns in the same order as newTable
's schema):
INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable
How can I create a copy of an Oracle table without copying the data?
Just use a where clause that won't select any rows:
create table xyz_new as select * from xyz where 1=0;
Limitations
The following things will not be copied to the new table:
- sequences
- triggers
- indexes
- some constraints may not be copied
- materialized view logs
This also does not handle partitions
How to copy data from one to table another table with different structure using PL/SQL
You can use Pivot Option. This best suits your requiement. Hope below snippet helps.
--Table cretion for test
CREATE TABLE TABLE1
(
ROW_ID NUMBER,
USERID VARCHAR2(75),
TEMPLATE_DATA BLOB,
FINGERID VARCHAR2(20),
ISLOCKED NUMBER,
ISDUPLICATE NUMBER(3)
);
--Data insertion
INSERT INTO TABLE1
SELECT LEVEL ,
CASE
WHEN LEVEL < 11
THEN 'AV_DUMMY'
ELSE 'SR_DUMMY'
END,
UTL_RAW.CAST_TO_RAW('TEMPLATE_DATA'),
LEVEL,
CASE
WHEN LEVEL < 11
THEN 0
ELSE 1
END,
CASE
WHEN LEVEL < 11
THEN 0
ELSE 1
END
FROM DUAL
CONNECT BY LEVEL < 21;
-- Data modification as per requirement
UPDATE TABLE1
SET FINGERID = SUBSTR(FINGERID,2,1)
WHERE FINGERID > 10;
CREATE TABLE TABLE2 AS
SELECT '1' ROW_ID,
USERID,
TEMPLATEDATA FINGERID_1_TEMP,
FINGERID_1_FN FINGERID_1,
ISLOCKED ISLOCKED_1,
ISDUPLICATE ISDUPLICATE_1,
TEMPLATEDATA FINGERID_2_TEMP,
FINGERID_2_FN FINGERID_2,
ISLOCKED ISLOCKED_2,
ISDUPLICATE ISDUPLICATE_2,
TEMPLATEDATA FINGERID_3_TEMP,
FINGERID_3_FN FINGERID_3,
ISLOCKED ISLOCKED_3,
ISDUPLICATE ISDUPLICATE_3,
TEMPLATEDATA FINGERID_4_TEMP,
FINGERID_4_FN FINGERID_4,
ISLOCKED ISLOCKED_4,
ISDUPLICATE ISDUPLICATE_4,
TEMPLATEDATA FINGERID_5_TEMP,
FINGERID_5_FN FINGERID_5,
ISLOCKED ISLOCKED_5,
ISDUPLICATE ISDUPLICATE_5,
TEMPLATEDATA FINGERID_6_TEMP,
FINGERID_6_FN FINGERID_6,
ISLOCKED ISLOCKED_6,
ISDUPLICATE ISDUPLICATE_6,
TEMPLATEDATA FINGERID_7_TEMP,
FINGERID_7_FN FINGERID_7,
ISLOCKED ISLOCKED_7,
ISDUPLICATE ISDUPLICATE_7,
TEMPLATEDATA FINGERID_8_TEMP,
FINGERID_8_FN FINGERID_8,
ISLOCKED ISLOCKED_8,
ISDUPLICATE ISDUPLICATE_8,
TEMPLATEDATA FINGERID_9_TEMP,
FINGERID_9_FN FINGERID_9,
ISLOCKED ISLOCKED_9,
ISDUPLICATE ISDUPLICATE_9,
TEMPLATEDATA FINGERID_10_TEMP,
FINGERID_10_FN FINGERID_10,
ISLOCKED ISLOCKED_10,
ISDUPLICATE ISDUPLICATE_10
FROM
(SELECT ROW_ID,
USERID,
FINGERID,
ISLOCKED,
ISDUPLICATE,
UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(TEMPLATE_DATA)) TEMPLATEDATA
FROM TABLE1
) PIVOT ( MAX(ROW_ID), MAX(FINGERID) FN FOR FINGERID IN (1 AS FINGERID_1,2 AS FINGERID_2,3 AS FINGERID_3,4 AS FINGERID_4,5 AS FINGERID_5,6 AS FINGERID_6,7 AS FINGERID_7,8 AS FINGERID_8,9 AS FINGERID_9,10 AS FINGERID_10) );
How do I copy data from one table to another in postgres using copy command
You cannot easily do that, but there's also no need to do so.
CREATE TABLE mycopy AS
SELECT * FROM mytable;
or
CREATE TABLE mycopy (LIKE mytable INCLUDING ALL);
INSERT INTO mycopy
SELECT * FROM mytable;
If you need to select only some columns or reorder them, you can do this:
INSERT INTO mycopy(colA, colB)
SELECT col1, col2 FROM mytable;
You can also do a selective pg_dump and restore of just the target table.
Related Topics
What's the Best Practice of Naming Stored Procedure for T-Sql
SQL - Conditional Where Clause
How to Force Oracle to Change a Query's Plan Without Using Hints
To Get Column Names from Table Having a Particular Value
Retrieving a Row, with Data from Key-Value Pair Table in MySQL
Identity_Insert Is Already on for Table 'X'. Cannot Perform Set Operation for Table 'Y'
Sql: Sort by Priority, But Put 0 Last
Do You Prefer Verbose Naming When It Comes to Database Columns
Sql: Retrieve Only the Records Whose Value Has Changed
How to Get Age in Years,Months and Days Using Oracle
Can You Have a Foreign Key Onto a View of a Linked Server Table in SQLserver 2K5
How to Merge Multiple Database Files in SQLite
How to Log All Failed SQL Statements in Oracle 10G