-
How to Terminate a PostgreSQL Session?
During day to day operation of PostgreSQL database administrator we may need to terminate a PostgreSQL session, in this article we are going to cover how to terminate a PostgreSQL session. What is a PostgreSQL Session? I would like to have a brief introduction of the terminology here, because in each database engine, name of…
-
How to Make Case Insensitive Query in PostgreSQL
Hello, today we are going to cover how to make case insensitive query in PostgreSQL topic. Important Note for How to Make Case Insensitive Query in PostgreSQL Before starting i would like to highlight, charset and collation is the biggest limitation for case insensitive queries in PostgreSQL. For instance, in Turkish (Latin) alphabet i letter…
-
Where Does PostgreSQL Store Configuration Files?
PostgreSQL’s file structure is complicated and a database engine consists of multiple configuration files. Where Does PostgreSQL Store Configuration Files? You can use following command to find PostgreSQL configuration files? The command above shows the configuration file and where does postgresql store configuration files. As an alternative you can locate configuration file from shell and…
-
How to Specify Password to psql Non-Interactively?
psql can be used as a remote scripting tool for PostgreSQL. While running a script remotely you may need to specify a user’s password. Normally, psql asks for a user’s password interactively. TL;DR export PGPASSWORD=password How psql works? If you specify a remote server that is not same with server you run psql, psql asks…
-
Import SQL Dump Into PostgreSQL Database
Data migrations, or creating a lower level test environment with same data has a requirement, exporting and importing a SQL dump into PostgreSQL database. Today we will cover importing SQL dump into PostgreSQL database. TL;DR pg_restore –dbname=database_name /tmp/path/to/dump.sql Importing SQL Dump Either you need to restore data or you want to create a lower level…
-
How to Reset PostgreSQL Key Sequence
After some administrative operations you might need to Reset key sequence in PostgreSQL. During modernization journey, team PostgreSQL designed shortcuts and improved reset key sequence workflow. TL;DR: ALTER SEQUENCE sequence_name RESET Resetting Key Sequence in PostgreSQL Sequence is the main object that keeps up with row identifiers. When you create a table with a sequence…
-
PostgreSQL: Upgrade a User to be a Superuser?
In this article we are going to cover how to upgrade a user to be a superuser in PostgreSQL. In PostgreSQL there is a unique term “role”, the term “role” will be discussed later because it’s a complicated feature by itself. TL;DR: ALTER USER username WITH SUPERUSER; Upgrade a User to be a Superuser In…
-
Insert, on Duplicate Update in PostgreSQL?
You have a value to insert to database, but you are not sure, if this row exists on database. What will you do? Upsert is the right term for you, UPdate, if fails inSERT. We can generalize it as insert, on duplicate update. TL;DR: INSERT INTO table () VALUES () ON CONFLICT (row) DO UPDATE…
-
How to drop a PostgreSQL database if there are active connections to it?
No, you can not. For all database engine, the answer is same, you can not drop a database if there are active connections. But PostgreSQL and some other database engines provide options to kill active connections automatically. TL;DR: DROP DATABASE database_name WITH (FORCE); Dropping a database with active connections As mentioned above, it is not…
-
Saving PL/pgSQL output from PostgreSQL to a CSV file
Sometimes we need to save output of our queries to a CSV file format, may be with a different delimiter. In this article we are going to cover how to save psql output to a file. TL;DR: Copy (My amazing query) to /path/to/file.csv with CSV DELIMITER ‘,’ HEADER; In TL;DR section i mentioned only one…