CREATE TEMPORARY TABLE temp_table_name(
column_list
Let’s create the city_temp table:
CREATE TEMPORARY TABLE city_temp (
city VARCHAR(80),
street VARCHAR(80)
ON COMMIT DELETE ROWS;
Here, by adding ON COMMIT DELETE ROWS, we specify to remove data from the temporary table at the end of each transaction.
Creating a new table from the command line
SQL Shell (psql) is a command-line-based frontend to PostgreSQL. It allows entering, editing, and executing queries and statements as well as viewing their results.
To create a Postgres table from the command line, first launch SQL Shell. Next, connect to the database and execute the CREATE TABLE statement.
CREATE TABLE table_name (column_1 datatype, column_2 datatype);
The above-mentioned methods to create a new table in PostgreSQL are quite straightforward. If you have basic knowledge of SQL, you can master them quickly. However, database developers and DBAs have to perform hundreds of similar tasks every day, and those tasks need to be performed quickly and without any errors.
The solution? That’s where the professional database development tools become extremely handy. Let’s observe one of the most convenient tools – dbForge Studio for PostgreSQL. With its help, the PostgreSQL create tables jobs can be completed with a couple of clicks and minimum manual coding.
Creating a PostgreSQL table using dbForge Studio for PostgreSQL
dbForge Studio for PostgreSQL is an advanced solution designed to offer all the necessary tools for PostgreSQL database development and administration in a single IDE. The Studio boasts a user-friendly interface, allowing people without database-related background to effectively cope with database tasks.
With dbForge Studio for PostgreSQL, you can easily create a table in SQL Editor and while doing that benefit greatly from automatic syntax check, context-sensitive code completion, and execution notifications.
To create a table using dbForge Studio for PostgreSQL:
1. Launch the Studio and connect to the server.
In the process, dbForge Studio for PostgreSQL will offer context-sensitive autocompletions so that you didn’t type all the code manually – just click to insert the suggestion into your syntax. It will also check your code and highlight typos. To get quick information about objects in the script, simply hover the mouse over them.
Postgres table constraints
Constraints are special rules or restrictions for data in a table. PostgreSQL supports both table and column constraints. Table constraints specify restrictions that apply to the whole table while a column constraint affects only one specific column.
PostgreSQL supports the following column constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY.
Let’s consider some of them in more detail. We have already mentioned UNIQUE and NOT NULL constraints above.
PRIMARY KEY constraint indicates that the column is used to uniquely identify a record within a table. Thus, when creating a PostgreSQL table, it is important to remember that the columns with PRIMARY KEYS can contain only unique (non-duplicate) values and cannot have NULLs.
Let’s consider the example Postgres CREATE TABLE statement with PRIMARY KEY.
CREATE TABLE orders (
order_id integer NOT NULL,
order_date date,
quantity integer,
notes varchar(200),
CONSTRAINT orders_pk PRIMARY KEY (order_id)
Note, that you can not create a table with multiple primary keys as it is against the essence of a primary key, instead you can have a primary key that contains multiple columns (a composite primary key).
CREATE TABLE product_tags
( product_id INTEGER NOT NULL,
tag_id SERIAL NOT NULL,
production_date VARCHAR(20),
tag_peni VARCHAR(20),
item_number VARCHAR(20),
PRIMARY KEY(product_id, tag_id)
FOREIGN KEY constraints are used to relate tables in a database. A foreign key comprises a column or a group of columns in one table that reference the primary key column or columns in another table. In other words, the FOREIGN KEY constraint specifies that the values in a column must match the values in another table. In such a simple way, database referential integrity is maintained.
CHECK constraints are used to make sure that values in a column meet a specific requirement. CHECK constraints use a Boolean expression to evaluate the values before they are inserted into a table. In case a value doesn’t pass the check, PostgreSQL won’t insert it and will issue a constraint violation error.
CREATE TABLE prices
( id serial PRIMARY KEY,
product_name VARCHAR (50),
product_description VARCHAR (50),
price numeric CHECK(price > 0)
Here we make sure that the price value must be greater than zero.
How to use the PostgreSQL CREATE TABLE AS statement
To create a new PostgreSQL table based on the results of a query, you can use the CREATE AS statement. In other words, Postgres CREATE TABLE AS statement creates a new table and populates it with the data returned by a query.
See the basic syntax for CREATE TABLE AS:
CREATE TABLE new_table_name
AS query;
If you want to make sure that your table doesn’t already exist, you might use the IF NOT EXISTS operator. In this case, the syntax is as follows:
CREATE TABLE IF NOT EXISTS new_table_name
AS query;
Let’s now look at the worked example:
CREATE TABLE thrillers AS
SELECT
movie_id,
movie_title,
production_year,
imbd_rating
movies
INNER JOIN movie_category USING (movie_id)
WHERE
category_id = 5;
The OR REPLACE option for the CREATE TABLE statement
The OR REPLACE option on the CREATE TABLE statement is used in MariaDB databases to change the definition of the existing table. In such a way, you can replace the old table with the newly defined one. In plain English, if you use the CREATE OR REPLACE TABLE statement and the table already exists, there won’t be any error issued – the old table will be dropped and the new one will be created.
However, PostgreSQL doesn’t support the OR REPLACE option on the CREATE TABLE statements. In Postgres, OR REPLACE works well with CREATE VIEW and CREATE FUNCTION statements, but doesn’t work for CREATE TABLE.
Instead, you may use the following method.
Suppose, you have a table:
CREATE TABLE t_table (
pk INT PRIMARY KEY,
txt VARCHAR(255)
Now, we want to replace it with another table. For this, we create a new table with the same structure, insert values into it, change the names of the old table and the new one, and then drop the old table:
CREATE TABLE "table_new" AS TABLE t_table;
INSERT INTO "table_new" (pk, txt) VALUES (1,'1');
ALTER TABLE t_table RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO t_table;
DROP TABLE "table_old";
Conclusion
In the article, we have explored the popular methods to create a new table in a PostgreSQL database and found out that dbForge Studio for PostgreSQL offers the advanced functionality for you to perform the task in the most convenient and beneficial way.
Download a 30-day trial of dbForge Studio for PostgreSQL and check it yourself.
Founded in 1997, Devart is currently one of the leading developers of database management software, ALM solutions and data providers for most popular database servers.
© 2024 Devart. All rights reserved.
