How to Create & Drop Table in PostgreSQL [Example]

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

Step 1) Connect to the database where you want to create a table. We will create a table in database gtupapers

\c gtupapers

Step 2) Enter code to create a table

CREATE TABLE tutorials (id int, tutorial_name text);

Step 3) Use command \d to check the list of relations (tables)

Step 4) Again try to create the same table, you will get an error

Step 5) 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

Here is an 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

Step 1) In the Object Tree,

1. Select the Database
2. Select the Schema where you want to create a table in our case public.
3. Click Create Table

Step 2) In the popup, Enter the Table Name

Step 3)

1. Select the Columns Tab
2. Enter Column Details
3. 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 this command 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

Summary