PostgreSQL Naming Conventions

Naming is very important because it helps other developers interact with your database to have an easy time reading and understanding the database components. This tutorial will discuss the naming conventions that we can use to name databases, tables, sequences, primary keys, constraints, and indexes.

PostgreSQL Naming Conventions

According to PostgreSQL documentation there is no defined standard for the naming convention, but as long as you are conversant with identifier naming rules, you can use any naming approach you find appropriate.

Use the following command to log in to your PostgreSQL server.

david@david-HP-ProBook-6470b:~$ psql -U postgres

Create a database that we will use to test the naming conventions.

postgres=# create database NAMING_CONVENTION_DB;
CREATE DATABASE

Note that even though we used the upper case to name the database, the database name will get converted to lowercase by default. To verify this, use the \l command to list the databases in the PostgreSQL server.

postgres=# \l

Output:

List of databases
Name         |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
----------------------+----------+----------+-------------+-------------+-----------------------
naming_convention_db | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
postgres             | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
template0            | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
template1            | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
(4 rows)

The table returned shows the different databases created in the PostgreSQL server and their users. The database we have just created is named naming_convention_db.

Use the following command to connect to the naming_convention_db database, which will ensure our queries are executed on the right database.

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

The UPPER_CASE naming convention is the most popular method used by developers to name keywords.

naming_convention_db=# CREATE TABLE employee(
naming_convention_db(# id SERIAL,
naming_convention_db(# first_name VARCHAR(30),
naming_convention_db(# last_name VARCHAR(30),
naming_convention_db(# email VARCHAR(50),
naming_convention_db(# PRIMARY KEY(id));
CREATE TABLE

In the above example, we have the following keywords: CREATE, TABLE, SERIAL, VARCHAR, and PRIMARY KEY. Note that the words are capitalized since they denote keywords for a particular function in the database management system.

The lower_case_with_underscore naming convention is the most popular method used by developers to name identifiers.

naming_convention_db=# CREATE SEQUENCE employee_sequence
naming_convention_db-# INCREMENT 5
naming_convention_db-# START 10;
CREATE SEQUENCE

We have created an identifier for a SEQUENCE using lower case characters and an underscore identified by employee_underscore in the above example. If you want your identifiers to be case-sensitive, you can use double quotes when naming the identifiers.

naming_convention_db=# CREATE TABLE "EMPLOYEE"(
naming_convention_db(# first_name VARCHAR(30),
naming_convention_db(# last_name VARCHAR(30),
naming_convention_db(# email VARCHAR(50),
naming_convention_db(# id SERIAL,
naming_convention_db(# PRIMARY KEY(id));
CREATE TABLE

The above example creates another employee table in our database with an upper case identifier named EMPLOYEE.

Use the command below to view all the tables in our database.

naming_convention_db=# \dt

Output:

List of relations
Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
public | EMPLOYEE | table | postgres
public | employee | table | postgres
(2 rows)

If you do not quote an identifier, it is saved in lowercase by default. Hence the names Employee, EMPLOYEE, and EmPlOyEe are the same.

This means that the identifiers are case insensitive when they are not quoted. We should also avoid using quoted identifiers with the same name as keywords to ensure there are no syntactic errors.

For example, the following data definition language should be avoided as it can bring abnormality in our queries.

naming_convention_db=# CREATE SEQUENCE "serial"
naming_convention_db-# INCREMENT 5
naming_convention_db-# START 10;

SERIAL is a keyword that interprets a different functionality in the database, and using quoted identifiers to create another serial property could pose potential errors.