Actuarial Data Science

Bridging the Gap between Actuarial and Data Science

Sat 09 September 2017

How to survive the psql command line

Posted by Pieter Marres in Articles   

psql-command-line

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.


 

Comments

blog comments powered by Disqus