Create Postgres DB from command line in Ubuntu 14.04

On ubuntu shell,

sudo su postgres
createdb mydb -O dbownername

If it says “dbownername” role does not exist, you can create it with

createuser dbownername


If above commands does not work, you may be missing following

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib


for more information  createdb manual  createuser manual



List all databases in PostgreSQL psql

Quick bite




to list all databases

\connect {databasename}

to connect to database


to list tables in current database

psql is interactive frontend for PstgreSQL using which you can connect you postgres database and execute commands and SQL. The commands listed above fall under category meta commands.
More information can be found in psql documentation

How to do UPSERT query in PostgreSQL

WITH upsert AS (<update-query-here> RETURNING *) <insert-query-here>  WHERE NOT EXISTS (SELECT * FROM upsert);

<insert-query-here> Should be of form
“INSERT INTO table_name (column1, column2, …) SELECT ‘column_1_value’, ‘column_2_value’, …”
Hence if update query succeeds, insert query wont execute due to WHERE clause.

Thanks to reference