CODEWITHSUNDEEP
amazon-redshift
leerssej
leerssej

Reputation: 14958

Change Schema Name and Then Change It Back Again

In a clean-up effort, I changed some schema names in Redshift. Then I nearly immediately switched the schema names back. All but a few of the tables disappeared.

Is this a known issue?

Should I be more careful about renaming tables to previous names?

sql> ALTER SCHEMA common_schema RENAME TO common_schema_v1

[2019-05-01 14:39:25] completed in 432 ms

sql> ALTER SCHEMA common_schema_v1 RENAME TO common_schema

[2019-05-01 14:48:41] completed in 371 ms

Upvotes: 0

Views: 387

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

The tables would not normally be dropped by a rename operation.

It could be the rename changed your search path and you're just not seeing the tables now. Try re-adding the schema name to your search path.

SHOW search_path;
SET search_path TO public, common_schema;

You can also look for the tables in the catalog to confirm they're still there.

SELECT * 
FROM information_schema.tables 
WHERE table_schema = 'common_schema'
;

Or

SELECT nspname AS schema_name
     , relname AS table_name
FROM pg_class c
   , pg_namespace n 
WHERE n.oid = c.relnamespace 
  AND c.reltype > 0 
  AND n.nspname = 'common_schema' 
ORDER BY 1, 2 
;

Upvotes: 2

Related Questions