DDL Syntax Overview

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

M compatibility does not support DDL operations when the CN is incomplete. For example, if a CN in the cluster is faulty, creating a database or a table will fail.

Defining a Role

A role is used to manage permissions. For database security, management and operation permissions can be granted to different roles. Table 1 lists the involved SQL statements.

**Table 1** SQL statements for defining a role
Function	SQL Statement
Creating a role	CREATE ROLE
Altering role attributes	ALTER ROLE
Dropping a role	DROP ROLE
Deleting the database objects owned by a database role	DROP OWNED

Defining a User

A user is used to log in to a database. Different permissions can be granted to users for managing data accesses and operations of the users. Table 2 lists the involved SQL statements.

**Table 2** SQL statements for defining a user
Function	SQL Statement
Creating a user	CREATE USER
Altering user attributes	ALTER USER
Dropping a user	DROP USER

Defining a Database or Schema

In M compatibility, database and schema are synonyms. A database or schema is the warehouse for organizing, storing, and managing data. Defining a database includes: creating a database, altering the database attributes, and dropping the database. Table 3 lists the involved SQL statements.

**Table 3** SQL statements for defining a database
Function	SQL Statement
Creating a database or schema	CREATE DATABASE and CREATE SCHEMA
Modifying a database or schema attribute	ALTER DATABASE and ALTER SCHEMA
Deleting a database or schema	DROP DATABASE and DROP SCHEMA
Specifying the current mode	USE

Defining a Table

A table is a special data structure in a database and is used to store data objects and relationship between data objects. Table 4 lists the involved SQL statements.

**Table 4** SQL statements for defining a table
Function	SQL Statement
Creating a table	CREATE TABLE
Altering table attributes	ALTER TABLE
Dropping a table	DROP TABLE

Defining a Partitioned Table

A partitioned table is a logical table used to improve query performance and does not store data (data is stored in ordinary tables). Table 5 lists the involved SQL statements.

**Table 5** SQL statements for defining a partitioned table
Function	SQL Statement
Creating a partitioned table	CREATE TABLE PARTITION
Create a partition	ALTER TABLE PARTITION
Altering partitioned table attributes	ALTER TABLE PARTITION
Dropping a partition	ALTER TABLE PARTITION
Dropping a partitioned table	DROP TABLE

Defining an Index

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

**Table 6** SQL statements for defining an index
Function	SQL Statement
Creating an index	CREATE INDEX
Altering index attributes	ALTER INDEX
Dropping an index	DROP INDEX
Rebuilding an index	REINDEX

Defining a Plug-in Extension

This feature is for internal use only. You are advised not to use it.

**Table 7** SQL statements for defining a plug-in extension
Function	SQL Statement
Creating a plug-in extension	CREATE EXTENSION
Modifying a plug-in extension	ALTER EXTENSION
Dropping a plug-in extension	DROP EXTENSION

Defining a User Group

**Table 8** SQL statements for defining a user group
Function	SQL Statement
Creating a user group	CREATE GROUP
Modifying attributes of a user group	ALTER GROUP
Deleting a user group	DROP GROUP

Defining a Node

**Table 9** SQL statements for defining a node
Function	SQL Statement
Creating a cluster node	CREATE NODE
Modifying the definition of an existing node	ALTER NODE
Deleting a node	DROP NODE

Defining a Node Group

**Table 10** SQL statements for defining a node group
Function	SQL Statement
Creating a cluster node group	CREATE NODE GROUP
Modifying information about an existing node group	ALTER NODE GROUP
Deleting a node group	DROP NODE GROUP

Defining a Sequence

**Table 11** SQL statements for defining a sequence
Function	SQL Statement
Adding a sequence to the current database	CREATE SEQUENCE
Modifying parameters of an existing sequence	ALTER SEQUENCE
Deleting a sequence from the current database	DROP SEQUENCE

Defining a Resource Label

**Table 12** SQL statements for defining a resource label
Function	SQL Statement
Creating a resource label	CREATE RESOURCE LABEL
Altering a resource label	ALTER RESOURCE LABEL
Dropping a resource label	DROP RESOURCE LABEL

Defining an Audit Policy

**Table 13** SQL statements for defining an audit policy
Function	SQL Statement
Creating a unified audit policy	CREATE AUDIT POLICY
Altering a unified audit policy	ALTER AUDIT POLICY
Dropping an audit policy	DROP AUDIT POLICY

Defining a View

**Table 14** SQL statements for defining a view
Function	SQL Statement
Creating a view	CREATE VIEW
Modifying the auxiliary attributes of a view	ALTER VIEW
Forcibly deleting a view from the database	DROP VIEW

Defining a Function

**Table 15** SQL statements for defining a function
Function	SQL Statement
Creating a function	CREATE FUNCTION
Deleting a function	DROP FUNCTION

Setting the nodeis_active Column of a Specified Node

ALTER COORDINATOR describes how to change the value of the nodeis_active column of a specified node in the pgxc_node system catalog.

Collecting Statistics

For details about how to collect statistics related to the contents of ordinary tables in the database, see ANALYZE.

Clearing a Recycle Bin

M compatibility provides statements for clearing a recycle bin. For details, see PURGE.

Defining an Object Comment

M compatibility supports statements for defining or modifying an object comment. For details, see COMMENT.

Creating a Table Based on Query Results

M compatibility supports statements for creating a table based on query results and inserting queried data into the target table. For details, see SELECT INTO.