Archive for the ‘PostgreSQL’ Category

Export PostgreSQL query result to file

We can take a backup file of database using pg_dump command, similarly can we take backup of a select query result. Syntax to Export PostgreSQL query result to file: copy(select * from table_name) to ‘path’; Examples:

Drop PostgreSQL database if there are active connections to it

Scenario: We want to drop database(assume database name as company_new) in PostgreSQL. But, the database contains more active sessions(idle). When we tried to drop database, we will receive following error message. postgres=# drop database company; ERROR: database “company” is being accessed by other users DETAIL: There is 1 other session using the database. Solution: Step1: […]

Kill PostgreSQL Session or Connection

Why we will session or connection? If connection is in hung state. If connection consumes more resources. If connection(user pid) blocks other resources. Solution: Kill the culprit process id. To kill any connection, the user who is going to kill pid(connection) must be super user in PostgreSQL.

Switch database in PostgreSQL

To switch database in Sybase, we run command: use database_name go To switch database in SQL Serve, MySQL or Oracle, we run command: use database_name; But, when comes to PostgreSQL, we use \connect database_name; or \c database_name; Examples:

Create a copy of database in PostgreSQL

PostgreSQL allows us to use of any existing database on the server as a template when creating a new database. Syntax to Create a copy of database in PostgreSQL CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser; If we run the above command, it take all objects of originaldb to newdb. Example:

How to change the owner of database in PostgreSQL

If we want to alter(change) the owner, we must own the database and also be a direct or indirect member of the new owning role, and we must have the CREATEDB privilege. How to change the owner of database in PostgreSQL 1. Check the current owner of the database. Run the below query to get […]

How to get list long running processes in PostgreSQL

Why processes(sql queries) takes long time? 1. Table(object) not designed properly 2. Memory issues 3. Table Scan 4. Others How to get list long running processes in PostgreSQL We can get this information from pg_stat_activity object. Following query gives logn running process, on which database it is running, start time of pid(process id) and sql […]

How to get list of table columns in PostgreSQL

We can get list columns using describe in Oracle/SQL Server or other RDBMS. But, describe not works in PostgreSQL. We can get the list of columns in PostgreSQL using \d table_name or running query on table INFORMATION_SCHEMA.COLUMNS. Method 1: Get list of columns of table in PostgreSQL using \d table_name

Difference between delete and truncate

Delete: Whenever we are using delete from tablename, then automatically deleted data internally stored in buffer. We can get it back by using rollback command.

emp table script PostgreSQL

Standard emp table script for PostgreSQL 1) Create table structure 2) Insert data into emp table

Powered by k2schools