-
Run a PostgreSQL .sql File Using Command Line Arguments
psql is capable of running SQL statements written into a .sql file. Either, you want to run it on a local or remote server you can use psql client. TL;DR: psql -f filename Requirement Either for new product installation, migration, cloning environment, we may need to run sql scripts written in files. In my professional…
-
What’s the PostgreSQL Datatype Equivalent to MySQL AUTO INCREMENT?
Each row in a data table, should be identified, in most cases, using a numeric identifier is the easiest way to achieve it. TL;DR: generated always as identity Creating an Identity Column When you say auto incrementing identitiy column, every DBA think about same, “sequences”. In PostgreSQL you can define a sequence and mark it…
-
Insert Text with Single Quotes in PostgreSQL
In this article we will discuss about how to insert text with single quotes in PostgreSQL. PostgreSQL escapes single quote character with a trick, Today we will cover structure and escaping. TL;DR: ” => real solution Let’s create a demo table to try first. Let’s insert a row with text that includes single quote character…
-
Creating a Copy of a Database in PostgreSQL
When you need to create a copy of a database there are several ways to achieve it in PostgeSQL. TL;DR: createdb -O ownerusername -T originaldb newdb If you need to clon a database, you need to plan it carefully. Because you approach will change either if your database is under traffic or not. In this…
-
psql: FATAL: database “user” does not exist
Hey! We are here with another common issue. TL;DR: psql -d <database_name> By default, psql client tries to connect to a database with your username. Let’s see together: In the example above, i created two users testuser1 and testuser2, defined roles for those roles to let them login. I switched to those user and tried…
-
PostgreSQL error: Fatal: role “username” does not exist
Today, we are together here for a very common error. Especially around beginners. Until you get used to PostgreSQL’s role structure and initial user configuration you will face this issue for a few times. TL;DR: su – postgres and psql Initial User Configuration Initially, only postgres user is granted to login to server. No 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…
-
Which version of PostgreSQL am I running?
You just got hired, asked to perform a health check on PostgreSQL servers and you don’t know exact version of PostgreSQL. TL;DR: SELECT VERSION(); or pg_config –version When we say version, there are two possibilities, client version and server version. Checking Client Version psql is our client on terminals, to check it’s version psql –version…
-
How to Switch Database in psql?
Sometimes we host multiple database in single PostgreSQL instance and we need to switch between them in psql. For this article, i created two databases: test1 and test2 will be our database to switch between. Tip of The Day You can use \l shortcut to list database and database templates in PostgreSQL. Also \list can…
-
How to Exit psql Command Line
PostgreSQL provides an interactive command line utility for it’s users. Mostly it’s called as psql. You can run SQL queries from psql command line tool, also statements like \d too. In this article we are going to cover how to exit from PostgreSQL command line. Answer of question “how to exit from PostgreSQL command line?”…