Postgresql Guide – Everything you Should Know

In this blog, we are going to discuss how to install PostgreSQL, verify the installation, start the database server, create database, create basic table, insert data into the table, see list of databases, see list of tables, see list of users, login as particular user, run sql query on table etc. Basically this blog will help you with PostgreSQL learning. We will discuss only basics. We will keep it simple to help you learn the basics.

First install Postgresql

Installing Postgresql on Linux ( Ubuntu/Debian)

You can run the following command to install PostgreSQL on Ubuntu/Debian based Linus operating system.

sudo apt-get update
sudo apt-get install postgresql

Installing Postgresql on Linux (CentOS/RHEL):

You can follow the instructions on https://www.postgresql.org/download/linux/redhat/ , you can enter details of your operating system and they will give you corresponding commands to run.

Installing Postgresql on macOS

For macOS, you can use the Homebrew package manager to install PostgreSQL.

brew install postgresql

Installing Postgresql on windows

On Windows, you can download the PostgreSQL installer from the official website and follow the installation process: PostgreSQL Downloads for Windows

Add path to environment variable

To log in to PostgreSQL from anywhere in system path, make sure to add PostgreSQL installation path C:\Program Files\PostgreSQL\15\bin to path environment variable. replace 15 with first two digit of version of your installation . This will make it lot easier to log in to PostgreSQL .

Verifying installation

Once installed, you can verify the installation by running psql --version command in the terminal.

Starting the Database Server:

After installation, the PostgreSQL database server may not start automatically. You can start it manually using the following command:

On Linux you can start by running the command sudo service postgresql start

On macOS you can start by running the command brew services start postgresql

On windows you can start the PostgreSQL service using the Services application(search for services in windows search) or by running this below command in the command prompt:

pg_ctl -D "C:\Program Files\PostgreSQL\<version>\data" start

Replace <version> with your PostgreSQL version number. In my case, it was version 15.3 , so command will be pg_ctl -D "C:\Program Files\PostgreSQL\15\data" start , you need to use first two digits of version.

Set pgpass.conf File for auto Login

Go inside the directory C:\Users\codingissimple\AppData\Roaming\postgresql\pgpass.conf , add below line in the file pgpass.conf (create the file if not found)

localhost:5432:postgres:postgres:password

Replace password with actual password for user postgres that you have set at the time of installation.

Database in Postgresql

Create database from command line

To create database from command line run command createdb -U postgres -h localhost new_database , this will create new_database for user postgres .

C:\Users\codingissimple>createdb -U postgres -h localhost new_database

If you have user codingissimple user is available on postgreSQL then you can simply run createdb new_database, this will create new_database for user codingissimple .i.e you need to have the same user in database as your windows user for it to work.

You can also use command psql -U postgres -c "CREATE DATABASE new_database;" to create the database from command line. -c allows to run sql command.

C:\Users\codingissimple>psql -U postgres -c "CREATE DATABASE mydatabase;"
CREATE DATABASE

Login in as User postgres in Postgresql

To login as postgres run the command psql -U postgres

C:\Users\codingissimple>psql -U postgres
psql (15.3)
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=#

You can login is different user using the same command, just replace postgres with the username. But as you are just starting you won’t have other users right now. We will come back to it again later in this blog.

View list of databases

To view list of all database, run the command \l , you will see the new_database in the list that we had created previously.

Connect to database

To connect to any database, run the command \c database_name , to change back to postgres you can again run \c postgres . Let’s try to connect to new_database that we had created previously.

postgres-# \c new_database
Password for user postgres:
You are now connected to database "new_database" as user "postgres".
new_database-# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#

Exiting psql

To exit the psql (PostgreSQL Command-Line Client), press ctrl + c or run the command \q ,

Deleting the database from command line

To delete the database from command line, run the command dropdb -U postgres -h localhost new_database, to verify if new_database is deleted you can login and list the databases again.

postgres=# \q

C:\Users\codingissimple>dropdb -U postgres -h localhost new_database

C:\Users\codingissimple>psql -U postgres
psql (15.3)
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=# \l

Creating database from inside psql using sql command

To create database from inside psql , run the command CREATE DATABASE new_database; you can verify by listing all the database again.

postgres=# CREATE DATABASE new_database;
CREATE DATABASE
postgres=# \l

Deleting database from inside psql using sql command

postgres=# DROP DATABASE new_database;
DROP DATABASE
postgres=# \l

Users of Postgresql database

Similar to creating and deleting database it can be done two ways i.e one from command line and other by using sql command from inside psql.

Creating new user using command line

To create user from command line without setting any password , you can run the command createuser -U postgres -h localhost codingissimple , you can replace codingissimple with username you want to create.

To create user from command line with password , run the command createuser -U postgres -h localhost -P codingissimple , it will prompt you to enter password

C:\Users\codingissimple>createuser -U postgres -h localhost codingissimple
C:\Users\codingissimple>createuser -U postgres -h localhost -P other_user_with_pwd
Enter password for new role:
Enter it again:

Creating new user using sql command

To create new user using sql command run the command CREATE USER some_user WITH PASSWORD 'password'; you can replace some_user and password with your desired value.

C:\Users\codingissimple>psql -U postgres
psql (15.3)
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 USER some_user WITH PASSWORD 'password';
CREATE ROLE

View list of users

To see list of all users run the command SELECT usename FROM pg_user;

postgres=# SELECT usename FROM pg_user;
       usename
---------------------
 postgres
 codingissimple
 other_user_with_pwd
 some_user
(4 rows)

You can also use \du command to see list of users and their roles.

postgres=# \du
                                        List of roles
      Role name      |                         Attributes                         | Member of
---------------------+------------------------------------------------------------+-----------
 codingissimple      |                                                            | {}
 other_user_with_pwd |                                                            | {}
 postgres            | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 some_user           |                                                            | {}

Change password for a user

To change password for a user run the command ALTER USER username_here PASSWORD 'new_password_here'; For example, to change password for a user some_user we can run the command ALTER USER some_user PASSWORD 'new_password';

postgres=# ALTER USER some_user PASSWORD 'new_password';
ALTER ROLE
postgres=# \q

Login to psql as user other than postgres

If you try to login by using the command psql -U some_user , you will get error. You will have to specify database as well , run the command psql -U some_user -d postgres to login to database postgres as user some_user. You can replace it with username of your choice.

C:\Users\codingissimple>psql -U some_user
Password for user some_user:
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  database "some_user" does not exist

C:\Users\codingissimple>psql -U some_user -d postgres
Password for user some_user:
psql (15.3)
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.

See the current user

To see which user has connected currently run the command SELECT current_user; to see additional information you can run the command SELECT current_user, current_database(), inet_client_addr(), inet_client_port();

postgres=> SELECT current_user;
 current_user
--------------
 some_user
(1 row)
postgres=> SELECT current_user, current_database(), inet_client_addr(), inet_client_port();
 current_user | current_database | inet_client_addr | inet_client_port
--------------+------------------+------------------+------------------
 some_user    | postgres         | ::1              |            56134(1 row)

Delete user

You can delete a user via command line or by sql. To delete using command line run the command dropuser -U postgres -h localhost some_user

To delete using sql command, first login as user postgres then run the sql command DROP USER other_user_with_pwd;

C:\Users\codingissimple>dropuser -U postgres -h localhost some_user

C:\Users\codingissimple>psql -U postgres
psql (15.3)
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=# SELECT usename FROM pg_user;
       usename
---------------------
 postgres
 codingissimple
 other_user_with_pwd
(3 rows)


postgres=# DROP USER other_user_with_pwd;
DROP ROLE
postgres=# SELECT usename FROM pg_user;
    usename
----------------
 postgres
 codingissimple
(2 rows)

Tables in Postgresql database

Creating a table inside database

To create a table inside database first switch the database into which you want to create the table. Next, run the below command to create users table with columns id, name, age . Depending on table column’s data type you need to choose appropriate data type value. To learn more on data types visit https://www.postgresql.org/docs/15/datatype.html , I have also provided various tables to help you understand different data types.

CREATE TABLE users (
    id serial PRIMARY KEY,
    name VARCHAR (255),
    age INT
);
--Employee Information Table:
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birthdate DATE,
    hire_date DATE,
    salary DECIMAL(10, 2),
    is_active BOOLEAN,
    address TEXT,
    email VARCHAR(100),
    phone_number VARCHAR(15),
    profile_picture BYTEA
);
--Product Catalog Table:
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    description TEXT,
    price DECIMAL(10, 2),
    stock_quantity INT,
    is_available BOOLEAN,
    manufacturer VARCHAR(50),
    product_image BYTEA
);
--Orders Table:
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP,
    customer_name VARCHAR(100),
    total_amount MONEY,
    is_paid BOOLEAN,
    shipping_address JSONB,
    order_items JSONB[]
);
--Time Tracking Table:
CREATE TABLE time_tracking (
    entry_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100),
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    duration INTERVAL,
    notes TEXT
);

See list of tables inside database

once you create users table you can see the list of tables using the command \dt

postgres=# \c new_database
You are now connected to database "new_database" as user "postgres".
new_database=# CREATE TABLE users (
new_database(#     id serial PRIMARY KEY,
new_database(#     name VARCHAR (255),
new_database(#     age INT
new_database(# );
CREATE TABLE
new_database=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | employees     | table | postgres
 public | orders        | table | postgres
 public | products      | table | postgres
 public | time_tracking | table | postgres
 public | users         | table | postgres
(5 rows)

Insert data into the table

To insert data into the users table run the command INSERT INTO users (name, age) VALUES ('John', 30); lets add two more rows to the table.

new_database=# INSERT INTO users (name, age) VALUES ('John', 30);
INSERT 0 1
new_database=# INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT 0 1
new_database=# INSERT INTO users (name, age) VALUES ('Bob', 35);
INSERT 0 1
  • 0: means that no existing rows were updated or deleted by the INSERT statement.
  • 1: indicating that one new row was inserted into the table.

View the particular table

To view the table data run the command SELECT * FROM users; here * means all columns will be selected from the table.

new_database=# SELECT * FROM users;
 id | name  | age
----+-------+-----
  1 | John  |  30
  2 | Alice |  25
  3 | Bob   |  35
(3 rows)

Run an sql command on the table

You can run any sql command on the table, for example to select users having age more than 25 years , you can run the command SELECT * FROM users WHERE age>25;

new_database=# SELECT * FROM users WHERE age>25;
 id | name | age
----+------+-----
  1 | John |  30
  3 | Bob  |  35
(2 rows)

Update the table

In users table John’s age right now is 30 and later you came to know it was wrongly entered and want to update the age to 40 for John. You can run the command UPDATE users SET age = 40 WHERE name = 'John';


new_database=# UPDATE users
 SET age = 40 WHERE name = 'John';

UPDATE 1
new_database=# SELECT * FROM users;
 id | name  | age
----+-------+-----
  2 | Alice |  25
  3 | Bob   |  35
  1 | John  |  40
(3 rows)

Delete the table

To delete the table, you can run the command DROP TABLE table_name; replace table_name with table you want to delete. For example to delete the users table

new_database=# DROP TABLE users;
DROP TABLE
new_database=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | employees     | table | postgres
 public | orders        | table | postgres
 public | products      | table | postgres
 public | time_tracking | table | postgres
(4 rows)

In this blog you have learned creating and deleting database in postgresql, creating and deleting users, creating and deleting tables, inserting data into the table , updating data of the table, executing sql command on the table etc.

Don't Miss Out! Subscribe to Read Our Latest Blogs.

If you found this blog helpful, share it on social media.

Subscription form (#5)

Leave a Comment

Your email address will not be published. Required fields are marked *

Pin It on Pinterest

Scroll to Top