Creating PostgreSQL Tables: A Comprehensive Guide 101

PostgreSQL is a powerful object-relational database system that is free and open source. It has been in active development for more than 15 years and has a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness.

In this article, you will gain information about PostgreSQL Tables. You will also gain a holistic understanding of PostgreSQL, its key features, different methods of creating PostgreSQL Tables, and the data types, and constraints supported by PostgreSQL Tables.

Read along to find out in-depth information about creating PostgreSQL Tables.

Table of Contents

• What is PostgreSQL?
  • Key Features of PostgreSQL
• What are PostgreSQL tables?
• Methods of Creating PostgreSQL Tables
  • Creating PostgreSQL Tables: Using CREATE TABLE Command
  • Creating PostgreSQL Tables: From the Command Line
  • Creating PostgreSQL Tables: Using dbForge Studio for PostgreSQL
• Data Types used in PostgreSQL Tables
• Table Constraints in PostgreSQL Tables
• Conclusion

What is PostgreSQL?

PostgreSQL is an open-source, high-performance relational database. It is well-known for its Open-Source platform, which supports all RDBMS features. On top of RDBMS features, it also includes indexes, views, stored procedures, triggers, atomicity features, etc. It also supports both SQL and JSON queries. PostgreSQL can also be used as a data warehouse system.

PostgreSQL supports a wide variety of operating systems including Windows, Linux, macOS, UNIX, etc. PostgreSQL’s source code is freely available under an open-source license, allowing its users to use, change, and implement it as per their requirements. Since  PostgreSQL has no license fees, there is no risk of over-deployment thus saving a lot of unnecessary expenses.

Key Features of PostgreSQL

Some of the key features of PostgreSQL are as follows:

• Customizable: PostgreSQL can be customized by creating plugins to make the DBMS fit according to your requirements. It also allows you to include custom functions written in other programming languages such as Java, C, C++, and some others.
• Supportive Community: A dedicated community is always available for its users. Even a wide array of  Private, third-party support services are available for PostgreSQL. 
• Open-Source: Being a free and open-source solution,  PostgreSQL provides Object-Oriented and Relational Database features. 
• Users: It is a widely accepted and used RDBMS. PostgreSQL is used by Apple, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, etc. 
• Code quality: Every line of code that goes into PostgreSQL is reviewed by a team of experts, and the entire development process is community-driven, allowing for quick issue reporting, patches, and verification.
• Data Availability and Resiliency: Privately supported PostgreSQL versions offer extra high availability, resilience, and security for mission-critical production environments such as government agencies, financial organizations, and health& care providers.

What are PostgreSQL Tables?

A relational database table is made up of rows and columns. The number and order of the columns are fixed, and each one is labeled. The number of rows in a table varies depending on how much data is stored at any given time. SQL makes no guarantees about the order of rows in a table. Unless sorting is explicitly requested, rows in a table will appear in an unspecified order when read.

Each column in a table has its own data type. The data type limits the set of possible values that can be assigned to a column and provides semantics to the data stored in the column.

In PostgreSQL, a table is a database object that organizes and stores data in rows and columns. PostgreSQL tables make it possible to quickly access and retrieve information.

Methods of Creating PostgreSQL Tables

The different methods of creating PostgreSQL Tables are as follows:

• Creating PostgreSQL Tables: Using CREATE TABLE Command
• Creating PostgreSQL Tables: From the Command Line
• Creating PostgreSQL Tables: Using dbForge Studio for PostgreSQL

1) Creating PostgreSQL Tables: Using CREATE TABLE Command

THE CREATE TABLE command is used for creating tables in a relational database.

The syntax is as follows:

CREATE TABLE [IF NOT EXISTS] table_name (
   column1 datatype(length) column_contraint,
   column2 datatype(length) column_contraint,
   column3 datatype(length) column_contraint,
   table_constraints
);

In this syntax,

• In the above syntax, the IF NOT EXISTS operator helps to ensure that the name of the table i.e., “table_name” doesn’t exist in the database. However, if there is already a table existing with the same name, then PostgreSQL won’t let you proceed with the table creation process.
• Here column1, column2, and column3 are the name of the columns in the tables. The column names should be comma-separated along with the specified datatypes, column lengths, and the associated constraints.
• In the table_constraints you can specify the PRIMARY KEY and FOREIGN KEY.

The usage of the CREATE TABLE Command can be illustrated in the following example.

CREATE TABLE accounts (
	user_id serial PRIMARY KEY,
	username VARCHAR ( 50 ) UNIQUE NOT NULL,
	password VARCHAR ( 50 ) NOT NULL,
	email VARCHAR ( 255 ) UNIQUE NOT NULL,
	created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP
);

The CREATE TABLE command can be further used in 2 forms:

• Using the LIKE Option
• For creating a Temporary table

1) Using the LIKE Option

You can create a table by referring to the definition of an already existing table. You can also specify which column attributes and indexes you want to get reflected in the new table. And for this purpose, you can use the LIKE clause while writing the command.

In the above command, old_table_name is the already existing table and new_table_name is the new table being created in reference to old_table_name.

2) For creating a Temporary Table

You can also create temporary tables in PostgreSQL. A temporary table is basically a short-lived table. It exists until the end of the database session. PostgreSQL will automatically delete the temporary table as soon as the database session ends.

The command to create a temporary table is as follows:

CREATE TEMPORARY TABLE temp_table_name(
   column_list
);

2) Creating PostgreSQL Tables: From the Command Line

SQL Shell (psql) is a PostgreSQL command-line interface. It enables its users to enter, edit, and execute queries and statements. Users can also view the results of those queries and statements in the SQL Shell.

For creating PostgreSQL tables from the command line, you can first launch the SQL shell. Then connect to the database where you want to create the table. Then, you can write and execute the CREATE TABLE Command.

3) Creating PostgreSQL Tables: Using dbForge Studio for PostgreSQL

dbForge Studio for PostgreSQL is a graphical user interface (GUI) client and all-in-one tool for database development and management in PostgreSQL. This PostgreSQL IDE has a user-friendly interface that even allows users without extensive database knowledge to create, develop, and execute queries, as well as edit and adjust code to meet their needs.

You can easily create a table in SQL Editor with dbForge Studio for PostgreSQL and benefit greatly from automatic syntax checking, context-sensitive code completion, and execution notifications.

The steps for creating tables using dbForge Studio for PostgreSQL are as follows:

• Step 1: First, you can launch the dbForge Studio and connect to the PostgreSQL server.

• Step 2: Navigate to the Database Explorer on the left side. Right-click on the database where you want to create a table. Then, click on the “New SQL” option.

• Step 3: The SQL Editor appears on the screen. Here, you can type the CREATE TABLE command to create a table.

Data Types in PostgreSQL Tables

The data types supported by the data stored in PostgreSQL Tables are as follows:

• Boolean: It is used to declare a True or False kind of solution.
• Character
  • Character varying(n), varchar(n): specifies a variable-length character data.
  • Character(n), char(n): Fixed-length character data
  • Text: specified a variable with any length value.
• Numeric
  • Integers
    • smallint: includes number ranging from -32768 to +32767. Its storage takes two bytes.
    • integer: includes numbers ranging from -2147483648 to +2147483647. Its storage takes four bytes.
    • bigint:  includes numbers ranging from -2^63 (-9223372036854775808) to 2^63 – 1(9223372036854775807). Its storage takes eight bytes.
    • SmallSerial: You can easily auto-increment integer range from 1 to 32767.
    • Serial: You can easily auto-increment integer range from 1 to 2147483647.
    • Bigserial: You can easily auto-increment integer range from 1 to 9223372036854775807.
  • Floating-point number
    • Decimal and numeric: The user needs to specify the precision value
    • Real and double precision: The precision value can be variable
  • Geometric Types: Point, line, path, lseg, circle, and polygon
  • Network Address Types: inet, cidr, macaddr

For further information on data types supported by PostgreSQL Tables, you can visit here.

Table Constraints in PostgreSQL Tables

Table constraints are a set of rules that the data in PostgreSQL tables should comply with. These are used to filter and limit the kind of data that can go into PostgreSQL tables. For example, if you want a specific column to have any NULL values, then you can set a NOT NULL constraint for that column.

In this way, the constraints ensure the reliability and accuracy of the data in the PostgreSQL tables. The action is aborted if there is a conflict between the constraint and the data action.

• CHECK Constraint
• UNIQUE Constraint
• NOT NULL Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint

1) CHECK Constraint

The CHECK Constraint checks for a certain boolean expression for any particular column. This constraint inserts and updates the data in that column if the expression evaluates to be true.

For example, the following query uses the CHECK constraints:

• Employee birthdates should be greater than 1980-01-01
• Employee salary cannot be less than or equal to zero

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
"Name" VARCHAR (50),
birthdate DATE CHECK (birthdate > '1980-01-01'),
salary numeric CHECK(salary > 0)
);

2) UNIQUE Constraint

The UNIQUE constraint ensures that all the values in a column are unique across all rows in a table.

For example, two employees in an organization might have similar names, but their email IDs should be unique. Here, to ensure that the email address is unique, a UNIQUE constraint is applied to the email column.

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
"Name" VARCHAR (50),
email varchar(100) UNIQUE
);

3) NOT NULL Constraint 

By default, a column in a table can hold NULL values. You can control the behavior of any column whether it can accept NULL values or not using the NOT NULL constraint. This constraint ensures that a field would always have to contain a value. Because of this constraint, any query that will try to insert NULL into the specified column will be rejected.

For example, in this query, the UNIQUE constraint is applied to the email field. This ensures that the no NULL values will be accommodated in this column.

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
"Name" VARCHAR (50) NOT NULL,
email varchar(100) UNIQUE
);

4) PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. You can have only one primary key in a table; however, it might span across multiple columns. In simple terms, you can apply a single primary key (applied to cone column) or a composite primary key (applied to multiple columns). 

For example, the PRIMARY KEY constraint is applied to the id field in the employees table.

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
"Name" VARCHAR (50) NOT NULL,
email varchar(100) UNIQUE
);

Even you can have a composite PRIMARY KEY to uniquely identify each row in a table. Here, the coach number and seat number are both used to uniquely identify each passenger.

CREATE TABLE Passengers (
COACH NUMBER INT PRIMARY KEY,
SEAT NUMBER INT,
"Name" VARCHAR (50) NOT NULL,
email varchar(100) UNIQUE,
PRIMARY KEY(COACH NUMBER, SEAT NUMBER)
);

5) FOREIGN KEY Constraint

A FOREIGN KEY links 2 tables in a particular database. It is basically a reference to the PRIMARY KEY of another table. In simple terms, FOREIGN KEY is a field or a collection of fields that refer to the PRIMARY KEY of another table.

It prevents any actions that would otherwise destroy the existing links between tables. 

The table containing the foreign key is referred to as the child table, while the table containing the primary key is referred to as the referenced or parent table.

In the following query, the department table is a child table for the employees table (parent table). In the child table, the foreign key is the id column.

Create table departments
(
dept_id SERIAL PRIMARY KEY,
id serial,
departmentname varchar(50),
CONSTRAINT fk_customer
FOREIGN KEY(id) REFERENCES employees(id)
)

6) DEFAULT Constraint

You can use the DEFAULT constraint to set a default value for a column. In case no other value is specified for any record of that particular column, then the default value will fill that place.

In the following example, the default value for the City field is set as “Bangalore”.

CREATE TABLE Persons (
	ID int NOT NULL,
	LastName varchar(255) NOT NULL,
	FirstName varchar(255),
	Age int,
	City varchar(255) DEFAULT ‘Bangalore’
);

Conclusion

In this article, you learned about PostgreSQL Tables. This article also focused on PostgreSQL, its key features, different methods of creating PostgreSQL Tables, and the datatypes, and constraints supported by PostgreSQL Tables.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 100+ Data Sources (including 40+ Free Sources) such as PostgreSQL allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows the integration of data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding about creating PostgreSQL Tables in the comment section below! We would love to hear your thoughts.