postgres copy data from one table to another

Or for table names that require double-quoting and different schemas: SELECT f_copy_idx('old_TBL', 'table', 'public', 'New_SCHEmA') So if both tables reside in the public schema, you can simple ignore the schema parameters.Ĭall: SELECT f_copy_idx('mytbl', 'newtbl') - all in schema public I added 'public' as default value for the schema parameters _sch and _sch1. This way I get reliable results that do not depend on the setting. To force schema-qualified table names I reset the search_path inside the function ( SET search_path = ''). This is also prepared for non-standard table names that need double-quoting and differing schemas. $func$ LANGUAGE plpgsql SET search_path = '' , _sch text = 'public', _sch1 text = 'public')

If you are confident in your naming scheme you can fully automate: CREATE OR REPLACE FUNCTION f_copy_idx(_tbl text, _tbl1 text All of that is built into the function below. USING btree (somecol) WHERE (cutblade IS NOT NULL) īut we have not yet considered non-standard table names, schemas or the search_path setting. The 2nd regexp_preplace() is more specific and only replaces the actual table name.įor the given examples you get: CREATE INDEX tbl1_tbl_id_idx ON tbl1 USING btree (tbl_id) ĬREATE INDEX tbl1_somecol_nonull_idx ON tbl1 The 1st replace() only replaces the first match in the name (unless instructed otherwise). This query should work flawlessly - but verify that against your actual indexes yourself! SELECT regexp_replace(regexp_replace( Note the three false positives (part of column name or index name) which we do not want to replace. I bolded every occurrence of the original table name. USING btree (somecol) WHERE (cu tblade IS NOT NULL) USING gin (((data -> 'people'::text)) jsonb_path_ops) ĬREATE INDEX tbl_comecol_nonull_idx ON tbl If you are working with the default naming convention of Postgres, index definitions look like this: CREATE INDEX tbl_ tbl_id_idx ON tbl USING btree ( tbl_id) You have to replace the old table name with the new one - and you don't want to replace false positives in the string (like a column name that matches the table name): SQL injection in Postgres functions vs prepared queries.This includes all indexes: PK, partial, functional, unique, with special operator classes, etc. WHERE indrelid = 'public.tbl'::regclass - optionally schema-qualified For a table called tbl in the public schema: SELECT pg_get_indexdef(indexrelid) || ' ' AS idx The query can be considerably simpler, though, using a regclass parameter. To get all index definitions you can use the system catalog information function pg_get_indexdef(index_oid) like already provided.

Postgres copy data from one table to another