PostgreSQL
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
becomesEXPLAIN 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.