Rename all tables in a Postgresql schema

After a conversion usually done through AWS Database Migration Service, you may have a Postgresql database converted with table names in uppercase. These tables needs renaming, because you can’t access it unless you specify the table name between quotes, like so.

Select count(*) from schema."MY_TABLE_NAME";

Otherwise the table are not found, because all identifiers, including column name are Case-Sensitive in PostgreSQL. To overcome this, you’ll need to rename all the tables of the converted schema. Here’s how to generate the ALTER script to rename all tables into lower case letters.

SELECT 'ALTER TABLE "'||pgc.relname||'" RENAME TO '||lower(pgc.relname)||';'
FROM pg_catalog.pg_class pgc
LEFT JOIN pg_catalog.pg_namespace pgn ON pgn.oid = pgc.relnamespace
WHERE pgc.relkind ='r'
AND pgn.nspname='public'
ORDER BY 1;

Once you execute the script, simply save the output and execute it to perform the rename operation.

Tags:

Rename a Postgresql database

#database #postgresql

Here's the procedure to rename a PostgreSQL database: 1. Disconnect from the database that you want to rename and connect to a different database. 2. Check and terminate all active connections to the database that you want to rename. 3. Use the `ALTER DATABASE` statement to rename the database to the new one. Let’s take a look at an example of... Read more

20 May 2019 - 1 minute read

How to duplicate a Postgresql database

#database #postgresql

If you need to duplicate an existing Postgresql database, and possibly transfer ownership of the database objects to a new user, here's how to do that in a quick way: ```sql -- First, I recommand getting the size of the database to copy, as this might be important for the rest of the process. SELECT pg_database.datname,pg_size_pretty(pg_databas... Read more

20 Apr 2019 - less than 1 minute read

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

Rename a Postgresql database

#database #postgresql

Here's the procedure to rename a PostgreSQL database: 1. Disconnect from the database that you want to rename and connect to a different database. 2. Check and terminate all active connections to the database that you want to rename. 3. Use the `ALTER DATABASE` statement to rename the database to the new one. Let’s take a look at an example of... Read more

20 May 2019 - 1 minute read

How to duplicate a Postgresql database

#database #postgresql

If you need to duplicate an existing Postgresql database, and possibly transfer ownership of the database objects to a new user, here's how to do that in a quick way: ```sql -- First, I recommand getting the size of the database to copy, as this might be important for the rest of the process. SELECT pg_database.datname,pg_size_pretty(pg_databas... Read more

20 Apr 2019 - less than 1 minute read

Change default schema on Postgresql

#database #postgresql

In PostgreSQL, users can have many namespaces to resolve objects names. These are called schemas like in Oracle, and can be altered through the `search_path` variable. Here's how to check current search path: ```sql SHOW search_path; /* Result search_path ------------------ "$user", public */ ``` Usually it defaults to the **username** and **... Read more

20 Jan 2018 - less than 1 minute read