PostgreSQL

Ubuntu installation

Install PostgreSQL and its contrib package:

sudo apt install postgresql postgresql-contrib

Create a new user (with the same username as your Linux login):

sudo -u postgres createuser --interactive

Create a database:

createdb my_test_db

Connect via psql:

psql -d my_test_db

Tips

• 'Upserting' (INSERT turning into an UPDATE if there is a conflict) can be achieved by adding ON CONFLICT DO UPDATE to the end of the INSERT
• Only inserting if a value doesn't exist can be achieved by adding ON CONFLICT DO NOTHING to the end of the INSERT

Comparison with MySQL

• Both default to using a socket for local connections on Unix hosts (instead of TCP/IP)
• SHOW TABLES becomes \dt (\dt+ for additional info)
• DESCRIBE tbl becomes \d tbl (\d+ tbl for additional info)
• EXPLAIN becomes EXPLAIN ANALYZE (very different output, much more detail in some respects but also harder to interpret)

Links

• Don't Do This - list of things not to do in PostgreSQL, with explanations.