Nowadays there are numerous databases management systems (DBMS) in the market. According to the DB Engines ranking the most popular open source databases in the market today are MySQL and PostgreSQL.
All DBMS have in common that they offer some tooling to create and maintain the database and its tables. This post features a basic tutorial to manage a PostgreSQL databases using the powerful psql command line interface (“PostgreSQL interactive terminal”).
Keep in mind there are two types of commands you can run in the psql shell:
- meta-commands, intended to save you time when performing routine tasks;
- SQL commands, intended to issue queries to the database.
Connecting to the database
First of all, we need to connect to the database like so:
psql --host=localhost --username=postgres --password --dbname=forex
Password for user postgres:
Please note the database can be located either on your own machine (—host=localhost) or remote, see Step #3 in this post for an example where we connected to a database hosted by Amazon.
Meta commands
Meta commands are there to make live for a database administrator easier. Meta commands always start with a backslash (“\”) often followed by just one single character:
| \h (or \help) | displays all commands |
| \h CREATE DATABASE | displays help on a specific command |
| \l | list all databases |
| \du | displays all users |
| \c (or \connect) dbname | connect (or switch) to db called dbname |
| \dt (just \d also works) | display all tables within selected db |
| \i | insert records from dump |
| \q | quit psql shell |
See under Step #3 in this post for an example how to insert records from a dump into your database.
SQL commands
You can also run the regular SQL commands in the psql shell, like so:
SELECT * FROM tbl_currency LIMIT 10;
Important: In order to have your SQL commands executed, make sure you include the semicolon (;) at the end of the command!
Finally
Of course, in this basic tutorial we only scratched the surface of all the good stuff offered by the psql command line, but this short overview should help you to survive at least. If you want to dig any further, have a look at the technical documentation.