PostgreSQL Survival Guide

PostgreSQL has been gaining a lot of popularity these days, so let’s have a look at the basics of administering a PostgreSQL database. We’ll cover the structure exploration, object manipulation and some user managment.

Quick remainder of some mainly used PostgreSQL datatypes:

Name	Storage size	Description
smallint	2 octets	Small integer
integer	4 octets	Integer
bigint	8 octets	Big Integer
decimal	variable	Precision decimal
numeric	variable	Precision decimal
real	4 octets	6 decimals precision number
double precision	8 octets	15 decimals precision number
smallserial	2 bytes	2 octets integer with auto-increment
serial	4 octets	Integer with auto-increment
bigserial	8 octets	Big integer with auto-increment

List all users

sam=# Select rolname, rolcanlogin, rolpassword, rolvaliduntil from pg_roles;
 rolname | rolcanlogin | rolpassword | rolvaliduntil
---------+-------------+-------------+---------------
 sam     | t           | ********    |
 cerbere | t           | ********    |
(2 rows)

sam=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 my_user   | Superuser, Create DB                           | {}
 sam       | Superuser, Create role, Create DB, Replication | {}

List all databases

sam-# \l
                                List of databases
    Name    |  Owner  | Encoding |   Collate   |    Ctype    | Access privileges
------------+---------+----------+-------------+-------------+-------------------
 my_user_db | sam     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres   | sam     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sam        | sam     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0  | sam     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/sam           +
            |         |          |             |             | sam=CTc/sam
 template1  | sam     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/sam           +
            |         |          |             |             | sam=CTc/sam

Create a database

sam=# Create database some_db;
CREATE DATABASE

Connect to a database

sam=# \c some_db;
You are now connected to database "some_db" as user "sam".

List all tables

some_db=# \dt
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | test | table | sam

Create a table with a PK index and describe structure

some_db=# Create table test (test_col INTEGER PRIMARY KEY);
CREATE TABLE
some_db=# \dp
                          Access privileges
 Schema | Name | Type  | Access privileges | Column access privileges
--------+------+-------+-------------------+--------------------------
 public | test | table |                   |
(1 row)

some_db=# \di
             List of relations
 Schema |   Name    | Type  | Owner | Table
--------+-----------+-------+-------+-------
 public | test_pkey | index | sam   | test
 (1 row)

 some_db=# \d test
       Table "public.test"
   Column  |  Type   | Modifiers
 ----------+---------+-----------
  test_col | integer | not null
 Indexes:
     "test_pkey" PRIMARY KEY, btree (test_col)

Note that the maximum name length for a table name is 64, which is much more comfortable than the 30 character limit in Oracle.

SQL operations, explain plan and quit

sam=# Select * from produits;
 no_produit |   nom   | prix
------------+---------+------
          1 | Fromage | 9.99
          1 | Fromage | 9.99
          1 | Fromage | 9.99
          1 | Fromage |     
          1 | Fromage |     
          1 | Fromage | 9.99
          2 | Pain    | 1.99
          3 | Lait    | 2.99
(8 rows)

sam=# Explain select nom, prix from produits where prix > 5;
                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on produits  (cost=0.00..1.10 rows=3 width=64)
   Filter: (prix > 5::numeric)

sam=# \q

SQL operations are quite familiar if you already know your way on an Oracle or a MySQL database.

Anyway, if you’re stuck you can use \? for psql help or \h for a complete in-depth command-line help.

Written on January 20th, 2016 by Samy Gejzenblozen

Tags:

#postgresql #database

Social networks

You may also enjoy:

Rename a Postgresql database

#database #postgresql

Here's the procedure to rename a PostgreSQL database: 1. Disconnect from the database that you want to rename and connect to a different database. 2. Check and terminate all active connections to the database that you want to rename. 3. Use the `ALTER DATABASE` statement to rename the database to the new one. Let’s take a look at an example of... Read more

20 May 2019 - 1 minute read

How to duplicate a Postgresql database

#database #postgresql

If you need to duplicate an existing Postgresql database, and possibly transfer ownership of the database objects to a new user, here's how to do that in a quick way: ```sql -- First, I recommand getting the size of the database to copy, as this might be important for the rest of the process. SELECT pg_database.datname,pg_size_pretty(pg_databas... Read more

20 Apr 2019 - less than 1 minute read

Change default schema on Postgresql

#database #postgresql

In PostgreSQL, users can have many namespaces to resolve objects names. These are called schemas like in Oracle, and can be altered through the `search_path` variable. Here's how to check current search path: ```sql SHOW search_path; /* Result search_path ------------------ "$user", public */ ``` Usually it defaults to the **username** and **... Read more

20 Jan 2018 - less than 1 minute read

Rename a Postgresql database

#database #postgresql

20 May 2019 - 1 minute read

How to duplicate a Postgresql database

#database #postgresql

20 Apr 2019 - less than 1 minute read

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read