-
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…
-
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 Drop All Tables in a PostgreSQL Database?
Today, we will discuss a very dangerous question. How to drop all tables in a PostgreSQL database? This is a very dangerous question, as i see, thats very popular question, i would like to bring it here. The Wrong Way On internet you can find statements like: And grant permissions again. I call this method…
-
How to Change a PostgreSQL User Password
That command is one of the simplest and most common one around different database engines. (Excluding Db2-like Db engines, because it uses OS to validate passwords) Alter is the command we use to change / update an attribute or object in database engines. So simply, change user’s password in PostgreSQL as: Of course, you need…
-
How to Select Fist Row in Each Group?
Sometimes we need maximums of each group, or minimums of each group in SQL. In PostgeSQL, you can use DISTINCT ON statement to achieve this. In PostgreSQL’s documentation DISTINCT ON statement explained as: SELECT DISTINCT ON ( expression [, …] ) keeps only the first row of each set of rows where the given expressions…
-
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?”…