PostgreSQL: Difference between revisions
Jump to navigation
Jump to search
Created page with "== Links == * [https://wiki.postgresql.org/wiki/Don't_Do_This Don't Do This] - list of things not to do in PostgreSQL, with explanations." |
|||
| (6 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
== 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 <code>ON CONFLICT DO UPDATE</code> to the end of the INSERT | |||
* Only inserting if a value doesn't exist can be achieved by adding <code>ON CONFLICT DO NOTHING</code> 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) | |||
* <code>SHOW TABLES</code> becomes <code>\dt</code> (<code>\dt+</code> for additional info) | |||
* <code>DESCRIBE tbl</code> becomes <code>\d tbl</code> (<code>\d+ tbl</code> for additional info) | |||
* <code>EXPLAIN</code> becomes <code>EXPLAIN ANALYZE</code> (very different output, much more detail in some respects but also harder to interpret) | |||
== Links == | == Links == | ||
* [https://wiki.postgresql.org/wiki/Don't_Do_This Don't Do This] - list of things not to do in PostgreSQL, with explanations. | * [https://wiki.postgresql.org/wiki/Don't_Do_This Don't Do This] - list of things not to do in PostgreSQL, with explanations. | ||
Latest revision as of 10:38, 6 January 2022
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 UPDATEto the end of the INSERT - Only inserting if a value doesn't exist can be achieved by adding
ON CONFLICT DO NOTHINGto 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 TABLESbecomes\dt(\dt+for additional info)DESCRIBE tblbecomes\d tbl(\d+ tblfor additional info)EXPLAINbecomesEXPLAIN 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.