PostgreSQL Table

Updated May 6, 2023

Introduction to PostgreSQL Table

In this article, we will learn about PostgreSQL Table.

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:

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:

Code:

select * from student_job;

Output:

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:

• 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.

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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?