Tag: postgresql
Rename a Postgresql database
#database #postgresqlHere’s the procedure to rename a PostgreSQL database: Disconnect from the database that you want to rename and connect to a different database. Check and terminate all active connections to the database that you want to rename. Use the ALTER DATABASE statement to rename the database to the new one. Let’s take a look at an example of re... Read more
20 May 2019 - 1 minute read
How to duplicate a Postgresql database
#database #postgresqlIf 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: -- 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_database_size(... Read more
20 Apr 2019 - less than 1 minute read
Change default schema on Postgresql
#database #postgresqlIn 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: SHOW search_path; /* Result search_path ------------------ "$user", public */ Usually it defaults to the username and public. So when yo... Read more
20 Jan 2018 - less than 1 minute read
Rename all tables in a Postgresql schema
#database #postgresqlAfter 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, ... Read more
20 Dec 2017 - less than 1 minute read
PostgreSQL Survival Guide
#postgresql #databasePostgreSQL has been gaining a lot of popularity these days, so let’s have a look at the basics of administering a PostgreSQL database. We’ll cover the structure exploration, object manipulation and some user managment. Quick remainder of some mainly used PostgreSQL datatypes: Name Storage size Description ... Read more
20 Jan 2016 - 3 minute read