Creating Tables in PostgreSQL

The CREATE TABLE statement is used to creating a table in the provided schema. If a schema name is given, then the table will be create in the specified schema. Otherwise it will create in the current schema.

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

So it must be distinct from the name of any other table, sequence, index, view, or foreign table in the same schema

Syntax

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

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

CREATE TABLE actors (
	actors_id SERIAL PRIMARY KEY,
	first_name VARCHAR(150),
	last_name VARCHAR(150) NOT NULL,
	gender CHAR(1),
	date_of_birth DATE,
	add_date DATE,
	update_date DATE
);

Creating Table with Foreign Key

Foreign key is a way to link different tables together for data connectivity.

The REFERENCES clause specify a foreign key constraint, which requires that a group of one or more columns of the new table must only contain values that match values in the referenced column(s) of some row of the referenced table.

The foreign key constraints cannot be defined between temporary tables and permanent tables.

CREATE TABLE movies (
	movie_id SERIAL PRIMARY KEY,
	movie_name VARCHAR(100) NOT NULL,
	movie_length INT,
	movie_lang VARCHAR(20),
	age_certificate VARCHAR(10),
	release_date DATE,
	director_id INT REFERENCES directors (director_id)
);

Creating a Junction Table

A Junction table is a table where it contain foreign keys of different tables together so that they can create a transaction table. .

CREATE TABLE movies_actors(
	movie_id INT REFERENCES movies (movie_id),
	actor_id INT REFERENCES actors (actors_id),
	PRIMARY KEY (movie_id,actor_id)
);

Creating Table Using PgAdmin

Follow the below process to create a table using PgAdmin.

Firstly, open the pgAdmin in local system, and then go to the object tree and select the database, in which you want to create a table.

Click left to select the database(here pickupbrain is selected), and then you can see the Catalogs and Schemas.

Then right-click on the Public under Schema section, select Create option from the provided drop-down, and click on the Table from the shown list.

Click on the Table, the Create-Table window will appear on the screen where you need to enter all the necessary details like Table name.

ove to the Column tab in the same window then click on the + sign to add columns in a particular table and select the data types from the given drop-down list.

You can change the columns Not-null preference and also set the Primary key.

Now click on Save button to complete the process of creating a table. After that you can see that the table is created under the table section.