EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Table
 

PostgreSQL Table

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 6, 2023

PostgreSQL Table

 

 

Introduction to PostgreSQL Table

In this article, we will learn about PostgreSQL Table.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

A table is a data collection in a tabular format within a database, consisting of columns and rows. The table can be used to represent the relational data model; we can achieve relationship management using multiple tables linked using various keys.

Syntax

CREATE TABLE table_name (
column_name datatype column_constraint,
table_constraint table_constraint
) INHERITS existing_table_name;
  • CREATE TABLE clause: Define the new table name after the CREATE TABLE clause.
  • Define the column name list (one or many separated by comma), its data type, and respective column constraints.
  • After the column list, specify a constraint for the table, which we can term a table-level constraint. It defines rules for the data in the table.
  • INHERITS clause: Specify an existing table name from which the new table is getting inherited. This defines the newly created table will have columns defined in the CREATE TABLE statement and all columns of the existing table. INHERITS clause is PostgreSQL’s extension to SQL.

Create the student table, which has the following columns with constraints:

Code:

CREATE TABLE student (
rollno int PRIMARY KEY,
firstname VARCHAR (50) NOT NULL,
lastname VARCHAR (50) NOT NULL,
email VARCHAR (355) UNIQUE NOT NULL,
branch VARCHAR (50) NOT NULL,
result boolean,
joining_date DATE NOT NULL
);

Code:

select * from student;

Output:

PostgreSQL Table - 1

The following statement creates the ‘job’ table:

Code:

CREATE TABLE job (
job_id serial PRIMARY KEY,
job_name VARCHAR (255) UNIQUE NOT NULL
);

Code:

select * from job;

Output:

PostgreSQL Table - 2

Code:

select * from student_job;

Output:

PostgreSQL Table - 3

How to Drop the Table in PostgreSQL?

Below we will learn about the drop table:

Syntax:

DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];
  • In order to delete the table from the database, you need to define the name of the table after the DROP TABLE keyword.
  • PostgreSQL throws an error if you delete a non-existent table. To avoid exceptions while deleting such a situation, add the IF EXISTS parameter after the DROP TABLE clause.
  • If you want to delete a table used in constraints, views, or any other objects, you need to specify CASCADE after the table name, which allows you to remove a table and its all dependent objects.
  • By default, PostgreSQL uses RESTRICT, which restricts the table deletion of any object depends on it.
  • In order to delete multiple tables at a time, you need to specify a comma-separated list of table names after the DROP TABLE clause.

1. The following statement deletes ‘MY_TABLE’ from the database

Code:

DROP TABLE MY_TABLE;

PostgreSQL throws an error because the MY_TABLE does not exist. In order to avoid this error, use the IF EXISTS parameter.

Code:

DROP TABLE IF EXISTS MY_TABLE;

2. Create the following tables to understand the DROP TABLE more:

Code:

CREATE TABLE teacher (
teacher_id INT NOT NULL PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50)
);
CREATE TABLE subject (
subject_id serial PRIMARY KEY,
name VARCHAR (80) NOT NULL,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher (teacher_id)
);
  • Remove the teacher table

Code:

DROP TABLE IF EXISTS teacher;
  • Because the constraint on the subject table depends on the teacher table, PostgreSQL issues an error message.

Output:

PostgreSQL Table - 4

  • In this case, you need to remove all dependent objects first before removing the teacher table or use the CASCADE parameter as follows:

Code:

DROP TABLE teacher CASCADE;
  • PostgreSQL removes the teacher table as well as the constraint in the subject table.
Note: drop cascades to constraint subject_teacher_id_fkey on table subject.

How to Rename a Table in PostgreSQL?

Syntax:

ALTER TABLE table_name RENAME TO new_table_name;

Explanation: Define the table name to be renamed after the ALTER TABLE clause. Specify the new name for the table after the RENAME TO clause.

  • PostgreSQL throws an error if you try to rename the non-existing table. In order to avoid this situation, you need to add the IF EXISTS parameter as follows:

Code:

ALTER TABLE IF EXISTS table_name
RENAME TO new_table_name;
  • In order to rename multiple tables at a time, you need to execute multiple statements of ALTER TABLE RENAME TO. You can not do it in one statement. Let’s create a table ‘Books’ for demonstration.

Code:

CREATE TABLE Books (
id serial PRIMARY KEY,
name VARCHAR NOT NULL
);
  • Rename the ‘Books’ table to ‘Notebooks’ using the following statement

Code:

ALTER TABLE Books RENAME TO Notebooks;

How to Truncate tables in PostgreSQL?

In order to remove all rows from a table, you have to use the DELETE statement. But in the case of bigger tables, using a TRUNCATE TABLE statement is more efficient. The TRUNCATE TABLE statement is faster than DELETE because it removes data from the table without scanning.

Remove all data from a single table:

Syntax #1

TRUNCATE TABLE table_name;

Example: Remove all rows from the ‘Notes’ table

TRUNCATE TABLE Notes;
  • The PostgreSQL TRUNCATE TABLE statement resets the table’s associated sequence generator by defining RESTART IDENTITY and removing all data from a table.

Syntax #2

TRUNCATE TABLE table_name RESTART IDENTITY;

Example: Remove all rows from the Notes table and reset the sequence associated with the note_id column:

TRUNCATE TABLE Notes RESTART IDENTITY;
  • To remove all data from multiple tables at once, you need to specify comma(,) separated table name list as follows:

Syntax #3

TRUNCATE TABLE table_name1, table_name2

Example: The following statement removes all data from the Books and Notes tables:

TRUNCATE TABLE Books, Notes;
  • In order to remove data from the main table and all other tables dependent on it that have foreign key references to the main table, you need to use the CASCADE parameter.

Syntax #4

TRUNCATE TABLE table_name CASCADE;

Example: Remove all data from the Notes table and cascade to any tables that reference to Notes table via foreign key constraints

TRUNCATE TABLE Notes CASCADE;

How to Alter the table in PostgreSQL?

To change the existing table structure, use the ALTER TABLE statement.

Syntax:

ALTER TABLE table_name action;

1. Understand the actions supported by PostgreSQL with a teacher table having the following structure:

select * from teacher;

Output:

PostgreSQL Table - 5

2. Add a new column to a table using the ALTER TABLE ADD COLUMN statement:

Syntax:

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

Code:

ALTER TABLE teacher ADD COLUMN email VARCHAR (80);

Output:

PostgreSQL Table - 6

3. Remove an existing column using ALTER TABLE DROP COLUMN statement:

Syntax

ALTER TABLE table_name DROP COLUMN column_name;

Code:

ALTER TABLE teacher DROP COLUMN email;

Output:

PostgreSQL Table - 7

4. Rename an existing column using ALTER TABLE RENAME COLUMN TO statement:

Syntax:

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

Code:

ALTER TABLE teacher RENAME COLUMN teacher_id TO teacher_uid;

Output:

PostgreSQL Table - 8

5. Change the default value of the column using ALTER TABLE ALTER COLUMN SET DEFAULT or DROP DEFAULT:

Syntax:

ALTER TABLE table_name ALTER COLUMN column_name [SET DEFAULT value | DROP DEFAULT];

6. Change the NOT NULL constraint using ALTER TABLE ALTER COLUMN statement:

Syntax:

ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL| DROP NOT NULL];

Code:

Let’s check the column schema of the teacher table using the following statement:

SELECT column_name, is_nullable
FROM  information_schema.columns
WHERE  TABLE_NAME = 'teacher';

Output:

column schema

7. Now change the first_name to null and execute the above statement again.

Code:

ALTER TABLE teacher ALTER COLUMN first_name SET NOT null;

Output:

execute

8. Add a CHECK constraint using ALTER TABLE ADD CHECK statement:

Code:

ALTER TABLE table_name ADD CHECK expression;

9. Add a constraint using ALTER TABLE ADD CONSTRAINT statement:

Code:

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

10. Rename a table using ALTER TABLE RENAME TO statement:

Syntax:

ALTER TABLE table_name RENAME TO new_table_name;

Code:

ALTER TABLE teacher RENAME TO staff;

Output:

ALTER RENAME TO statement

How to Fetch data from a table?

PostgreSQL provides the FETCH clause to fetch a part of rows returned by a query.

Syntax:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

Explanation: ROW | ROWS and FIRST | NEXT are synonymous. The start is an integer that must be zero or positive.

If the OFFSET clause is not defined, the default value of the start is zero. The query returns no rows if the start is greater than the number of rows returned.

  • The row_count is one or higher.
  • If the OFFSET clause is not defined, the default value of row_count is one.

How to Copy a table in PostgreSQL?

Let’s understand the various ways of copying table structures using the staff table.

Code:

Insert some data on the staff table.
INSERT INTO staff (teacher_uid , first_name, last_name)
values
('101', 'Oliver','Jake'),
('102', 'Jack','Connor'),
('103', 'Harry','Callum'),
('104', 'Jacob','John'),
('105', 'Thomas','David');

1. Copy a complete table completely

Syntax:

CREATE TABLE new_table AS
TABLE existing_table;

Code:

CREATE TABLE teacher AS
TABLE staff;

Output:

complete

2. Copy a table without data from an existing table

Syntax:

CREATE TABLE new_table AS
TABLE existing_table
WITH NO DATA;

Code:

CREATE TABLE teacher AS
TABLE staff
WITH NO DATA;

Output:

Copy a without data

3. Copy a table structure with partial data from an existing table

Syntax:

CREATE TABLE new_table AS
SELECT *
FROM  existing_table
WHERE condition;
  • Condition: defines rows to be copied from the existing table

Code:

CREATE TABLE teacher AS
SELECT *
FROM  staff s2
WHERE first_name like 'J%';

Output:

Copy a structur

Conclusion

I hope you have understood the PostgreSQL tables in detail from the above and learned how to create a table, remove the existing table, rename a table, truncate a table, copy a table, etc.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Table” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Architecture
  2. Complete Guide to PostgreSQL Schema
  3. Examples and Parameters of PostgreSQL OFFSET
  4. How to Work PostgreSQL Alias?

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW