Skip to content

Naming Conventions

When you design your database schema in SQL Server, it's common to name your tables and columns using pascal casing. For example, in SQL Server we may have a table like so:

Table NameProduct
ColumnsId
Name
Price
IsDeleted
CategoryId
CreatedByUser
ModifiedByUser

PostgreSQL stores all table and columns (that are not in double quotes) in lowercase, so the above would be stored as product rather than Product, if you run a select with uppercase against Postgres, the query will fail saying the column doesn’t exist. Thus, the Postgres convention for tables and columns, is to name everything lowercase with under scores. The above would become:

Table Nameproduct
Columnsid
name
price
is_deleted
category_id
created_by_user
modified_by_user

While it is possible to use the convention from SQL Server, if you're looking at the table and column information from the database you will find it is stored in lowercase, this often makes it harder to read later.

For example, if we created a table in Postgres, the same as we would in SQL Server.

create table if not exists Product (
    Id serial,
    Name text,
    Price money,
    IsDeleted bool,
    CategoryId int,
    CreatedByUser int,
    ModifiedByUser int
);

When we query the table definition:

select *
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'product';

You can see from the screen grab that the table and columns are stored lowercase.

The screenshot of the table and columns