How to PostgreSQL- Create & Delete Tables

In this tutorial, you will learn how to use CREATE TABLE & DROP TABLE statements to create & delete tables respectively from the database.

 

PostgreSQL CREATE TABLE

The PostgreSQL CREATE TABLE statement is used to create a new, initially empty table in the current database. The table will be owned by the user issuing the command. This PostgreSQL tutorial explains how to use the PostgreSQL CREATE TABLE statement with syntax and examples.

Syntax
     CREATE TABLE table_name( 
     column1 datatype [ NULL | NOT NULL ],
     column2 datatype [ NULL | NOT NULL ],
     ...);

 

Above syntax is the simplest form of CREATE TABLE statement in PostgreSQL. The full syntax for the PostgreSQL CREATE TABLE statement has lots options. Check PostgreSQL documentation here.

PostgreSQL supports the data types: Boolean, Character, Number, Temporal i.e., date and time-related data types, Special types, Array. For more data types, check PostgreSQL documentation here.

CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema.

Example
     CREATE TABLE payroll(
     id INTEGER CONSTRAINT payroll_key PRIMARY KEY,
     firstname VARCHAR(30) NOT NULL,
     lastname VARCHAR(30) NOT NULL,
     payment_date DATE,
     designation VARCHAR(30),
     salary INTEGER NOT NULL
     );

 

NOT NULL – the value of the column cannot be NULL.
CONSTRAINT – An optional name for a column or table constraint. If the constraint is violated, the constraint name is present in error messages.
UNIQUE – the value of the column must be unique across the whole table
PRIMARY KEY – this constraint is the combination of NOT NULL and UNIQUE constraints. You can define one column as PRIMARY KEY by using column-level constraint.

 

Result

postgresql create & Delete Tables

 

You can verify if your table has been created successfully using \d command,

 

Table List

Create & Delete Tables

Now we have a table named payroll in database lauyou.

 

ALTER TABLE Statement

PostgreSQL ALTER TABLE statement is used to add a column, modify a column, drop a column, rename a column or rename a table.

Syntax:

To add a new column into the table

Syntax
     ALTER TABLE table_name
     ADD new_column_name column_definition;

 

new_column_name: The name of the new column to add to the table.
column_definition: The datatype of the column.

The syntax to modify a column in a table in PostgreSQL is,

Syntax
     ALTER TABLE table_name
     ALTER COLUMN column_name TYPE column_definition;

 

column_name: The name of the column to be modified.

The syntax to drop a column in a table in PostgreSQL is,

Query
     ALTER TABLE table_name
     DROP COLUMN column_name;

 

column_name: The name of the column to delete from the table.

The syntax to rename a table in PostgreSQL is,

Query
     ALTER TABLE table_name
     RENAME TO new_table_name;

 

table_name: The table to rename.
new_table_name: The new table name.

 

PostgreSQL DROP TABLE

The PostgreSQL DROP TABLE statement will delete a table from the PostgreSQL database.

Query
     DROP TABLE table_name;
Example
     DROP TABLE payroll;

 

This above example would delete the table payroll from lauyou database.

Muhsin Yakkiparamban

Muhsin Yakkiparamban is the co-founder of Lauyou Learning. Muhsin holds Bachelor Degree in Civil Engineering from KMCT Calicut.

You may also like...