PostgreSQL – CREATE TABLE – Query and pgAmdin

Create Table using SQL Query

To create a new table in PostgreSQL database, use sql CREATE TABLE query.

The syntax of CREATE TABLE query is:

CREATE TABLE table_name(
    column1 datatype,
    column2 datatype,
    ...
    columnN datatype
 );

where

  • table_name is the name given to the table. This table_name is used for referencing the table to execute queries on this table.
  • column1, column2,.., columnN are the column names of the table.
  • datatype s are are the respective datatypes of the columns.

Example 1 – Create a Table in PostgreSQL

In this example, we will create a PostgreSQL Table in a database, by running CREATE TABLE sql query in query tool of pgAdmin.

CREATE TABLE students(
	id   INT,
	name CHAR(50),
	age  INT
)

If the CREATE TABLE query is successful, you will get a message that Query returned successfully as shown below.

PostgreSQL CREATE TABLE

Now, if you right click on the Tables under mydb database, students table appears.

PostgreSQL CREATE TABLE

CREATE TABLE  using GUI of pgAdmin

Without writing the CREATE TABLE query by yourself, you can also use GUI of pgAdmin to create a table.

Expand the database in which you would like to create the table. Expand Schemas, public, Tables, right click on the Tables and click on Create, Table.

PostgreSQL CREATE TABLE GUI

A wizard appears to create table.

PostgreSQL CREATE TABLE GUI

Provide table name against Name field. You can also select details like: Owner, Schema, Tablespace, Partitioned table flag and Comment.

Create Table

In this example, we will give Name and Comment, and leave the rest to their default values.

PostgreSQL CREATE TABLE

Create Columns

Now click on the Columns tab. As shown in the following screenshot, click on the + button to add a column. Then fill in the details for the column. You can click the same + button to create more columns.

PostgreSQL CREATE TABLE - Add Columns

We have added two column, selected their datatypes and changed some of the flags for them as shown below. After you add the required columns, click on Save button.

PostgreSQL CREATE TABLE

A new table is created with the name mytable and you can see the table under Tables in the left panel of pgAdmin.

Conclusion

In this PostgreSQL Tutorial, we have learnt to create a table using Query and pgAmdin.