PostgreSQL - CREATE Table

The PostgreSQL CREATE TABLE statement is used to create a new table in any of the given database.

Syntax

Basic syntax of CREATE TABLE statement is as follows −

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

CREATE TABLE is a keyword, telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Initially, the empty table in the current database is owned by the user issuing the command.

Then, in brackets, comes the list, defining each column in the table and what sort of data type it is. The syntax will become clear with an example given below.

Examples

The following is an example, which creates a COMPANY table with ID as primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table −

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

PostgreSQL - DROP Table

The PostgreSQL DROP TABLE statement is used to remove a table definition and all associated data, indexes, rules, triggers, and constraints for that table.

You have to be careful while using this command because once a table is deleted then all the information available in the table would also be lost forever.

Syntax

Basic syntax of DROP TABLE statement is as follows −

DROP TABLE table_name;

Example

We had created the tables DEPARTMENT and COMPANY in the previous chapter. First, verify these tables (use \d to list the tables) −

testdb-# \d

testdb=# drop table tb_1, tb_2;

Using DROP DATABASE

This command drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. This command cannot be executed while you or anyone else is connected to the target database (connect to postgres or any other database to issue this command).

Syntax

The syntax for DROP DATABASE is given below −

DROP DATABASE [ IF EXISTS ] name

Parameters

The table lists the parameters with their descriptions.

Example

The following is a simple example, which will delete testdb from your PostgreSQL schema −

postgres=# DROP DATABASE testdb;

Database SQL Prompt

Assume you have already launched your PostgreSQL client and you have landed at the following SQL prompt −

postgres=#

You can check the available database list using \l, i.e., backslash el command as follows −

postgres-# \l

OS Command Prompt

You can select your database from the command prompt itself at the time when you login to your database. Following is a simple example −

psql -h localhost -p 5432 -U postgress testdb