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
—————–

\list

or

\l

to list all databases

\connect {databasename}

to connect to database

\dt

to list tables in current database

Munch
————
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 http://www.postgresql.org/docs/9.3/static/app-psql.html

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 http://www.the-art-of-web.com/sql/upsert/