SQL through Postgres — Introduction

updated for postgreSQL 13.1

A million accolade for the world’s most advanced open source RDMS.

I love Microsoft’s Windows Subsystem for Linux.

by Carlos Muza — Unsplash

Meet PostgreSQL

PostgreSQL is a RDMS that follows the client/server model. The server can be on the same host with the client or not (in which case it accesses the server through a TCP/IP connection).

Windows and Mac OS: Download and Install on Windows and Mac from EDB postgres

On Ubuntu (WSL2): To install postgreSQL (client & server) on linux, we use the below code. postgresq-contrib installs additional extensions.

sudo apt update
sudo apt install postgresql postgresql-contrib

The above install creates a user account postgres and a database postgres we can use to connect to the postgreSQL server.

We check the status of the server and start it if need be.

sudo service postgresql status

If need be start the server with

sudo service postgresql start

PostgreSQL allows us to use the createdb, dropdb, createuser etc commands with a valid user account. Thus we create a new user.

sudo -u postgres createuser --interactive

creating new user

We connect using account postgres to set password for our new user guser.

sudo -u postgres psql postgres

change user ‘guser’ password

Now we can create a new database from bash and assign guser as the owner.

sudo -u postgres createdb gdb --owner guser

We can now connect to postgreSQL server with ‘guser’ using our database.

psql -h localhost -p 5432 -U guser -d gdb

connected to guser with gdb

We can perform SQL and psql operations as shown below

example SQL and psql ops

We can exit psql using \q command.

To stop the server we use

sudo service postgresql stop