-
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…
-
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…
-
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…
-
PostgreSQL Difference between text and varchar
Today we are going to discuss about character data types in PostgreSQL. On internet you can find misinformation about their performances, storage methods. But today we are going to correct all of them. TL;DR: There is no performance difference between CHAR, VARCHAR and TEXT. You can find PostgreSQL documentation about character datatypes from this link.…
-
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…
-
How to Import CSV File Data Into a PostgreSQL Table
We have different data sources for databases, especially during test case design, CSV files adds more compatibility and flexibility. TL;DR: COPY tablename FROM ‘/path/to/file.csv’ WITH (FORMAT csv); CSV Format CSV stands for comma separated value. It’s a portable data format, stores data in a text format separated with commas. Impoting CSV Into a PostgreSQL Table…
-
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…