When I first started using Postgres, I made the mistake of thinking it was identical to other SQL-like languages. It is for the most part, but there are some subtle differences. In this article, I will cover off some of the differences, I would have found useful to know.
First of all, connecting to a Postgres DB from the command line. It’s pretty simple: psql -d mydb -U myuser. This is quite self explanatory – connect a given database as a particular user.
Next, let’s show all available databases using \l.
We can then switch to a particular database using \c dbname username.
We can list all the users and their roles using \du.
Next, let’s list all the tables in our database using \dt.
We might want to describe table, we can do that using \d tablename.
If we want to run an SQL command against a table, we can do as usual SELECT * from tablename;. Just remember to add the semicolon at the end.
Now, if we want to run the same command again, we don’t need to retype it, we can just use \g.
We can add timing to our queries to see how they run using \timing.
db=> \timing
db=> select count(*) from db_skills;
count
-------
184
(1 row)
Time: 1.001 ms
We can list all previous commands using \s.
We can run commands from a file too, using \i filename.
And of course, we can quit the shell using \q.
