Introduction to PostgreSQL Table
In this article, we will learn about PostgreSQL Table.
A table is a collection of data in a tabular format that exists within a database, which consists 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 as 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 sequence associated with the note_id column:
TRUNCATE TABLE Notes RESTART IDENTITY;
- In order 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 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 not null and again execute the above statement.
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 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 from the above, you have understood the PostgreSQL tables in detail and learned how to create a table, remove the existing table, rename a table, truncate a table, copy a table, etc.
Recommended Articles
This is a guide to PostgreSQL Table. Here we discuss how to create a table, remove the existing table, rename a table, truncate a table and copy a table, etc., in detail with appropriate syntax and respective examples. You can also go through our other related articles to learn more –
