Postgres SQL

Install PostgreSQL client 

Mac:

> brew install postgresql

Ubuntu:

> apt-get install postgresql-client

Connect using psql

> psql --host=tardis.dev.pci.irdeto.com --port=5433 --dbname=postgres --username=dbmaster --password

or

> psql -h db-master -p 5432 -d mcjdev -U keystone -W

Dump Database

> pg_dump --host=localhost --port=5432 --dbname=TardisDB --username=dbmaster --password >db_dump.sql

Import DB Dump

> psql --host=localhost --port=5432 --dbname=TardisDB --username=dbmaster --password < db_dump.sql

Create User

> CREATE User <username> WITH PASSWORD 'password';

> SELECT * FROM pg_user;

> DROP USER <username>;

Alter User's Password

> ALTER USER <username> WITH PASSWORD 'password';

Create Database

> CREATE DATABASE "dbname";

> CREATE DATABASE "dbname" WITH OWNER = "provisioner";

> GRANT ALL PRIVILEGES on DATABASE <dbname> TO <user>;

Update a Field in a Table

> update <table> set <field> = <value> where <other_field> = <othe_value>;

ex: update flyway_schema_history set checksum = -673794054 where version = '000003';

Query using JSON field

> select * from users where attributes->>'name' like '%johnmx%';

Common Commands