How to Create & Drop Table in PostgreSQL [Examples]
The command to create a new table is
Syntax
CREATE TABLE table_name ( field_name data_type constrain_name, field_name data_type constrain_name );
Here
table_name: Is the name of the table
field_name: Is the name the column
data_type: Is the variable type of the column
constrain_name: Is optional. It defines constraints on the column.
Tables never have the same name as any existing table in the same schema.
PostgreSQL Create Table: SQL Shell
Here is a step by step process to create table in PostgreSQL:
Step 1) Connect to the Database
Connect to the database where you want to create a table. We will create a table in database guru99
\c guru99
Step 2) Create a Table
Enter code to create a table
CREATE TABLE tutorials (id int, tutorial_name text);
Step 3) Check the relation of tables
Use command \d to check the list of relations (tables)
Step 4) Try creating the same Table
Again try to create the same table, you will get an error
Step 5) Use IF NOT EXISTS parameter
Use the parameter IF NOT EXISTS and you will get a notice instead of an error
The list of parameters you can use while creating a table is exhaustive. Here are a few important ones
Parameter Name | Description |
---|---|
TEMP or TEMPORARY | This parameter creats a temporary table. Temporary tables are deleted at the end of a session, or at after the current transaction. |
Unlogged | Unlogged clause does not enter data into WAL(write ahead log). Due to removal of this additional IO operation, write performance is increased |
If not exists | If a table already exisits with a same name, a warning is shown instead of an error |
Of_type_name | A table that takes structure from the specified composite type. |
Here is a PostgreSQL create table example of a table with constraints
CREATE TABLE order_info ( order_id integer CONSTRAINT order_details_pk PRIMARY KEY, Product_id integer NOT NULL, Delivery_date date, quantity integer, feedback TEXT );
PostgreSQL Create Table: pgAdmin
Below is a step by step process to create table in pgAdmin:
Step 1) In the Object Tree,
- Select the Database
- Select the Schema where you want to create a table in our case public.
- Click Create Table
Step 2) In the popup, Enter the Table Name
Step 3)
- Select the Columns Tab
- Enter Column Details
- Click Save
Step 4) In the object tree, you will see the table created
PostgreSQL Delete/Drop Table
The PostgreSQL DROP TABLE statement allows you to remove a table definition and all associated data, indexes, constraints, rules, etc. for that table.
You should be cautious while using the command DROP TABLE in PostgreSQL because when a table is deleted, then all the information containing in the table would also be lost permanently.
Syntax
DROP TABLE table_name;
Example
Step 1) Let’s check the existing tables using command \d
Step 2) Delete table tutorials using the command
DROP TABLE tutorials;
Step 3) Again check for the list of relations and we see the table is deleted using Postgres delete command
Summary
CREATE TABLE table_name ( field_name data_type constrain_name, field_name data_type constrain_name ); |
Command to create Table |
DROP TABLE table_name; | Command to Delete Table in PostgreSQL |