Basic PostgreSQL Query for Beginner

The first thing you need to learn when learning PostgreSQL is how to create databases, tables, browse data, update data, and even delete data using statements. In this article, we will learn about those basic SQL queries in PostgresSQL.

Database connection

To start working with PostgreSQL database you can use interactive query shell psql or gui tools like Tableplus, Squelpro or others. But in this article we will go with psql.

I will assume you have already downloaded and installed postgres, but if not, go to this page and follow the instructions for installation.

After done with the installation you can start connect to PostgresSQL using this command.

psql -h localhost -U postgres -d postgres -p 5432

If your console showing information like this, it mean all is good we can start to run sql query there.

$ psql -h localhost -U postgres -d postgres -p 5432
Password for user postgres: 
psql (10.17)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

Create database

First thing you need to create database name, in Postgres we can have multiple database name on single server so before working with the data we need to create a database name first.

Create database

CREATE DATABASE basic_postgres;

So here's the rules that you need to remember:

  • You can't use character - when creating database name e.g.: create database basic-postgres;
  • If you want to use char - you wrap it with double quote e.g.: create database "basic-postgres";
  • You can't create database name using number e.g: create database 1234;
  • If you need a number use alphanumeric e.g.: create database project1;

More about PostgreSQL data type, read here.

Connect database

After creating the database we need to select database before we create table and play with the data, it's like creating folder then go inside that folder.

For that we can use this query.

\c basic_postgres;

If your console shows this, it means the database has already been created and now you can run sql query here.

postgres=# \c basic_postgres;
You are now connected to database "basic_postgres" as user "postgres".
basic_postgres=#

Create table

Now let's create a simple users table to store user information like email, password, email etc. You will find that you will work with this table when you are working on website that have login system in it.

CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL DEFAULT '',
    email VARCHAR(150) NOT NULL,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    CONSTRAINT users_email_key UNIQUE (email)
);

Let's explore what happen inside query above.

  • id BIGSERIAL PRIMARY KEY column id will have data type of integer and with primary key it will make this field can be connected with others table usualy will be used to query join.
  • username VARCHAR(100) NOT NULL DEFAULT '' column username will have string with max length 100 if we not specify it will be 255 by default, and we are set the default value to empty string.
  • email VARCHAR(150) NOT NULL column email can not be empty because we don't set the default value here.
  • created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() in this column we are will store a date data with the timezone for the user.

The data inside users table will look like this.

id  |  username  |     email      |   password   |         created_at
----+------------+----------------+--------------+------------------------------
 1  | ahmadrosid | ahmad@mail.com | ZZx9skKNMaYe | 2021-09-22 23:37:59.327921+07

And if you want to see list of table inside the database you can use this query.

basic_postgres=# \d+
                           List of relations
 Schema |     Name     |   Type   |  Owner   |    Size    | Description
--------+--------------+----------+----------+------------+-------------
 public | users        | table    | postgres | 8192 bytes |
(1 rows)

Insert data

INSERT INTO "public"."users" ("username", "email", "password") 
VALUES ('ahmadrosid', 'alahmadrosid@gmail.com', 'ZZx9skKNMaYe') RETURNING *;

Get data from table

SELECT * FROM users;

The result of this query

basic_postgres=# select * from users;
 id |  username  |     email      |   password   |         created_at
----+------------+----------------+--------------+------------------------------
 1  | ahmadrosid | ahmad@mail.com | ZZx9skKNMaYe | 2021-09-22 23:37:59.327921+07
(1 row)

Update data

UPDATE "public"."users" SET "username" = 'rosid' WHERE "id" = 1;

Delete data

Delete singgle row :

DELETE FROM users WHERE id = 1;

Delete multiple row :

DELETE FROM users WHERE id IN (1,2,3,4,5,6,7)

Delete table

DROP TABLE users;