Creating Tables for PostgreSQL: A Quick Guide

Introductory to Tables

A table in PostgreSQL is a database object that can organize and store data in a structured format: in rows and columns making it easier to track and manage the data you are inputting. PostgreSQL tables allow information to flow and be quickly accessed and retrieved. in this guide, you will learn how to create tables in PostgreSQL in different ways, giving you a lot of options for managing your database.

SQL Command Line

For starters, a table can be created simply from the command line in SQL Shell by just simply running:

CREATE TABLE table_name (column_1 datatype, column_2 datatype);

and to see all the tables and if your table has been successfully created, run:

\d

with this command, you will see all the tables you have created.

‍

Like Option

Another option in the command allows you to create an empty table based on another table’s traits, from its column attributes to its indexes defined on its original table. Simply use:

CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL);

Syntax Statement

Up next we will use a SYNTAX to create tables and here is an example:

CREATE TABLE [IF NOT EXISTS] table_name (
    Col_name_1 datatype(length),
    Col_name_2 datatype(length),
    Col_name_3 datatype(length),
table_constraint
)
TABLESPACE=tablespace_name

in this SYNTAX, the table_name should be unique in the database, or else, it will return an error if another table exists with the same name. The command IF NOT EXISTS serves as a fail-safe if there was a table with the same name because, without it, it will terminate the statement.

Now let’s learn about Constraints and how you can use them on your tables.

‍

Constraints

PostgreSQL supports both table and column constraints. Constraints are rules or restrictions used for data in a table or a column. A table constraint specifies restrictions that can apply to the whole table while a column constraint will affect a single specific column.

Here are the constraints you can use in managing your tables:

• UNIQUE: this constraint ensures that the column will not contain repeated values in a statement
• NOT NULL: this will enforce a column to reject NULL values
• PRIMARY KEY: this one will indicate that the column will be used to identify uniquely a record within the table. It is important that while creating a PostgreSQL table that when you used a PRIMARY KEY constraint on a column, it can only contain unique values and it won’t accept NULL values. here’s an example:

CREATE TABLE orders ( 
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200),
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

• FOREIGN KEY: This constraint is used to relate tables to one another in a database, it comprises a group of columns in a single table that references the prime key column in another table. this will specify the value in that column must match the value in the specified table, as such database referential integrity will be maintained.
• CHECK: this constraint makes sure that the values in a specified column meet its specific requirement. CHECK uses Boolean expression to evaluate the values before being inserted into a table, if a value doesn’t pass the initial check, PostgreSQL will not insert the value and will issue a constraint violation error. Here’s an example of a CHECK constraint:

CREATE TABLE prices
( id serial PRIMARY KEY,
  product_name VARCHAR (50),
  product_description VARCHAR (50),
  price numeric CHECK(price > 0)
);

in the example above, we made sure that the price value must always be greater than 0.

‍

Viewing Created Tables

This simple command in the pg_admin schema will let you view all your existing tables so you can check them for errors:

select * from pg_tables where schemaname='table_name'

And there you have it, Now you know the different and popular ways to make a table in PostgreSQL, but you can also tap other resources like Ottomatik to secure, manage and create backups for hassle-free management of your databases making you perform tasks simpler and more efficient.