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.
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.
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.
You can verify if your table has been created successfully using \d command,
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.
To add a new column into the table
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,
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,
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,
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.
DROP TABLE table_name;
DROP TABLE payroll;
This above example would delete the table payroll from lauyou database.