In PostgreSQL, objects including users, virtual warehouses, databases, schemas, tables, views, columns, functions, and stored procedures are created, modified, and altered using Data Definition Language (DDL) commands.
A collection of SQL commands called Data Definition Language (DDL) is used in PostgreSQL to specify and control the structure of database objects. DDL statements mostly deal with the creation, modification, and removal of objects like tables, databases, schemas, views, and indexes, in contrast to commands that work with the data stored in tables.
Several DDL statements, such as CREATE, ALTER, DROP, and TRUNCATE, are available in PostgreSQL. These commands assist database administrators and developers in creating the database structure, making changes to already-existing objects, and deleting objects or their contents when necessary. To effectively manage a PostgreSQL database's structure and organization, one must comprehend DDL statements.
PostgreSQL Create Table
To add a new table to a database in PostgreSQL, use the CREATE TABLE statement. A table uses rows and columns to hold data, with each row denoting a single record and each column defining a particular kind of information.
Column names, data types, and constraints like PRIMARY KEY, NOT NULL, UNIQUE, and DEFAULT can all be specified using the CREATE TABLE statement. In addition to ensuring that the table structure satisfies the application's needs, defining these components at table creation aids in maintaining data consistency.
To build a table with its columns and data types, use the create a statement. The fundamental syntax for creating a table is shown below.
Syntax:-
CREATE TABLE <table_name>(
<Column1> <DataType>,
<Column2> <DataType>,
<Column3> <DataType>,
<Column4> <DataType>,
.
.
.
<Column n> <DataType>
)
As you can see, we specified the table name after CREATE TABLE, followed by the column name and datatype in parentheses. Here is an example of how to make a table.
CREATE TABLE Student_Report(
ID INT PRIMARY KEY NOT NULL,
SNAME TEXT NOT NULL,
SAGE INT NOT NULL,
SADDRESS CHAR(50)
);
Output-
As you can see, we are creating the table Student_Report in the example above.We have four columns with the names ID, SNAME, SAGE, and SADDRESS, with ID serving as the main key and being both unique and non-null.
Let's set up another table.
CREATE TABLE Employee_Report(
EID INT PRIMARY KEY NOT NULL,
ENAME TEXT NOT NULL,
EAGE INT NOT NULL,
EADDRESS CHAR(50),
ESALARY INT
);
Output-
As you can see, we are creating the table Employee_Report in the example above.We have five columns with the names EID,ENAME,EAGE,EADDRESS, and ESALARY, with EID serving as the main key and being both unique and non-null.
\d Command in Postgres
The \d command, which will be used to list down all the tables in an associated database, may be used to check whether your table has been correctly constructed.
Syntax:-
\d
Example:-
postgres=# \d
Output-
In my system, 17 tables have been formed, but when you build the first two tables(which can be shown above) and run the query (\d), you can see the tables you've generated. You can view the table names for the student_report and Employee_Report tables here also.
“\d tablename” Command in Postgres
Syntax:-
\d tablename
Use “\d tablename” to specifically specify each table.
Example:-
postgres=# \d student_report
This command displays all of the student_report's details.
Output-
Exapmple-
postgres=# \d Employee_Report
This command displays all of the Employee_Report details.
Output-
PostgreSQL Drop Table
An existing table can be permanently removed from a database using PostgreSQL's DROP TABLE statement. It removes the table, all of the data it contains, and the table structure that goes with it. As a result, this command should be used with caution, particularly when handling production or sensitive data.
In order to prevent problems when the requested table does not exist, PostgreSQL also offers options like IF EXISTS. When a table is no longer needed or when a database structure needs to be entirely erased and rebuilt, the DROP TABLE statement can be helpful.
A table may be deleted from a database, a database can be removed, etc. using the drop command. The syntax and an example are provided below.
Syntax:-
DROP TABLE <table_name/database_name>
The drop command is used to drop or remove the table from the database and drop the database from the system.
Example:-
postgres=# drop table student_report;
This command drop the table student_report from the database.
Output:-
PostgreSQL Alter Table
In PostgreSQL, you can change an existing table's structure without erasing the table or its contents by using the ALTER TABLE query. As database requirements change, you can modify columns, constraints, and other table attributes.
You can rename tables or columns, add or remove constraints, and add, change, or remove columns using ALTER TABLE. Because of this, it is a crucial DDL statement for effectively updating and maintaining the database structure.
The Alter statement is used to make changes to the table structure, such as modifying a column's data type, name, or both. The syntax and an example of adding a column are shown below.
Syntax:-
ALTER TABLE <table_name>
<ADD/DROP/ALTER/ALTER> COLUMN <column_name> <datatype>
By using alter command you can add a column, modify a column, drop a column, rename a column.
Add columns in table using Alter
Syntax:-
ALTER TABLE <table_name>
<ADD> COLUMN <column_name> <datatype>
By using alter command you can add a column.
Example:-
postgres=# ALTER TABLE Employee_Report
postgres-# ADD Bounus int;
In this PostgreSQL ALTER TABLE demonstration, the Employee_Report table will add a Bonus field. It will be a NULL column when it is created.
Output:-
Add multiple columns in table using Alter
Syntax:-
ALTER TABLE <table_name>
<ADD/DROP/ALTER/ALTER> COLUMN <column_name> <datatype>
<ADD/DROP/ALTER/ALTER> COLUMN <column_name> <datatype>
<ADD/DROP/ALTER/ALTER> COLUMN <column_name> <datatype>
......
;
When using the ALTER TABLE command in PostgreSQL, the following syntax is used to add additional columns to a table.
Example:-
postgres=# ALTER TABLE Employee_Report
postgres-# ADD joining_date Date,
postgres-# ADD Father_name char(20);
In this PostgreSQL ALTER TABLE demonstration, the Employee_Report table will add a joining_date and Father_name field. It will be a NULL column when it is created.
Output:-
Drop columns in table using Alter
Syntax:-
ALTER TABLE <table_name>
<DROP> COLUMN <column_name>
By using this alter command you can drop a column.
Example:-
postgres=# ALTER TABLE Employee_Report
postgres-# Drop column joining_date;
In this PostgreSQL ALTER TABLE demonstration, the Employee_Report table will drop a joining_date field.
Output:-
Modify columns in table using Alter
Syntax:-
ALTER TABLE <table_name>
ALTER COLUMN <column_name> TYPE <data_type>
By using this alter command you can Modify the column data type in the table.
Example:-
postgres=# ALTER TABLE Employee_Report
postgres-# Alter column EADDRESS TYPE char(20);
In this PostgreSQL ALTER TABLE demonstration, the Employee_Report table will Modify the Column EADDRESS data type to char.
Output:-
Rename columns in table using Alter
Syntax:-
ALTER TABLE <table_name>
RENAME COLUMN <old_name> TO <new_name>
When using this ALTER TABLE query in PostgreSQL, this syntax is to rename a column in a table.
Example:-
postgres=# ALTER TABLE Employee_Report
postgres-# Rename column EADDRESS to HOMEADDRESS;
In this PostgreSQL ALTER TABLE QUERY, the Employee_Report table will Rename the Column EADDRESS to ADDRESS.
Output:-
Rename Table in table using Alter
Syntax:-
ALTER TABLE <table_name>
RENAME TO <new_name>
When using this ALTER TABLE query in PostgreSQL, this syntax is to rename the table to a new table_name.
Example:-
postgres=# ALTER TABLE Employee_Report
postgres-# Rename TO Employee_describe;
In this PostgreSQL ALTER TABLE QUERY, the Employee_Report table will be Rename to Employee_describe.
Output:-
PostgreSQL Truncate Table
To swiftly eliminate every row from an existing database while maintaining the table structure, use PostgreSQL's TRUNCATE database command. The table can still be utilized to store fresh data because TRUNCATE does not remove the table itself, in contrast to the DROP TABLE statement.
Because TRUNCATE TABLE is particularly made to clear an entire table, it is typically faster than using DELETE to remove all entries. Additionally, PostgreSQL offers options like CASCADE to truncate connected tables when needed and RESTART IDENTITY to reset associated sequence values.
TRUNCATE swiftly eliminates every row from a collection of tables. Although it is quicker since it does not really scan the tables, it has the same impact on each table as an unqualified DELETE. Additionally, it immediately frees up disc space rather than requiring a future VACUUM procedure. This works best with large tables.
Syntax:-
TRUNCATE TABLE <table_name>
With no changes to the table's structure, the TRUNCATE TABLE command eliminates all data from the table.
Example:-
postgres=# TRUNCATE TABLE Employee_describe;
The Employee_describe table's whole contents are removed using the TRUNCATE TABLE command in the example above.
Output:-
Truncate data over multiple table
You must separate each table with a comma (,) if you want to delete all the data from multiple tables at once.
Syntax:-
TRUNCATE TABLE
<table_name>,
<table_name>,
<table_name>,
......;
With no changes to the table's structure, the TRUNCATE TABLE command eliminates all data from the multiple table.
Example:-
postgres=# TRUNCATE student_report,Employee_describe;
The Employee_describe table and student_report table , whole contents are removed using the TRUNCATE TABLE command in the example above.
Output:-
Conclusion
A PostgreSQL database's structure must be defined and managed using DDL (Data Definition Language) statements. There are several ways to create, alter, remove, and clear database objects and their data using commands like CREATE TABLE, ALTER TABLE, DROP TABLE, and TRUNCATE TABLE.
Developers and database managers can maintain an effective and well-organized database structure by comprehending these statements. PostgreSQL databases can be efficiently handled while preserving data consistency and integrity by utilizing the proper DDL command according to the demand.
All things considered, PostgreSQL DDL statements serve as the basis for creating and managing database schemas. By adding new columns, changing current constraints, or eliminating superfluous tables and data, they make it simpler to modify the database structure when application requirements change. Maintaining database structures' flexibility, organization, and suitability for long-term usage requires a thorough understanding of these procedures.
The DDL (Data Definition Language) Statements in PostgreSQL are covered in this tip. We are taught how to build, drop, and amend tables in PostgreSQL. When altering a table, we are shown how to add columns, change the datatype of columns, rename columns and tables, and lastly, we are taught how to use the PostgreSQL truncate command.
Top comments (0)