PostgreSQL

From Rixort Wiki
Jump to navigation Jump to search

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.