Menu
I have a PostgreSQL 9.1 database with 100 or so tables that were loaded into the 'public' schema. I would like to move those tables (but not all of the functions in 'public') to a 'data' schema.
I know that I can use the following to move 1 table at a time.
ALTER TABLE [tablename] SET SCHEMA [new_schema] Is it possible to move all of the tables to the new schema in one operation? If so, what would be the most efficient way to accomplish this task?
672
To change the schema or tablespace of a table, you must also have CREATE privilege on the new schema or tablespace. To add the table as a new child of a parent table, you must own the parent table as well. Also, to attach a table as a new partition of the table, you must own the table being attached.
SET search_path TO inventory, public; Now inventory is a default schema and you don't need to mention it explicitly to access it. You can change the name or the ownership of a schema by using ALTER SCHEMA. DROP SCHEMA [IF EXISTS] schema_name [CASCADE | RESTRICT];
DO will do the trick:
DO $$ DECLARE row record; BEGIN FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' -- and other conditions, if needed LOOP EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA [new_schema];'; END LOOP; END; $$; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
