Help Center/ GaussDB(DWS)/ SQL Syntax Reference (8.1.3.x)/ DDL Syntax/ DDL Syntax Overview

Updated on 2024-12-19 GMT+08:00

View PDF

DDL Syntax Overview

Data definition language (DDL) is used to define or modify an object in a database, such as a table, index, or view.

GaussDB(DWS) does not support DDL if its CN is unavailable. For example, if a CN in the cluster is faulty, creating a database or table will fail.

Defining a Database

A database is the warehouse for organizing, storing, and managing data. Defining a database includes: creating a database, altering the database attributes, and dropping the database. The following table lists the related SQL statements.

**Table 1** SQL statements for defining a database
Function	SQL Statement
Create a database	CREATE DATABASE
Alter database attributes	ALTER DATABASE
Delete a database	DROP DATABASE

Defining a Schema

A schema is the set of a group of database objects and is used to control the access to the database objects. The following table lists the related SQL statements.

**Table 2** SQL statements for defining a schema
Function	SQL Statement
Create a schema	CREATE SCHEMA
Alter schema attributes	ALTER SCHEMA
Delete a schema	DROP SCHEMA

Defining a Table

A table is a special data structure in a database and is used to store data objects and the relationship between data objects. The following table lists the related SQL statements.

**Table 3** SQL statements for defining a table
Function	SQL Statement
Create a table	CREATE TABLE
Alter table attributes	ALTER TABLE
Alter a table name	RENAME TABLE
Delete a table	DROP TABLE
Delete all the data from a table	TRUNCATE

Defining a Partitioned Table

A partitioned table is a special data structure in a database and is used to store data objects and the relationship between data objects. The following table lists the related SQL statements.

**Table 4** SQL statements for defining a partitioned table
Function	SQL Statement
Create a partitioned table	CREATE TABLE PARTITION
Editing a Partition	ALTER TABLE PARTITION
Alter partitioned table attributes	ALTER TABLE PARTITION
Delete a partition	ALTER TABLE PARTITION
Delete a partitioned table	DROP TABLE

Defining a Foreign Table

A foreign table is a logical table object. Its data is stored in an external storage service instead of in the database.

**Table 5** SQL statements for defining a foreign table
Function	SQL Statement
Creating a GDS foreign table	CREATE FOREIGN TABLE (for GDS Import and Export)
Creating an HDFS or OBS foreign table (manually create server).	CREATE FOREIGN TABLE (SQL on OBS or Hadoop)
Creating an OBS foreign table (default server)	CREATE FOREIGN TABLE (for OBS Import and Export)
Creating a foreign table for collaborative analysis	CREATE FOREIGN TABLE (SQL on other GaussDB(DWS))
Modifying a GDS foreign table	ALTER FOREIGN TABLE (GDS Import and Export)
HDFS foreign table and OBS foreign table	ALTER FOREIGN TABLE (for HDFS or OBS)
Modifying a foreign table for collaborative analysis	ALTER FOREIGN TABLE (SQL on other GaussDB(DWS))
Deleting a foreign table.	DROP FOREIGN TABLE

Defining an Index

An index indicates the sequence of values in one or more columns in the database table. The database index is a data structure that improves the speed of data access to specific information in a database table. The following table lists the related SQL statements.

**Table 6** SQL statements for defining an index
Function	SQL Statement
Create an index	CREATE INDEX
Alter index attributes	ALTER INDEX
Delete an index	DROP INDEX
Rebuild an index	REINDEX

Defining a Role

A role is used to manage rights. For database security, all management and operation rights can be assigned to different roles. The following table lists the related SQL statements.

**Table 7** SQL statements for defining a role
Function	SQL Statement
Create a role	CREATE ROLE
Alter role attributes	ALTER ROLE
Delete a role	DROP ROLE

Defining a User

A user is used to log in to a database. Different rights can be assigned to users for managing data accesses and operations of users. The following table lists the related SQL statements.

**Table 8** SQL statements for defining a user
Function	SQL Statement
Create a user	CREATE USER
Alter user attributes	ALTER USER
Delete a user	DROP USER

Defining a Redaction Policy

Data redaction is to protect sensitive data by masking or changing data. You can create a data redaction policy for a specific table object and specify the effective scope of the policy. You can also add, modify, and delete redaction columns. The following table lists the related SQL statements.

**Table 9** SQL statements for managing redaction policies
Function	SQL Statement
Create a data redaction policy	CREATE REDACTION POLICY
Modify a data redaction policy applied to a specified table	ALTER REDACTION POLICY
Delete a data redaction policy applied to a specified table	DROP REDACTION POLICY

Defining Row-Level Access Control

Row-level access control policies control the visibility of rows in database tables. In this way, the same SQL query may return different results for different users. The following table lists the related SQL statements.

**Table 10** SQL statements for row-level access control
Function	SQL Statement
Create a row-level access control policy	CREATE ROW LEVEL SECURITY POLICY
Modify an existing row-level access control policy	ALTER ROW LEVEL SECURITY POLICY
Delete a row-level access control policy from a table	DROP ROW LEVEL SECURITY POLICY

Defining a Stored Procedure

A stored procedure is a set of SQL statements for achieving specific functions and is stored in the database after compiling. Users can specify a name and provide parameters (if necessary) to execute the stored procedure. The following table lists the related SQL statements.

**Table 11** SQL statements for defining a stored procedure
Function	SQL Statement
Create a stored procedure	CREATE PROCEDURE
Delete a stored procedure	DROP PROCEDURE

Defining a Function

In GaussDB(DWS), a function is similar to a stored procedure, which is a set of SQL statements. The function and stored procedure are used the same. The following table lists the related SQL statements.

**Table 12** SQL statements for defining a function
Function	SQL Statement
Create a function	CREATE FUNCTION
Alter function attributes	ALTER FUNCTION
Delete a function	DROP FUNCTION

Defining a View

A view is a virtual table exported from one or several basic tables. The view is used to control data accesses for users. The following table lists the related SQL statements.

**Table 13** SQL statements for defining a view
Function	SQL Statement
Create a view	CREATE VIEW
Delete a view	DROP VIEW

Defining a Sequence

Sequences are database objects. You can generate unique integers from sequences.

**Table 14** SQL statements for defining a sequence
Function	SQL Statement
Creating a sequence	CREATE SEQUENCE
Modifying a sequence	ALTER SEQUENCE
Dropping a sequence	DROP SEQUENCE

Defining a Trigger

A trigger is a stored procedure that runs automatically when a table event happens. It responds to operations like insert, delete, and update on a table.

**Table 15** SQL statements for defining a trigger
Function	SQL Statement
Creating a trigger	CREATE TRIGGER
Modifying a trigger	ALTER TRIGGER
Delete the trigger.	DROP TRIGGER

Defining a Cursor

To process SQL statements, the stored procedure process assigns a memory segment to store context association. Cursors are handles or pointers to context regions. With a cursor, the stored procedure can control alterations in context areas.

**Table 16** SQL statements for defining a cursor
Function	SQL Statement
Create a cursor	CURSOR
Move a cursor	MOVE
Extract data from a cursor	FETCH
Close a cursor	CLOSE

Altering or Ending a Session

A session is a connection established between the user and the database. The following table lists the related SQL statements.

**Table 17** SQL statements related to sessions
Function	SQL Statement
Alter a session	ALTER SESSION
End a session	ALTER SYSTEM KILL SESSION

Defining a Resource Pool

A resource pool is a system catalog used by the resource load management module to specify attributes related to resource management, such as Cgroups. The following table lists the related SQL statements.

**Table 18** SQL statements for defining a resource pool
Function	SQL Statement
Create a resource pool	CREATE RESOURCE POOL
Change resource attributes	ALTER RESOURCE POOL
Delete a resource pool	DROP RESOURCE POOL

Defining Synonyms

A synonym is a special database object compatible with Oracle. It is used to store the mapping between a database object and another. Currently, only synonyms can be used to associate the following database objects: tables, views, functions, and stored procedures. The following table lists the related SQL statements.

**Table 19** SQL statements for managing synonyms
Function	SQL Statement
Creating a synonym	CREATE SYNONYM
Modifying a synonym	ALTER SYNONYM
Deleting a synonym	DROP SYNONYM

Defining Text Search Configuration

A text search configuration specifies a text search parser that can divide a string into tokens, plus dictionaries that can be used to determine which tokens are of interest for searching. The following table lists the related SQL statements.

**Table 20** SQL statements for configuring text search
Function	SQL Statement
Create a text search configuration	CREATE TEXT SEARCH CONFIGURATION
Modify a text search configuration	ALTER TEXT SEARCH CONFIGURATION
Delete a text search configuration	DROP TEXT SEARCH CONFIGURATION

Defining a Full-text Retrieval Dictionary

A dictionary is used to identify and process specific words during full-text retrieval. Dictionaries are created by using predefined templates (defined in the PG_TS_TEMPLATE system catalog). Five types of dictionaries can be created, Simple, Ispell, Synonym, Thesaurus, and Snowball. Each type of dictionaries is used to handle different tasks. The following table lists the related SQL statements.

**Table 21** SQL statements for a full-text search dictionary
Function	SQL Statement
Create a full-text retrieval dictionary	CREATE TEXT SEARCH DICTIONARY
Modify a full-text retrieval dictionary	ALTER TEXT SEARCH DICTIONARY
Delete a full-text retrieval dictionary	DROP TEXT SEARCH DICTIONARY