Create a Table in PostgreSQL

CREATE TABLE in PostgreSQL

The CREATE TABLE statement is used in PostgreSQL to create a new table within a specified schema. This statement provides a comprehensive way to define the table's structure, including column names, data types, and constraints.

Syntax

The syntax for the CREATE TABLE in PostgreSQL is as follows:

Parameters

• Column Name:
  This is the name of the column you are defining. It should be unique within the table.
• Data Type:
  Specifies the data type of the column, such as INTEGER, VARCHAR, DATE, etc.
• Constraints:
  Constraints enforce rules on the data within the column. Common constraints include NOT NULL (values cannot be null), UNIQUE (values must be unique), and PRIMARY KEY (uniquely identifies each row).
• Default Value:
  You can provide a default value for a column if no value is explicitly specified during insertion.
• Auto-increment:
  For columns like primary keys, you can use the SERIAL data type to automatically generate a unique value for each row.

Examples

Let's take a look at some examples of creating tables in PostgreSQL:

Example 1: Creating a Simple Table

Output

In this example, we're creating a table named employees with columns for employee details. The employee_id column is set as the primary key, and first_name and last_name columns are defined as mandatory.

Example 2: Adding Constraints

Output

Here, the orders table includes a foreign key reference to the customers table and defines a total_amount column with a numeric data type.

How to Create Table in PostgreSQL?

Using the Like Option

When it comes to creating new tables in PostgreSQL, the LIKE option provides a convenient way to replicate the structure of an existing table. This can be especially useful when you want to create a new table that inherits the attributes, data types, and constraints of an already existing table. By using the LIKE option, you can save time and effort in defining column names, data types, and other properties that match the source table.

The syntax for creating a new table using the LIKE option is as follows:

where:

• new_table_name:
  The name you want to give to the new table you're creating.
• old_table_name:
  The name of the existing table whose structure you want to replicate.

Example:

Let's consider an example scenario. Suppose you have an existing table named products that contains information about various products, including their names, prices, and categories. Now, you want to create a new table named new_products that will have a similar structure for a different set of products. Instead of manually specifying all the column names and data types, you can use the LIKE option to quickly achieve this.

Here's how you can do it:

In this example, the new_products table is created with the same structure as the products table. This means it will have the same columns, data types, constraints, and other attributes. However, it's important to note that the new table does not inherit any data from the original table. It only inherits the structure.

Temporary Table Creation in PostgreSQL

Temporary tables are a special type of table in PostgreSQL that allows you to store and manipulate data temporarily for the duration of a session or transaction. These tables are particularly useful when you need to store intermediate results, perform complex queries, or perform data manipulations without affecting permanent tables in the database. Temporary tables are automatically dropped at the end of the session or transaction, making them ideal for temporary data storage needs.

Creating Temporary Tables:

To create a temporary table in PostgreSQL, you can use the CREATE TEMP TABLE statement. The syntax is similar to creating a regular table, with the addition of the TEMP keyword:

where:

• temp_table_name:
  The name you want to give to the temporary table.
• column1, column2, etc.:
  The names of the columns within the temporary table.

Example:

Let's consider a scenario where you need to perform a series of calculations on a set of data without affecting the permanent tables in your database. You can use a temporary table to store intermediate results and work with them within a session.

Output:

In this example, we're creating a temporary table named temp_sales with columns for product_id and quantity_sold. You can then insert, update, or delete data in this temporary table as needed within the same session.

Using dFroge Studio

dForge Studio is a powerful and feature-rich GUI (Graphical User Interface) tool designed for managing and developing PostgreSQL databases. This tool offers an intuitive and user-friendly interface that simplifies various database tasks, including creating tables. Whether you're a database administrator, developer, or data analyst, dForge Studio can greatly enhance your productivity and efficiency when working with PostgreSQL databases.

• The first step to use dFroge is to go on this official downloads page link and download the exe.

• Click on the exe file that is downloaded. You can navigate to the Downloads folder on your machine to locate this exe:

• The following screen will home. Click on the Install button:

• Choose the directory where you want the installation to happen. You can also set it to install in the default directory:

• Specify the file extensions that will be associated with this application and click on Next:

• Choose your Startup Optimization setting and press Next:

• Once the installation is complete, you will see the following screen:

• Open dFroge and select database connection properties:

• On the left hand side, you would see a list of databases. Right click on the database of your choice and click on the New SQL button:

• A SQL editor will open. For creating a table, write a CREATE TABLE statement in this editor.

• Click on the Execute button and your table will be created.

Creating a New Table from Command Line

You can create a new table directly from the command line using the psql tool. Command-line creation offers a quick and efficient way to define tables and their attributes without the need for a graphical interface. This method is especially useful for scripting, automation, and when working on remote servers.

Using the psql Tool

The psql tool is a command-line interface for interacting with PostgreSQL databases. It allows you to execute SQL commands, including creating tables, directly from the terminal.

To create a new table from the command line, follow these steps:

1. Open a Terminal:
   Launch your terminal application.
2. Connect to the Database:
   Use the following command to connect to the PostgreSQL database:

3. Execute SQL Command:
   Once connected, you can execute SQL commands directly in the terminal. To create a new table, use the CREATE TABLE statement:
   Replace new_table, column1, column2, etc. with the appropriate names and data types.
4. Exit psql: After creating the table, you can exit the psql interface by typing:
   This command will return you to the regular terminal.

Example

Let's take a simple example of creating a table named books with columns for book information:

In this example, we connect to the my_database database, execute the CREATE TABLE statement to define the structure of the books table, and then exit the psql interface using the \q command.

Table Containers

Table containers, also known as tablespaces, allow you to specify a location for storing table and index data separately from the main database storage.

Example

You can create a table within a specific tablespace using the TABLESPACE option in the CREATE TABLE statement. Here's an example:

In this example:

• We first create a new tablespace named my_tablespace using the CREATE TABLESPACE command. This specifies a location (/path/to/my_tablespace) where the tablespace will be stored.
• Next, we use the TABLESPACE option when creating a table (new_table). This indicates that the table's data will be stored in the my_tablespace tablespace.

Output:

If the table and tablespace creation statements are successful, PostgreSQL will return a confirmation message indicating that the table and tablespace have been created.

If there are any errors, PostgreSQL will provide an error message to indicate the issue.

The CREATE TABLE AS Statement

The CREATE TABLE AS statement lets you create a new table with data derived from a SELECT query.

Example

Let us take an example to understand this concept. Let us create a table named employees with the fields: employee_id, first_name, last_name and salary.

Use the following set of queries to insert data into the high_earners table.

We want to create a new table high_earners with employees earning more than 50000.

In this example:

• We use CREATE TABLE high_earners AS to indicate that we want to create a new table named high_earners.
• The SELECT statement following it retrieves specific columns (employee_id, first_name, last_name, salary) from an existing table (employees).
• The WHERE clause filters the records to only include those where the salary is greater than 50000.

Output:

If the statement executes successfully, PostgreSQL will return a confirmation message indicating that the new table has been created.

You can then query the high_earners table to view its contents.

Output:

The OR REPLACE Option

In databases, the OR REPLACE option in CREATE TABLE in PostgreSQL facilitates altering the definition of an existing table. Essentially, it allows you to swap out the old table for the newly defined one. To put it simply, if you employ the CREATE OR REPLACE TABLE statement and the table already exists, it won't trigger an error – instead, the old table will be removed, and the new one will take its place.

PostgreSQL, however, does not support the OR REPLACE option within CREATE TABLE in PostgreSQL. In Postgres, OR REPLACE works seamlessly with CREATE VIEW and CREATE FUNCTION statements, but it does not apply to CREATE TABLE.

An alternative approach can be used. Let's assume you have a table:

Populate it with some data:

Now, if you wish to replace it with another table, you can follow these steps:

1. Create a new table with the same structure.
2. Insert values into the new table.
3. Rename the old table and the new one.
4. Drop the old table:

This sequence of actions allows you to effectively replace the existing table with a new one.