Creating PostgreSQL Tables: Syntax and Examples

a explorer robot is developing a tutorial with the title creating postgresql tables syntax and examples in a campus

This blog teaches you how to create tables in PostgreSQL using the CREATE TABLE statement. You will learn the syntax, data types, and constraints for defining tables.

1. Introduction

In this tutorial, you will learn how to create tables in PostgreSQL using the CREATE TABLE statement. Tables are the basic units of data storage in PostgreSQL, where you can store and manipulate your data. Creating tables is one of the most common tasks you will perform as a PostgreSQL user or developer.

To create tables in PostgreSQL, you need to know the following concepts:

  • PostgreSQL CREATE TABLE syntax: The syntax of the CREATE TABLE statement defines the structure and properties of the table, such as the table name, column names, data types, and constraints.
  • PostgreSQL data types: Data types specify the kind of data that can be stored in each column of the table. PostgreSQL supports a variety of data types, such as numeric, character, date and time, and more.
  • PostgreSQL CREATE TABLE examples: Examples of how to use the CREATE TABLE statement to create different kinds of tables, such as simple tables, tables with constraints, and tables from another table.

By the end of this tutorial, you will be able to create tables in PostgreSQL using the CREATE TABLE statement. You will also learn how to use different data types and constraints to define your tables. You will also see some practical examples of creating tables for different purposes.

Are you ready to start creating tables in PostgreSQL? Let’s begin with the PostgreSQL CREATE TABLE syntax.

2. PostgreSQL CREATE TABLE Syntax

The CREATE TABLE statement is used to create a new table in PostgreSQL. The basic syntax of the CREATE TABLE statement is as follows:

CREATE TABLE table_name (
    column1 data_type constraint,
    column2 data_type constraint,
    ...
);

The CREATE TABLE statement consists of the following parts:

  • The table_name is the name of the table that you want to create. You can choose any name that is valid in PostgreSQL, but it is recommended to follow some naming conventions, such as using lowercase letters and underscores.
  • The column1, column2, … are the names of the columns that you want to create in the table. You can have as many columns as you need, but each column must have a unique name within the table.
  • The data_type is the type of data that can be stored in each column. PostgreSQL supports many data types, such as numeric, character, date and time, and more. You will learn more about PostgreSQL data types in the next section.
  • The constraint is an optional parameter that defines some rules or restrictions on the values that can be stored in each column. PostgreSQL supports many types of constraints, such as primary key, foreign key, unique, not null, check, and more. You will learn more about PostgreSQL constraints in the later section.

The CREATE TABLE statement ends with a semicolon (;) to indicate the end of the statement.

How do you use the CREATE TABLE statement to create a table in PostgreSQL? Let’s see some examples in the next section.

2.1. Basic Syntax

In this section, you will learn the basic syntax of the CREATE TABLE statement in PostgreSQL. The basic syntax is the simplest form of the statement that allows you to create a table with the minimum information. You can use the basic syntax to create a table with a name and one or more columns, each with a name and a data type.

The basic syntax of the CREATE TABLE statement is as follows:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
);

Let’s break down the basic syntax of the CREATE TABLE statement:

  • The CREATE TABLE keywords indicate that you want to create a new table in PostgreSQL.
  • The table_name is the name of the table that you want to create. You can choose any name that is valid in PostgreSQL, but it is recommended to follow some naming conventions, such as using lowercase letters and underscores.
  • The column1, column2, … are the names of the columns that you want to create in the table. You can have as many columns as you need, but each column must have a unique name within the table.
  • The data_type is the type of data that can be stored in each column. PostgreSQL supports many data types, such as numeric, character, date and time, and more. You will learn more about PostgreSQL data types in the next section.
  • The parentheses (( and )) enclose the list of columns and their data types.
  • The semicolon (;) indicates the end of the statement.

The basic syntax of the CREATE TABLE statement does not include any constraints on the columns or the table. Constraints are optional parameters that define some rules or restrictions on the values that can be stored in the table. You will learn more about PostgreSQL constraints in the later section.

How do you use the basic syntax of the CREATE TABLE statement to create a table in PostgreSQL? Let’s see an example in the next section.

2.2. Column Definition

In this section, you will learn how to define the columns of a table in PostgreSQL using the CREATE TABLE statement. The column definition is the part of the statement that specifies the name, data type, and optional constraints of each column in the table. You can use the column definition to create columns with different characteristics and properties, such as size, precision, default value, and more.

The column definition of the CREATE TABLE statement has the following syntax:

column_name data_type [constraint] [default expr]

Let’s break down the column definition of the CREATE TABLE statement:

  • The column_name is the name of the column that you want to create in the table. You can choose any name that is valid in PostgreSQL, but it is recommended to follow some naming conventions, such as using lowercase letters and underscores.
  • The data_type is the type of data that can be stored in the column. PostgreSQL supports many data types, such as numeric, character, date and time, and more. You will learn more about PostgreSQL data types in the next section.
  • The constraint is an optional parameter that defines some rules or restrictions on the values that can be stored in the column. PostgreSQL supports many types of constraints, such as primary key, foreign key, unique, not null, check, and more. You will learn more about PostgreSQL constraints in the later section.
  • The default expr is an optional parameter that specifies a default value for the column. You can use any expression that evaluates to a constant value, such as a literal, a function, or a variable. If you do not specify a default value, the column will have a null value by default.

The column definition can have more than one constraint and/or default value, separated by spaces. For example, you can create a column that has a primary key constraint, a not null constraint, and a default value of 1.

How do you use the column definition of the CREATE TABLE statement to create columns with different characteristics and properties in PostgreSQL? Let’s see some examples in the next section.

2.3. Table Constraints

In this section, you will learn how to define the table constraints in PostgreSQL using the CREATE TABLE statement. The table constraints are the rules or restrictions that apply to the whole table, rather than to individual columns. You can use the table constraints to enforce the integrity and consistency of the data in the table, such as ensuring uniqueness, preventing null values, or establishing relationships with other tables.

The table constraints of the CREATE TABLE statement have the following syntax:

CONSTRAINT constraint_name constraint_type (column_list)

Let’s break down the table constraints of the CREATE TABLE statement:

  • The CONSTRAINT keyword indicates that you want to define a table constraint.
  • The constraint_name is the name of the table constraint that you want to create. You can choose any name that is valid in PostgreSQL, but it is recommended to follow some naming conventions, such as using lowercase letters and underscores.
  • The constraint_type is the type of the table constraint that you want to create. PostgreSQL supports many types of table constraints, such as primary key, foreign key, unique, not null, check, and more. You will learn more about each type of table constraint in the later sections.
  • The column_list is the list of columns that are involved in the table constraint, enclosed in parentheses. The columns must be existing columns in the table, and the number and order of the columns must match the requirement of the constraint type.

The table constraints can be defined either within the column definition or after the column definition, separated by commas. For example, you can create a table constraint that defines a primary key for two columns within the column definition, or after the column definition.

How do you use the table constraints of the CREATE TABLE statement to create tables with integrity and consistency in PostgreSQL? Let’s see some examples in the next section.

3. PostgreSQL Data Types

In this section, you will learn about the PostgreSQL data types that you can use to define the columns of a table. Data types specify the kind of data that can be stored in each column, such as numbers, characters, dates, times, and more. PostgreSQL supports a variety of data types, each with its own range, precision, format, and storage size.

The PostgreSQL data types can be classified into the following categories:

  • Numeric data types: These data types are used to store numeric values, such as integers, decimals, fractions, and floating-point numbers. PostgreSQL supports several numeric data types, such as smallint, integer, bigint, numeric, real, and double precision.
  • Character data types: These data types are used to store character strings, such as letters, symbols, and spaces. PostgreSQL supports two character data types, namely character varying (or varchar) and character (or char). You can also use the text data type to store unlimited-length character strings.
  • Date and time data types: These data types are used to store date and time values, such as years, months, days, hours, minutes, seconds, and time zones. PostgreSQL supports several date and time data types, such as date, time, timestamp, interval, and timestamptz.
  • Other data types: PostgreSQL also supports many other data types, such as boolean, array, json, xml, uuid, and more. You can also create your own custom data types using the CREATE TYPE statement.

How do you use the PostgreSQL data types to define the columns of a table? Let’s see some examples in the next section.

3.1. Numeric Data Types

In this section, you will learn about the numeric data types that you can use to store numeric values in PostgreSQL. Numeric values are numbers that can be used for mathematical calculations, such as integers, decimals, fractions, and floating-point numbers. PostgreSQL supports several numeric data types, each with its own range, precision, format, and storage size.

The numeric data types that PostgreSQL supports are as follows:

  • smallint: This data type is used to store small integers, ranging from -32768 to 32767. It occupies 2 bytes of storage.
  • integer: This data type is used to store medium-sized integers, ranging from -2147483648 to 2147483647. It occupies 4 bytes of storage.
  • bigint: This data type is used to store large integers, ranging from -9223372036854775808 to 9223372036854775807. It occupies 8 bytes of storage.
  • numeric: This data type is used to store exact numeric values, with a user-specified precision and scale. The precision is the maximum number of digits that the value can have, and the scale is the number of digits after the decimal point. The numeric data type can store up to 131072 digits before the decimal point and up to 16383 digits after the decimal point. It occupies variable storage depending on the precision and scale.
  • real: This data type is used to store approximate numeric values, with a single precision floating-point format. The real data type can store up to 6 decimal digits of precision. It occupies 4 bytes of storage.
  • double precision: This data type is used to store approximate numeric values, with a double precision floating-point format. The double precision data type can store up to 15 decimal digits of precision. It occupies 8 bytes of storage.

How do you use the numeric data types to define the columns of a table in PostgreSQL? Let’s see some examples in the next section.

3.2. Character Data Types

In this section, you will learn about the character data types that you can use to store character strings in PostgreSQL. Character strings are sequences of characters, such as letters, symbols, and spaces. PostgreSQL supports two character data types, namely character varying (or varchar) and character (or char). You can also use the text data type to store unlimited-length character strings.

The character data types that PostgreSQL supports are as follows:

  • character varying: This data type is used to store variable-length character strings, with a user-specified maximum length. The maximum length can be up to 10485760 characters. If you do not specify a maximum length, the character varying data type can store unlimited-length character strings. It occupies variable storage depending on the actual length of the string.
  • character: This data type is used to store fixed-length character strings, with a user-specified length. The length can be up to 10485760 characters. If you do not specify a length, the character data type defaults to a length of 1. If the string is shorter than the length, it is padded with spaces. It occupies fixed storage depending on the specified length of the string.
  • text: This data type is used to store unlimited-length character strings. It occupies variable storage depending on the actual length of the string.

How do you use the character data types to define the columns of a table in PostgreSQL? Let’s see some examples in the next section.

3.3. Date and Time Data Types

In this section, you will learn about the date and time data types that you can use to store date and time values in PostgreSQL. Date and time values are values that represent a specific point or duration in time, such as years, months, days, hours, minutes, seconds, and time zones. PostgreSQL supports several date and time data types, each with its own range, precision, format, and storage size.

The date and time data types that PostgreSQL supports are as follows:

  • date: This data type is used to store date values, without time zone information. The date data type can store values from 4713 BC to 5874897 AD. It occupies 4 bytes of storage.
  • time: This data type is used to store time values, without date or time zone information. The time data type can store values from 00:00:00 to 24:00:00, with a user-specified precision of up to 6 fractional digits for the seconds. It occupies 8 bytes of storage.
  • timestamp: This data type is used to store date and time values, without time zone information. The timestamp data type can store values from 4713 BC to 294276 AD, with a user-specified precision of up to 6 fractional digits for the seconds. It occupies 8 bytes of storage.
  • interval: This data type is used to store time intervals, or durations of time. The interval data type can store values from -178000000 years to 178000000 years, with a user-specified precision of up to 6 fractional digits for the seconds. It occupies 16 bytes of storage.
  • timestamptz: This data type is used to store date and time values, with time zone information. The timestamptz data type is an alias for the timestamp with time zone data type, which stores values in UTC and converts them to the local time zone when displayed. The timestamptz data type can store values from 4713 BC to 294276 AD, with a user-specified precision of up to 6 fractional digits for the seconds. It occupies 8 bytes of storage.

How do you use the date and time data types to define the columns of a table in PostgreSQL? Let’s see some examples in the next section.

4. PostgreSQL CREATE TABLE Examples

In this section, you will see some examples of how to use the CREATE TABLE statement to create different kinds of tables in PostgreSQL. You will learn how to create a simple table, a table with constraints, and a table from another table. You will also see how to use the different data types and syntax options that you have learned in the previous sections.

To run the examples, you will need to have PostgreSQL installed and running on your system. You will also need to have a database and a user account that you can use to connect to the database. You can use any tool or application that allows you to execute SQL commands, such as psql, pgAdmin, or DBeaver.

Before you create any table, you need to connect to the database that you want to use. You can use the following command to connect to a database using psql:

psql -U username -d database_name

Replace username with your user account name and database_name with the name of the database that you want to use. You will be prompted to enter your password. After you enter your password, you will see a prompt like this:

database_name=>

This means that you are connected to the database and ready to execute SQL commands.

Let’s start with the first example: creating a simple table.

4.1. Creating a Simple Table

In this section, you will see an example of how to use the CREATE TABLE statement to create a simple table in PostgreSQL. A simple table is a table that has a name and one or more columns, each with a name and a data type. A simple table does not have any constraints on the columns or the table.

To create a simple table in PostgreSQL, you can use the basic syntax of the CREATE TABLE statement, as follows:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
);

For example, suppose you want to create a table named customers that has four columns: id, name, email, and phone. The id column is an integer, the name column is a variable-length character string, the email column is a variable-length character string, and the phone column is a fixed-length character string. You can use the following CREATE TABLE statement to create the customers table:

CREATE TABLE customers (
    id integer,
    name varchar,
    email varchar,
    phone char(10)
);

After you execute the CREATE TABLE statement, you can use the \d command to describe the structure of the customers table, as follows:

database_name=> \d customers
                 Table "public.customers"
 Column |     Type          | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id     | integer           |           |          | 
 name   | character varying |           |          | 
 email  | character varying |           |          | 
 phone  | character(10)     |           |          |

As you can see, the customers table has four columns with the specified data types. The table does not have any constraints, such as primary key, foreign key, unique, not null, or check.

You have successfully created a simple table in PostgreSQL using the CREATE TABLE statement. You can now insert, update, delete, and query data from the customers table. You will learn how to do these operations in the later sections.

But what if you want to create a table that has some constraints on the columns or the table? How do you use the CREATE TABLE statement to create a table with constraints? Let’s see an example in the next section.

4.2. Creating a Table with Constraints

In this section, you will see an example of how to use the CREATE TABLE statement to create a table with constraints in PostgreSQL. A table with constraints is a table that has some rules or restrictions on the values that can be stored in the columns or the table. Constraints are used to ensure the data integrity and validity of the table.

To create a table with constraints in PostgreSQL, you can use the extended syntax of the CREATE TABLE statement, as follows:

CREATE TABLE table_name (
    column1 data_type constraint,
    column2 data_type constraint,
    ...
    table_constraint
);

The extended syntax of the CREATE TABLE statement is similar to the basic syntax, except that it allows you to specify constraints on the columns or the table. There are two types of constraints in PostgreSQL, namely column constraints and table constraints.

    • Column constraints: These are constraints that apply to a single column of the table. You can specify a column constraint after the data type of the column, using the following syntax:
column_name data_type constraint_name constraint_type
    • Table constraints: These are constraints that apply to the whole table or multiple columns of the table. You can specify a table constraint after the list of columns, using the following syntax:
CONSTRAINT constraint_name constraint_type (column_name1, column_name2, ...)

PostgreSQL supports several types of constraints, such as primary key, foreign key, unique, not null, check, and more. You will learn more about each type of constraint in the later sections.

How do you use the extended syntax of the CREATE TABLE statement to create a table with constraints in PostgreSQL? Let’s see an example in the next section.

4.3. Creating a Table from Another Table

In this section, you will see an example of how to use the CREATE TABLE statement to create a table from another table in PostgreSQL. A table from another table is a table that is created by copying the structure and/or data of an existing table. You can use this technique to create a backup, a copy, or a subset of an existing table.

To create a table from another table in PostgreSQL, you can use the following syntax of the CREATE TABLE statement:

CREATE TABLE new_table_name AS
SELECT column_list
FROM existing_table_name
WHERE condition;

Let’s break down the syntax of the CREATE TABLE statement to create a table from another table:

  • The CREATE TABLE keywords indicate that you want to create a new table in PostgreSQL.
  • The new_table_name is the name of the table that you want to create. You can choose any name that is valid in PostgreSQL, but it is recommended to follow some naming conventions, such as using lowercase letters and underscores.
  • The AS keyword introduces the query that defines the structure and data of the new table.
  • The SELECT statement is used to select the columns and data that you want to copy from the existing table. You can specify the column names or use the asterisk (*) to select all columns. You can also use expressions, functions, aliases, and other clauses in the SELECT statement.
  • The FROM clause specifies the name of the existing table that you want to copy from.
  • The WHERE clause is an optional clause that allows you to filter the data that you want to copy from the existing table. You can use any condition that is valid in PostgreSQL.
  • The semicolon (;) indicates the end of the statement.

How do you use the syntax of the CREATE TABLE statement to create a table from another table in PostgreSQL? Let’s see an example in the next section.

5. Conclusion

In this tutorial, you have learned how to create tables in PostgreSQL using the CREATE TABLE statement. You have learned the syntax, data types, and constraints for defining tables. You have also seen some examples of creating different kinds of tables, such as simple tables, tables with constraints, and tables from another table.

Creating tables is one of the most important and common tasks that you will perform as a PostgreSQL user or developer. Tables are the basic units of data storage in PostgreSQL, where you can store and manipulate your data. By creating tables, you can organize your data in a structured and meaningful way.

Now that you know how to create tables in PostgreSQL, you can explore other topics related to PostgreSQL, such as inserting, updating, deleting, and querying data from tables. You can also learn how to modify, drop, truncate, and rename tables in PostgreSQL. You can find more tutorials and resources on PostgreSQL on the official documentation website.

We hope you have enjoyed this tutorial and learned something new and useful. If you have any questions or feedback, please feel free to leave a comment below. Thank you for reading and happy learning!

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts