Data Definition Language (DDL)
A key component of the SQL standard, Data Definition Language (DDL) is in charge of specifying and modifying a database’s relational structure. Its main objective is to design and oversee the database’s data structure. Since the data structure is usually specified once and then rarely altered, DDL statements are often used less frequently than Data Manipulation Language (DML), which deals with the data itself. DROP (to delete database objects like tables, views, or functions), ALTER (to change existing database objects, like adding or deleting columns, or altering constraints), and CREATE (for databases, tables, views, functions, types, and more) are examples of common DDL procedures.
Developers can record descriptions of database objects using other DDL statements like COMMENT ON. TRUNCATE swiftly deletes every entry from a table; it works similarly to an unqualified DELETE but is quicker because it doesn’t search the table. Additionally, DDL command start events can be logged by event triggers in PostgreSQL, demonstrating the importance of these actions in the database system.
Core DDL Commands in PostgreSQL
CREATE, ALTER, and DROP are the main database creation, modification, and deletion DDL commands.
CREATE
SQL’s Data Definition Language (DDL) is essential for creating, maintaining, and updating a database’s relational structure. It governs the database’s structure or architecture, unlike DML, which manipulates data. DDL instructions are therefore often used less often after the database structure is set up.
A key component of DDL, the CREATE command allows the creation of a variety of database objects. Among CREATE’s primary uses are:
CREATE DATABASE: A new database can be created with this command. PostgreSQL usually copies an existing template database, template1 being the default, when a new database is created. During creation, the database owner, encoding, collation, and tablespace can all be specified. When template0, new encoding and locale settings can be specified, something that is not feasible when template1 is copied because of potentially encoding-specific data.
CREATE TABLE: The main DDL command for creating database tables. When creating a table, you identify its columns and data types. Tables can have integrity requirements like PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and FOREIGN KEY to relate to other tables. Additionally, columns may have DEFAULT values. The SERIAL shorthand allows simply generate an integer column that automatically fills with sequence generator data. Tables can be INHERITS (inherited columns and constraints from a parent table), TEMPORARY (session-only), UNLOGGED (not tracked in transaction logs), or permanent. Use the build TABLE AS statement to build a table from SELECT results.
CREATE VIEW: In order to wrap a SELECT statement, the CREATE VIEW command creates a named query or virtual table. To make views updateable, define INSTEAD OF triggers.
CREATE FUNCTION: To define new functions that can return sets of records or scalar values, use the CREATE FUNCTION function. PL/pgSQL, C, SQL, and other procedural languages can all be used to write functions. VOLATILE, STABLE, and IMMUTABLE are the volatility categories that PostgreSQL employs to tell the query optimiser how a function behaves.
CREATE TYPE: Users can specify new data types using this command. These can include enumerated types (ENUM), which are static, ordered sets of values; domain types, which are base types combined with constraints; and composite types, which are lists of field names and their kinds that resemble table rows.
CREATE SCHEMA: This creates a new schema that arranges database items similarly to programming language namespaces, saving object names from conflicting when they are used in various schemas.
CREATE INDEX: In order to improve query efficiency, secondary indexes on tables are created using the CREATE INDEX function. In order to enforce the uniqueness of values, indexes might be UNIQUE.
CREATE SEQUENCE: The CREATE SEQUENCE function defines a sequence generator to control serial data type autoincrementation.
CREATE TRIGGER: Create a trigger to run a predetermined function when INSERT, UPDATE, DELETE, or TRUNCATE a table or view. Triggers can activate before, after, or instead of the event.
CREATE USER / CREATE ROLE: New database users or roles can be created using the CREATE USER and CREATE ROLE commands. LOGIN privilege (connecting to the database), the ability to build other databases (CREATEDB), or the capacity to create roles (CREATEROLE) are just a few of the properties that can be assigned to roles. The LOGIN attribute is automatically included in CREATE USER.
CREATE OPERATOR: PostgreSQL’s CREATE OPERATOR DDL statement provides new user-defined operators, greatly expanding the database’s capability. Operators are symbolic aliases for underlying functions that do the work, making query syntax more natural and readable.
CREATE EXTENSION: In addition to PostgreSQL’s built-in functionality, this command can be used to install extra procedural languages.
CREATE EVENT TRIGGER: DDL command logging is enabled by these unique triggers, which are activated by DDL command beginnings or endings, specifically SQL DROP events.
COMMENT ON: This DDL statement, which is not a CREATE command per se, enables developers to save descriptions of database objects after they have been created.
ALTER
DDL in PostgreSQL relies on the ALTER command to change database object definitions and structures. ALTER operations update the schema by adding or removing columns, changing data types, or renaming objects, unlike DML instructions. These activities are less common than data manipulation but necessary for schema evolution and maintenance.
ALTER can be used on many PostgreSQL objects, including:
ALTER TABLE: The PostgreSQL ALTER TABLE command is a basic Data Definition Language (DDL) operation that modifies the structure or definition of an existing table without recreating it and reloading the data, which is essential for evolving a database schema while preserving existing data.
ALTER DATABASE: Change database parameters, rename, or owner with ALTER DATABASE. For instance, ALTER DATABASE… CONNECTION LIMIT limits connections. User search_path can be set.
ALTER ROLE: Changes role attributes including LOGIN, CREATEDB, CREATEUSER, password, and expiration dates. Database cluster roles are global.
ALTER SCHEMA: To change the definition of an existing schema in PostgreSQL, use the ALTER SCHEMA command. Within a PostgreSQL instance, this command offers features for preserving the ownership and organisation of database items.
ALTER SEQUENCE: In PostgreSQL, the ALTER SEQUENCE command is used to change an existing sequence generator’s definition. Because of its versatility, a sequence’s behaviour and attributes can be controlled in a number of ways.
ALTER FUNCTION: In PostgreSQL, an existing user-defined function can have its definition changed using the ALTER FUNCTION command. By enabling developers and database administrators to control functions after they are created, this command makes database optimisation and maintenance easier.
ALTER TYPE: Changes user-defined composite, domain, and enumerated types. Composite type modifications affect dependent tables.
ALTER VIEW: In PostgreSQL, the ALTER VIEW command is used to change an existing view’s definition. Although the references presented mention ALTER VIEW as a SQL command that can be used, they do not specifically describe all of its parameters in the extracts that are provided.
ALTER INDEX: PostgreSQL can construct indexes implicitly when creating PRIMARY KEY or UNIQUE constraints on a table, or directly with the construct INDEX command. Indexes are physical database objects. PostgreSQL continues to select the best execution plans for queries when index properties are changed using ALTER INDEX, particularly for SELECT, UPDATE, and DELETE operations that benefit from indexed columns.
ALTER TRIGGER: Triggers are specifications that, when specific actions (such as INSERT, UPDATE, DELETE, or TRUNCATE) are carried out on a table or view, automatically carry out a given function. They may be set up to fire prior to, following, or in lieu of the surgery. Triggers can function once per SQL statement (FOR EACH STATEMENT) or once per row (FOR EACH ROW).
ALTER SYSTEM: In PostgreSQL 9.4, the ALTER SYSTEM command allows SQL-accessible changes to global configuration parameters in postgresql.conf. Changes to postgresql.auto.conf override postgresql.conf settings.
ALTER TABLESPACE: The CREATE TABLESPACE command creates tablespaces with a logical name and an empty directory owned by the PostgreSQL operating system user. They help move frequently accessed data to SSDs or when a disc partition is full. Pg default and pg global tablespaces are created automatically at cluster startup by PostgreSQL. Tablespaces might be used for new objects or temporary files.
ALTER PUBLICATION:Because other publications in the same database may disclose the same information, security mechanisms like row filters or column lists shouldn’t be depended upon exclusively. Psql’s \dRp+ meta-command, which displays defined row filters and column lists, can be used to view publications.
DROP
To permanently delete existing database objects in PostgreSQL, together with their definitions and, if relevant, the data they are connected with, use the DROP command. The removed object cannot be readily recovered because this action is usually irrevocable.
DROP can be used to eliminate different kinds of database objects:
Tables: DROP TABLE eliminates all of the data in the table as well as its structure. Secondary indices in a table are also eliminated.
Databases: A whole database can be deleted using DROP DATABASE. While you are connected to the database you want to drop, you cannot run this command. An easy way to run this command from the operating system command line is with the dropdb shell script.
Indexes: To delete an existing index from a database in PostgreSQL, use the DROP INDEX command. By enabling the server to locate and retrieve particular rows far more quickly than it could in the absence of an index, indexes physical database objects are defined on a table column or a list of columns to improve database speed.
Views: To delete an existing view from the database in PostgreSQL, use the DROP VIEW command. Views function as pseudo-tables that seem as regular tables for SELECT operations but are not physically materialised; they are merely queries or wrappers around SELECT statements. The definition of a view is deleted from the system catalogues when it is discarded.
Functions: PostgreSQL’s DROP FUNCTION command removes user-defined functions. The command syntax is DROP FUNCTION name ([ type [,…] ] ), where name is the function to drop and type is its parameter types. PostgreSQL enables function overloading, thus many functions can use the same name if their argument types are different. This requires explicit parameter type specification.
Roles/Users: A database role or user can be removed using DROP ROLE or DROP USER. Any items that belong to a role must be dropped or transferred to another owner before the role can be dropped. These dependencies can be cleaned up with the use of the REASSIGN OWNED and DROP OWNED commands. A command-line interface for dropping users is offered by the dropuser tool.
Schemas: PostgreSQL’s DROP structure command deletes a database structure. Schemas, like namespaces in programming languages, organise PostgreSQL database elements like tables, views, functions, aggregates, indexes, sequences, triggers, data types, domains, and ranges.
Triggers: PostgreSQL’s DROP TRIGGER command removes trigger definitions. Server-side triggers activate when a table or view receives a DML event like INSERT, UPDATE, DELETE, or TRUNCATE.
Types: The way that DROP TYPE handles dependencies is a crucial component. PostgreSQL requires the user to remove any other database objects that depend on the type being dropped, such as operators, functions, aggregates, access methods, subtypes, or classes.
Operators: PostgreSQL’s DROP OPERATOR command removes user-defined operators. PostgreSQL operators are “syntactic sugar” for underlying functions that do the job, hence they must have a CREATE FUNCTION-defined function.
Languages: DROP PROCEDURAL LANGUAGE, which is also accessible through droplang, eliminates a programming language from the database.
Sequences: A PostgreSQL sequence number generator can be removed using the DROP SEQUENCE command. Sequences, also referred to as sequence objects or generators, are named counters that are primarily used to generate unique identifiers for table rows, commonly for primary keys.
Tablespaces: PostgreSQL’s DROP TABLESPACE command deletes a tablespace. Database objects are stored on disc in a tablespace. DROP SEQUENCE name is the basic syntax, while DROP TABLESPACE name is more popular.
Constraints: To remove a particular constraint from a table, use ALTER TABLE… DROP CONSTRAINT. ALTER TABLE… ALTER COLUMN… DROP NOT NULL is used for a NOT NULL constraint.
PostgreSQL’s Extensibility and Tools for DDL
Catalog-driven PostgreSQL is extendable. In system catalogues (e.g., pg class, pg attribute, pg type, pg proc), PostgreSQL maintains object metadata instead of hardcoded processes, including data types, functions, and access methods. pg_index and operator). Users can query these catalogues to understand the database structure and develop new features using these internal specs. PostgreSQL GUIs and command-line tools execute DDL commands.
psql: The interactive terminal client, psql, executes SQL commands, including DDL, and gives meta-commands (e.g., \d for table description, \h for SQL help
Graphical Interfaces: Popular commercial tools like pgAdmin, DBeaver, PgManager, and RazorSQL provide graphical environments for database management and SQL programming, including DDL operations. Most installations lack GUIs. DBeaver manages PostgreSQL views, functions, sequences, data types, and more.
To conclude, PostgreSQL’s DDL language and allow database structure planning, construction, and modification while ensuring data integrity and flexibility for various applications.