-
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…
-
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…
-
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…
-
Describe Table in PostgreSQL
Describe table is a commonly used query in MySQL. In PostreSQL there isn’t a direct equivalent of this command. In PostgreSQL command line (pgsql) you can use: Some cloud provider use a special build or they don’t directly provide pg interface, therefore you can’t use \d \dt commands. You might need an SQL alternative to…
-
Show tables in PostgreSQL
How to show tables in PostgreSQL is a common question for beginners. In this article we will discuss showing tables in PostgreSQL. In PostgreSQL all information about DB objects stored under information_schema. You can use information_schema.tables table to list and show tables. A simple query to list all tables and schemas: If you want to…
-
PostgreSQL for Beginners
Hello, this is a test purpose article about PostgreSQL for beginners. PostgreSQL is a powerful, open-source relational database management system (RDBMS). It is a popular choice for a wide range of applications, including web applications, data warehousing, and big data analytics. PostgreSQL is known for its reliability, scalability, and flexibility. This blog article is designed…