[For Ubuntu Users] Comprehensive Guide to PostgreSQL Commands – From Installation to Detailed Explanation –

While I have written about this topic for Mac users before, this version has been updated for Ubuntu users (specifically, version 20.04).

For Mac Users

https://zero-cheese.com/en/11211/

Through this guide, you will be able to learn all the basic knowledge you need from the installation of PostgreSQL to the use of the visual operation tool “pgAdmin”.

Installation Method

How to Install PostgreSQL:

The easiest method is to use ‘apt’.

Open the terminal and enter the following commands:

sudo apt update

sudo apt install postgresql postgresql-contrib

The ‘postgresql-contrib’ in the above code is a package with extended features.

It’s very useful, so I recommend installing it along with PostgreSQL.

How to Install pgAdmin:

Next, install “pgAdmin,” which allows you to operate visually (optional).

I will describe how to use it in detail later.

Here also, we will use ‘apt’.

First, obtain the repository key and create a configuration file:

# Get repository key
sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add

# Create configuration file
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'

# apt update
apt update

Then, install it with the following command (Both desktop and web versions will be installed):

sudo apt install pgadmin4

You will see “pgAdmin4” with the following icon in your software list.

PostgreSQL Initialization

No initialization is necessary.

By default, a user account named “postgres” is created.

Starting and Stopping PostgreSQL

After installation, the service is initially started.

# Start
sudo service postgresql start

# Stop
sudo service postgresql stop

# Check Status
sudo service postgresql status

Database Operation Method (Command)

sudo -i -u postgres

Command to Check the List of Databases

This is a command to check the created databases.

In the initial state of ‘PostgreSQL’, the following is displayed.

psql -l

# Here is an example execution result
                               List of databases
    Name    |   Owner   | Encoding | Collate | Ctype |    Access privileges    
------------+-----------+----------+---------+-------+-------------------------
 example-db | ***       | UTF8     | C       | C     | 
 postgres   | ***       | UTF8     | C       | C     | 
 template0  | ***       | UTF8     | C       | C     | =c/***      +
            |           |          |         |       | ***=CTc/***
 template1  | ***       | UTF8     | C       | C     | ***=CTc/***+
            |           |          |         |       | =c/***
 test       | ***       | UTF8     | C       | C     | 

Database Creation, Deletion Command

# Create a new database
createdb database_name

# Delete a database
dropdb database_name

Table Related Commands

Command to enter SQL input state

psql -h hostname -p port_number -U role_name -d database_name

# If under the following conditions, you only need to input the database name
# (If using the current username, host: localhost Port: postgreSQL default port) 
psql database_name

# ⬆ Execution result (if you specified 'test' as the database name)
psql (13.1)
Type "help" for help.

test=# 
#  ⬆　Now you can input SQL

Command to Exit SQL Input State

\q   # Do not append a semicolon at the end
# Shortcut ctrl−d is also OK

Commands to Check the List of Tables, and View Column Information of a Table

# Check the list of tables
\dt;  # It's OK without a semicolon at the end
# ⬆ Execution result (example)
          List of relations
 Schema |  Name   | Type  |   Owner   
--------+---------+-------+-----------
 public | mytable | table | ********



# View column information of a table
\d (table_name);   # e.g., \d mytable
# ⬆ Execution result (example)
                Table "public.mytable"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null | 
 name   | character varying(10) |           |          | 
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (id)

Table CRUD (Create, Read, Update, Delete)

These are standard SQL statements.

Here are some examples for your reference.

# Create a table (table name, column names are just examples.)
CREATE TABLE mytable (
  id integer PRIMARY KEY, 
  name varchar(10)
);

# Delete a table
DROP table mytable;

# Read all data
SELECT * FROM mytable;

# Insert
INSERT INTO mytable VALUES (2, 'Nakagawa');

# Update
UPDATE mytable SET name = 'Asai' where id = 2;

# Delete
DELETE FROM mytable; # ← this case, all data will be deleted

Managing Roles (Who has what permissions to access which tables)

The following commands are executed after entering the SQL input state.

Command to check the list of roles

\du;

# ⬆　Execution result
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 taro        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 jiro        |                                                            | {}
 saburo      | Superuser                                                  | {}

Command to check permissions for each role

You can assign permissions for each table as follows.

\dp;

# ⬆ Execution result
                                   Access privileges
 Schema |  Name   | Type  |      Access privileges      | Column privileges | Policies 
--------+---------+-------+-----------------------------+-------------------+----------
 public | mytable | table | taro=arwdDxt/taro+          |                   | 
        |         |       | jiro=arwdDxt/taro           |                   | 

Access privileges の記号
a: INSERT(add)
r: SELECT(read)
w: UPDATE(write)
d: DELETE
D: TRUNCATE
x: REFERENCES

Command to create a new role

# For a general user
CREATE ROLE username LOGIN PASSWORD 'password';
# ⬆ Please enter username: user name, password: password.
# ⬆ If LOGIN is not entered, you will not be able to log in.

# Login rights (you can set it later)
CREATE ROLE testuser PASSWORD 'password';

# Set a password (you can set it later)
CREATE ROLE testuser;

# For a superuser
CREATE ROLE username superuser;
# ⬆ LOGIN is not attached, so even though it's a superuser, you can't log in...

Command to assign a password and LOGIN rights to a created role

# Set a password
ALTER ROLE username PASSWORD 'password';

# Grant login rights
ALTER ROLE username LOGIN;

Command to grant table operation permissions to a role

# If granting individual permissions
GRANT permissions ON tablename TO username;
# ⬆　Enter tablename: table name, username: user name
# ⬆　Permissions can be chosen from:
SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、
CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE

# If granting all permissions
GRANT all ON tablename TO username;

Command to revoke table operation permissions from a role

REVOKE permissions ON tablename FROM username;

Command to delete a role

DROP ROLE username;

How to operate ‘pgAdmin’, which can be operated visually

DB Connection Settings

When you start it, the following screen will appear.

Right-click on [Servers], click on [Create] → [Server…].

The following dialog will come up.

Name is optional.

Next, open the Connection tab.

With this, you will see the following tree. (A list of DBs will be displayed.)

How to View and Change Table Data

There is a list of tables under DB→Schemas→public→Tables. (The following figure is an example.)

If you want to see the table data, right-click on the table→view/Edit Data→Select All Rows.

The following screen will appear.

From here, you can change or insert values.

Finally

If you have an ORM, it should be OK (laughs).