DEV Community

Cover image for Basic PostgreSQL on the Command Line
Kristen Kinnear-Ohlmann
Kristen Kinnear-Ohlmann

Posted on • Originally published at kristenkinnearohlmann.dev

Basic PostgreSQL on the Command Line

I started working through freeCodeCamp's beta curriculum for Relational Database. I like to have a contrasting educational opportunity when I need a mental break; solutions to data structure and algorithm problems and other coding questions usually come to me when I'm studying a different topic.

Since databases are my original jam, I thought it would be profitable to work on my command line-level skills so I can gain strength with other types of databases. The first lessons introduce you to working with PostgreSQL to learn syntax. Although I'm very familiar with SQL itself, it had been some time since I had worked with Postgres on the command line. Part of the Flatiron School curriculum had students install Postgres (for me, on my WSL install) but we used SQLite for projects so my knowledge of accessing and using Postgres had gotten fuzzy.

After spending a few days on the CodeAlly platform that is included in freeCodeCamp's lessons, I decided to try looking back into my local install and get a set of basic instructions together. This will help when I return to my Flatiron projects to convert them to Postgres and deploy them to publicly available servers. For more information on installing Postgres, see https://www.postgresql.org/.

Working with psql on WSL

I knew I had Postgres installed so the first thing I tried was to invoke it. I received an error message.

Check psl

I vaguely recalled that the service is generally stopped so I tracked down the command to get it started.

Start postgres service

I was now able to access psql. I had created an account for myself as part of my Flatiron studies so my account name appears as the prompt.

Open postgres

I didn't know what databases had been created, so I listed them with \l. There were a lot more than I recalled!

List databases

I connected to one of the databases I recognized from the setup lesson for Flatiron.

Connect to database

I reviewed the objects that were included in this database. The lesson must have been using a blog as the object to model, since one of the objects was a posts table.

Review relations

I further reviewed what the sample lesson asked us to create as fields in the posts table.

Review fields

I connected to a few other relations but found they were empty, so I went ahead and quit Postgres.

Quit Postgres

I also stopped the Postgres service until I needed it again.

Stop Postgres service

Basic commands for psql

  • Check if psql is accessible
psql
Enter fullscreen mode Exit fullscreen mode
  • Start Postgres service
sudo service postgres start
Enter fullscreen mode Exit fullscreen mode
  • List available databases
\l
Enter fullscreen mode Exit fullscreen mode
  • Connect to a specific database to work within it
\c <database_name>
Enter fullscreen mode Exit fullscreen mode
  • Review relations in the database
\d
Enter fullscreen mode Exit fullscreen mode
  • Review details of a specific relation
\d <relation_name>
Enter fullscreen mode Exit fullscreen mode
  • Quit Postgres
\q
Enter fullscreen mode Exit fullscreen mode
  • Stop Postgres service
sudo service postgres stop
Enter fullscreen mode Exit fullscreen mode

Discussion (3)

Collapse
citizen428 profile image
Michael Kohl

psql also has a config file (~/.psqlrc) that can be quite handy. Here's mine:

\set PROMPT1 '%~%x%# '
\set PROMPT2 '[more] %R > '
\set ON_ERROR_STOP on
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups
\x auto

\pset null '(null)'
\pset linestyle 'unicode'
\pset border 2
\pset unicode_border_linestyle single
\pset unicode_column_linestyle single
\pset unicode_header_linestyle double
set intervalstyle to 'postgres_verbose';
Enter fullscreen mode Exit fullscreen mode
Collapse
andrewbaisden profile image
Andrew Baisden

psql is cool but I recently learned about pgcli. Pgcli is a command line interface for Postgres with auto-completion and syntax highlighting. You should give it a go.

Collapse
geraldew profile image
geraldew

Have marked this for reference. I do think this kind of walk-through piece is what makes Dev a worthwhile site.

While most of my work has been solid SQL scripting (a la Teradata) I've also had occasion to do interspersed bash and SQL scripting. Notably for HiveQL, Impala and SparkSql but not yet for PostgreSQL. I'm sure I'll come back to re-read this sometime, so thanks for writing it.