Duplicate postgresql schema including sequences
And so after some thinking I went along with updating sql function mentioned in my first post so now it looks like this:
CREATE FUNCTION copy_schema(
source_schema character varying,
target_schema character varying,
copy_data boolean)
RETURNS integer AS
$BODY$
DECLARE
t_ex integer := 0;
s_ex integer := 0;
src_table character varying;
trg_table character varying;
BEGIN
if (select 1 from pg_namespace where nspname = source_schema) THEN
-- we have defined target schema
s_ex := 1;
END IF;
IF (s_ex = 0) THEN
-- no source schema exist
RETURN 0;
END IF;
if (select 1 from pg_namespace where nspname = target_schema) THEN
-- we have defined target schema need to sync all table layout
t_ex := 1;
ELSE
EXECUTE 'CREATE SCHEMA '||target_schema||' AUTHORIZATION user';
END IF;
FOR src_table IN
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = source_schema
LOOP
trg_table := target_schema||'.'||src_table;
EXECUTE 'CREATE TABLE ' || trg_table || ' (LIKE ' || source_schema || '.' || src_table || ' INCLUDING ALL)';
EXECUTE 'CREATE SEQUENCE ' || trg_table || '_id_seq OWNED BY '||trg_table || '.id';
EXECUTE 'ALTER TABLE ' || trg_table || ' ALTER COLUMN id SET DEFAULT nextval('''|| trg_table || '_id_seq''::regclass)';
IF (copy_data = true) THEN
EXECUTE 'INSERT INTO ' || trg_table || '(SELECT * FROM ' || source_schema || '.' || src_table || ')';
END IF;
END LOOP;
return t_ex;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
This is not quite universal solution for everybody, but as all my tables in schema have serial field named id, it fits me.
Version suggested by @erwin-brandstetter with dump / hack dump file / restore dump file back again is commonly seen on the forums as the way to go.
In case of dedicated server it could work, in case of shared hosting (or need of less dependencies on outside scripts) the way of internal function seems better.
How can I copy sequences from one schema to another in Postgresql?
After looking at the stack trace, I found references to schema 1
being made. For example, C:\...\CDbCommandBuilder.php(62): CDbConnection->getLastInsertID("public.lime_user_groups_ugid_seq")
, where public
is schema 1
. And because schema 1
didn't appear in the pg_dump
, I knew it must've been an issue with the app configs. I found there were a few hidden schema 1
strings to change in the codebase with the help of https://forums.limesurvey.org/forum/installation-a-update-issues/111790-installation-on-a-different-schema. This means the method I used to migrate/copy tables and their sequences from one schema to another works.
Update: The problem was in the app configs. Our database uses a pgpool connection with two pg servers. Connecting to the pool created the error. It went away after connected directly to the main/master pg server.
How to duplicate schemas in PostgreSQL
You can probably do it from the command line without using files:
pg_dump -U user --schema='fromschema' database | sed 's/fromschmea/toschema/g' | psql -U user -d database
Note that this searches and replaces all occurrences of the string that is your schema name, so it may affect your data.
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.
How to copy certain tables from one schema to another within same DB in Postgres keeping the original schema?
You can use create table ... like
create table schema2.the_table (like schema1.the_table including all);
Then insert the data from the source to the destination:
insert into schema2.the_table
select *
from schema1.the_table;
Related Topics
Sql: Subquery Has Too Many Columns
Sql - How to Get The Unique Key's Column Name from Table
Why Can't You Use Sqlite Rowid as a Primary Key
Acts-As-Taggable-On Find All Tags by Context
How to Drop All Foreign-Key Constraints on a Table in SQL Server 2000
How to Use If Statement After a Cte (SQL Server 2005)
Oracle Subquery Does Not See the Variable from the Outer Block 2 Levels Up
While Loop to Iterate Through Databases
How to Retrieve a Column Value by Name Using Golang Database/Sql
Pyspark/Spark Window Function First/ Last Issue
SQL Server Management Studio 2012 Hangs
Why Are SQL Server Inserts So Slow
Self-Referential Table Fields in MySQL
Get the List of Stored Procedures Created And/Or Modified on a Particular Date
Convert Excel Worksheet to SQL Script
Are There Any Limits on Length of String in MySQL