Performing DDL Operations on PostgreSQL Tables

Share via:


You can create a new table by specifying the table name, along with all column names and their types.

Syntax for table creation:

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

An example for creating table on weather report.

Two dashes (“–”) introduce comments. Whatever follows them is ignored up to the end of the line.


varchar (80) specifies a data type that can store arbitrary character strings up to 80 characters in length.

int is the normal integer type.

real is a type for storing single precision floating-point numbers.

date should be self-explanatory. (Yes, the column of type date is also named date. This might be convenient or confusing — you choose.)

PostgreSQL supports the standard SQL types int, small int, real, double precision, char(N), varchar(N), date, time, timestamp, and interval, as well as other types of general utility and a rich set of geometric types.

PostgreSQL can be customized with an arbitrary number of user-defined data types. Consequently, type names are not key words in the syntax, except where required to support special cases in the SQL standard.

Describe a table:

To know the information of a particular table i.e., meta data. Run the describe table command.

Inserting values into table:

To add rows to a table we use insert command Here we can add single row or multiple rows at a time to a particular table.

Selecting data from the Table:

To display contents in the table run the select command. To display all the contents in a table use *. Here * select all the rows in the table.

Altering a table:
  1. Adding a column:

To add a new column to a particular table we use ALTER command. Run the following command to ADD column.

Modifying column:

To change the Data type of column Run the following ALTER command.

Rename column:

To change the name of a column Run the following command.

Dropping a column:

To DROP a particular column in table Run the following command.

Copying a table:

To create a copy of an existing table Run the following command to create a table copy.

Drop, Delete and Truncate:

Dropping a table:

To DROP a table i.e., permanently remove table from the database. Run the following command.

Delete a table:

To remove particular object or row we use DELETE command.


To remove all the records in a table we use TRUNCATE command.

Author    : Prudhvi Teja

LinkedIn  :

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTExperts is always active on social media platforms.

Facebook  :
LinkedIn    :
Twitter       :
YouTube   :

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Add Comment