- About MogDB
- Quick Start
- Characteristic Description
- Overview
- High Performance
- CBO Optimizer
- LLVM
- Vectorized Engine
- Hybrid Row-Column Store
- Adaptive Compression
- Adaptive Two-phase Hash Aggregation
- SQL Bypass
- Kunpeng NUMA Architecture Optimization
- High Concurrency of Thread Pools
- SMP for Parallel Execution
- Xlog no Lock Flush
- Parallel Page-based Redo For Ustore
- Row-Store Execution to Vectorized Execution
- Astore Row Level Compression
- BTree Index Compression
- Tracing SQL Function
- Parallel Index Scan
- Parallel Query Optimization
- Enhancement of Tracing Backend Key Thread
- Ordering Operator Optimization
- OCK-accelerated Data Transmission
- OCK SCRLock Accelerate Distributed Lock
- Enhancement of WAL Redo Performance
- Enhancement of Dirty Pages Flushing Performance
- Sequential Scan Prefetch
- Ustore SMP Parallel Scanning
- High Availability (HA)
- Primary/Standby
- Logical Replication
- Logical Backup
- Physical Backup
- Automatic Job Retry upon Failure
- Ultimate RTO
- High Availability Based on the Paxos Protocol
- Cascaded Standby Server
- Delayed Replay
- Adding or Deleting a Standby Server
- Delaying Entering the Maximum Availability Mode
- Parallel Logical Decoding
- DCF
- CM(Cluster Manager)
- Global SysCache
- Using a Standby Node to Build a Standby Node
- Two City and Three Center DR
- CM Cluster Management Component Supporting Two Node Deployment
- Query of the Original DDL Statement for a View
- MogDB/CM/PTK Dual Network Segment Support
- Enhanced Efficiency of Logical Backup and Restore
- Maintainability
- Workload Diagnosis Report (WDR)
- Slow SQL Diagnosis
- Session Performance Diagnosis
- System KPI-aided Diagnosis
- Fault Diagnosis
- Extension Splitting
- Built-in Stack Tool
- SQL PATCH
- Lightweight Lock Export and Analysis
- DCF Module Tracing
- Error When Writing Illegal Characters
- Support For Pageinspect & Pagehack
- Autonomous Transaction Management View and Termination
- Corrupt Files Handling
- Compatibility
- Add %rowtype Attribute To The View
- Aggregate Functions Distinct Performance Optimization
- Aggregate Functions Support Keep Clause
- Aggregate Functions Support Scenario Extensions
- Compatible With MySQL Alias Support For Single Quotes
- current_date/current_time Keywords As Field Name
- Custom Type Array
- For Update Support Outer Join
- MogDB Supports Insert All
- Oracle DBLink Syntax Compatibility
- Remove Type Conversion Hint When Creating PACKAGE/FUNCTION/PROCEDURE
- Support Bypass Method When Merge Into Hit Index
- Support For Adding Nocopy Attributes To Procedure And Function Parameters
- Support For Passing The Count Attribute Of An Array As A Parameter Of The Array Extend
- Support Q Quote Escape Character
- Support Subtracting Two Date Types To Return Numeric Type
- Support table()
- Support To Keep The Same Name After The End With Oracle
- Support Where Current Of
- Support For Constants In Package As Default Values
- Support PLPGSQL subtype
- Support Synonym Calls Without Parentheses For Function Without Parameters
- Support For dbms_utility.format_error_backtrace
- Support for PIVOT and UNPIVOT Syntax
- Mod Function Compatibility
- Support for Nesting of Aggregate Functions
- ORDER BY/GROUP BY Scenario Expansion
- Support for Modifying Table Log Properties After Table Creation
- Support for INSERT ON CONFLICT Clause
- Support for AUTHID CURRENT_USER
- Support for Stored Procedure OUT Parameters in PBE Mode
- Database Security
- Access Control Model
- Separation of Control and Access Permissions
- Database Encryption Authentication
- Data Encryption and Storage
- Database Audit
- Network Communication Security
- Resource Label
- Unified Audit
- Dynamic Data Anonymization
- Row-Level Access Control
- Password Strength Verification
- Equality Query in a Fully-encrypted Database
- Ledger Database Mechanism
- Transparent Data Encryption
- Enterprise-Level Features
- Support for Functions and Stored Procedures
- SQL Hints
- Full-Text Indexing
- Copy Interface for Error Tolerance
- Partitioning
- Support for Advanced Analysis Functions
- Materialized View
- HyperLogLog
- Creating an Index Online
- Autonomous Transaction
- Global Temporary Table
- Pseudocolumn ROWNUM
- Stored Procedure Debugging
- JDBC Client Load Balancing and Read/Write Isolation
- In-place Update Storage Engine
- Publication-Subscription
- Foreign Key Lock Enhancement
- Data Compression in OLTP Scenarios
- Transaction Async Submit
- Index Creation Parallel Control
- Dynamic Partition Pruning
- COPY Import Optimization
- SQL Running Status Observation
- BRIN Index
- BLOOM Index
- Event Trigger
- Scrollable Cursor Support for Reverse Retrieval
- Support for Pruning Subquery Projection Columns
- Pruning ORDER BY in Subqueries
- Automatic Creation of Indexes Supporting Fuzzy Matching
- Support for Importing and Exporting Specific Objects
- Application Development Interfaces
- AI Capabilities
- Middleware
- Workload Management
- Installation Guide
- Upgrade Guide
- Administrator Guide
- Localization
- Routine Maintenance
- Starting and Stopping MogDB
- Using the gsql Client for Connection
- Routine Maintenance
- Checking OS Parameters
- Checking MogDB Health Status
- Checking Database Performance
- Checking and Deleting Logs
- Checking Time Consistency
- Checking The Number of Application Connections
- Routinely Maintaining Tables
- Routinely Recreating an Index
- Exporting and Viewing the WDR
- Data Security Maintenance Suggestions
- Slow SQL Diagnosis
- Log Reference
- Primary and Standby Management
- Column-store Tables Management
- Backup and Restoration
- Database Deployment Solutions
- Importing and Exporting Data
- High Available Guide
- AI Features Guide
- AI4DB: Autonomous Database O&M
- DBMind Mode
- Components that Support DBMind
- AI Sub-functions of the DBMind
- ABO Optimizer
- DB4AI: Database-driven AI
- AI4DB: Autonomous Database O&M
- Security Guide
- Developer Guide
- Application Development Guide
- Development Specifications
- Development Based on JDBC
- JDBC Package, Driver Class, and Environment Class
- Development Process
- Loading the Driver
- Connecting to a Database
- Connecting to the Database (Using SSL)
- Connecting to the Database (Using UDS)
- Running SQL Statements
- Processing Data in a Result Set
- Closing a Connection
- Managing Logs
- Example: Common Operations
- Example: Retrying SQL Queries for Applications
- Example: Importing and Exporting Data Through Local Files
- Example 2: Migrating Data from a MY Database to MogDB
- Example: Logic Replication Code
- Example: Parameters for Connecting to the Database in Different Scenarios
- Example: JDBC Primary/Standby Cluster Load Balancing
- JDBC API Reference
- java.sql.Connection
- java.sql.CallableStatement
- java.sql.DatabaseMetaData
- java.sql.Driver
- java.sql.PreparedStatement
- java.sql.ResultSet
- java.sql.ResultSetMetaData
- java.sql.Statement
- javax.sql.ConnectionPoolDataSource
- javax.sql.DataSource
- javax.sql.PooledConnection
- javax.naming.Context
- javax.naming.spi.InitialContextFactory
- CopyManager
- JDBC-based Common Parameter Reference
- JDBC Release Notes
- Development Based on ODBC
- Development Based on libpq
- Psycopg2-Based Development
- Commissioning
- Stored Procedure
- User Defined Functions
- PL/pgSQL-SQL Procedural Language
- Scheduled Jobs
- Autonomous Transaction
- Logical Replication
- Extension
- MySQL Compatibility Description
- Dolphin Extension
- Dolphin Overview
- Dolphin Installation
- Dolphin Restrictions
- Dolphin Syntax
- SQL Reference
- Keywords
- Data Types
- Functions and Operators
- Assignment Operators
- Character Processing Functions and Operators
- Arithmetic Functions and Operators
- Dolphin Lock
- Date and Time Processing Functions and Operators
- Advisory Lock Functions
- Network Address Functions and Operators
- Conditional Expression Functions
- Aggregate Functions
- System Information Functions
- Logical Operators
- Bit String Functions and Operators
- JSON-JSONB Functions and Operators
- Type Conversion Functions
- Compatible Operators and Operations
- Comment Operators
- Expressions
- DDL Syntax
- DML Syntax
- DCL Syntax
- SQL Syntax
- ALTER DATABASE
- ALTER FUNCTION
- ALTER PROCEDURE
- ALTER SERVER
- ALTER TABLE
- ALTER TABLE PARTITION
- ALTER TABLESPACE
- ALTER VIEW
- ANALYZE | ANALYSE
- AST
- CHECKSUM TABLE
- CREATE DATABASE
- CREATE FUNCTION
- CREATE INDEX
- CREATE PROCEDURE
- CREATE SERVER
- CREATE TABLE
- CREATE TABLE AS
- CREATE TABLE PARTITION
- CREATE TABLESPACE
- CREATE TRIGGER
- CREATE VIEW
- DESCRIBE TABLE
- DO
- DROP DATABASE
- DROP INDEX
- DROP TABLESPACE
- EXECUTE
- EXPLAIN
- FLUSH BINARY LOGS
- GRANT
- GRANT/REVOKE PROXY
- INSERT
- KILL
- LOAD DATA
- OPTIMIZE TABLE
- PREPARE
- RENAME TABLE
- RENAME USER
- REVOKE
- SELECT
- SELECT HINT
- SET CHARSET
- SET PASSWORD
- SHOW CHARACTER SET
- SHOW COLLATION
- SHOW COLUMNS
- SHOW CREATE DATABASE
- SHOW CREATE FUNCTION
- SHOW CREATE PROCEDURE
- SHOW CREATE TABLE
- SHOW CREATE TRIGGER
- SHOW CREATE VIEW
- SHOW DATABASES
- SHOW FUNCTION STATUS
- SHOW GRANTS
- SHOW INDEX
- SHOW MASTER STATUS
- SHOW PLUGINS
- SHOW PRIVILEGES
- SHOW PROCEDURE STATUS
- SHOW PROCESSLIST
- SHOW SLAVE HOSTS
- SHOW STATUS
- SHOW TABLES
- SHOW TABLE STATUS
- SHOW TRIGGERS
- SHOW VARIABLES
- SHOW WARNINGS/ERRORS
- UPDATE
- USE db_name
- System Views
- GUC Parameters
- Resetting Parameters
- Stored Procedures
- Identifiers
- SQL Reference
- MySQL Syntax Compatibility Assessment Tool
- Dolphin Extension
- Materialized View
- Partition Management
- Application Development Guide
- Performance Tuning Guide
- Reference Guide
- System Catalogs and System Views
- Overview
- Querying a System Catalog
- System Catalogs
- GS_ASP
- GS_AUDITING_POLICY
- GS_AUDITING_POLICY_ACCESS
- GS_AUDITING_POLICY_FILTERS
- GS_AUDITING_POLICY_PRIVILEGES
- GS_CLIENT_GLOBAL_KEYS
- GS_CLIENT_GLOBAL_KEYS_ARGS
- GS_COLUMN_KEYS
- GS_COLUMN_KEYS_ARGS
- GS_DB_PRIVILEGE
- GS_ENCRYPTED_COLUMNS
- GS_ENCRYPTED_PROC
- GS_GLOBAL_CHAIN
- GS_GLOBAL_CONFIG
- GS_MASKING_POLICY
- GS_MASKING_POLICY_ACTIONS
- GS_MASKING_POLICY_FILTERS
- GS_MATVIEW
- GS_MATVIEW_DEPENDENCY
- GS_MODEL_WAREHOUSE
- GS_OPT_MODEL
- GS_PACKAGE
- GS_POLICY_LABEL
- GS_RECYCLEBIN
- GS_TXN_SNAPSHOT
- GS_UID
- GS_WLM_EC_OPERATOR_INFO
- GS_WLM_INSTANCE_HISTORY
- GS_WLM_OPERATOR_INFO
- GS_WLM_PLAN_ENCODING_TABLE
- GS_WLM_PLAN_OPERATOR_INFO
- GS_WLM_SESSION_QUERY_INFO_ALL
- GS_WLM_USER_RESOURCE_HISTORY
- PG_AGGREGATE
- PG_AM
- PG_AMOP
- PG_AMPROC
- PG_APP_WORKLOADGROUP_MAPPING
- PG_ATTRDEF
- PG_ATTRIBUTE
- PG_AUTH_HISTORY
- PG_AUTH_MEMBERS
- PG_AUTHID
- PG_CAST
- PG_CLASS
- PG_COLLATION
- PG_CONSTRAINT
- PG_CONVERSION
- PG_DATABASE
- PG_DB_ROLE_SETTING
- PG_DEFAULT_ACL
- PG_DEPEND
- PG_DESCRIPTION
- PG_DIRECTORY
- PG_ENUM
- PG_EVENT_TRIGGER
- PG_EXTENSION
- PG_EXTENSION_DATA_SOURCE
- PG_FOREIGN_DATA_WRAPPER
- PG_FOREIGN_SERVER
- PG_FOREIGN_TABLE
- PG_HASHBUCKET
- PG_INDEX
- PG_INHERITS
- PG_JOB
- PG_JOB_PROC
- PG_LANGUAGE
- PG_LARGEOBJECT
- PG_LARGEOBJECT_METADATA
- PG_NAMESPACE
- PG_OBJECT
- PG_OPCLASS
- PG_OPERATOR
- PG_OPFAMILY
- PG_PARTITION
- PG_PLTEMPLATE
- PG_PROC
- PG_PUBLICATION
- PG_PUBLICATION_REL
- PG_RANGE
- PG_REPLICATION_ORIGIN
- PG_RESOURCE_POOL
- PG_REWRITE
- PG_RLSPOLICY
- PG_SECLABEL
- PG_SET
- PG_SHDEPEND
- PG_SHDESCRIPTION
- PG_SHSECLABEL
- PG_STATISTIC
- PG_STATISTIC_EXT
- PG_SUBSCRIPTION
- PG_SUBSCRIPTION_REL
- PG_SYNONYM
- PG_TABLESPACE
- PG_TRIGGER
- PG_TS_CONFIG
- PG_TS_CONFIG_MAP
- PG_TS_DICT
- PG_TS_PARSER
- PG_TS_TEMPLATE
- PG_TYPE
- PG_USER_MAPPING
- PG_USER_STATUS
- PG_WORKLOAD_GROUP
- PGXC_CLASS
- PGXC_GROUP
- PGXC_NODE
- PGXC_SLICE
- PLAN_TABLE_DATA
- STATEMENT_HISTORY
- System Views
- GET_GLOBAL_PREPARED_XACTS(Discarded)
- GS_ASYNC_SUBMIT_SESSIONS_STATUS
- GS_AUDITING
- GS_AUDITING_ACCESS
- GS_AUDITING_PRIVILEGE
- GS_CLUSTER_RESOURCE_INFO
- GS_COMPRESSION
- GS_DB_PRIVILEGES
- GS_FILE_STAT
- GS_GSC_MEMORY_DETAIL
- GS_INSTANCE_TIME
- GS_LABELS
- GS_LSC_MEMORY_DETAIL
- GS_MASKING
- GS_MATVIEWS
- GS_OS_RUN_INFO
- GS_REDO_STAT
- GS_SESSION_CPU_STATISTICS
- GS_SESSION_MEMORY
- GS_SESSION_MEMORY_CONTEXT
- GS_SESSION_MEMORY_DETAIL
- GS_SESSION_MEMORY_STATISTICS
- GS_SESSION_STAT
- GS_SESSION_TIME
- GS_SHARED_MEMORY_DETAIL
- GS_SQL_COUNT
- GS_STAT_SESSION_CU
- GS_THREAD_MEMORY_CONTEXT
- GS_TOTAL_MEMORY_DETAIL
- GS_WLM_CGROUP_INFO
- GS_WLM_EC_OPERATOR_STATISTICS
- GS_WLM_OPERATOR_HISTORY
- GS_WLM_OPERATOR_STATISTICS
- GS_WLM_PLAN_OPERATOR_HISTORY
- GS_WLM_REBUILD_USER_RESOURCE_POOL
- GS_WLM_RESOURCE_POOL
- GS_WLM_SESSION_HISTORY
- GS_WLM_SESSION_INFO
- GS_WLM_SESSION_INFO_ALL
- GS_WLM_SESSION_STATISTICS
- GS_WLM_USER_INFO
- IOS_STATUS
- MPP_TABLES
- PG_AVAILABLE_EXTENSION_VERSIONS
- PG_AVAILABLE_EXTENSIONS
- PG_COMM_DELAY
- PG_COMM_RECV_STREAM
- PG_COMM_SEND_STREAM
- PG_COMM_STATUS
- PG_CONTROL_GROUP_CONFIG
- PG_CURSORS
- PG_EXT_STATS
- PG_GET_INVALID_BACKENDS
- PG_GET_SENDERS_CATCHUP_TIME
- PG_GROUP
- PG_GTT_ATTACHED_PIDS
- PG_GTT_RELSTATS
- PG_GTT_STATS
- PG_INDEXES
- PG_LOCKS
- PG_NODE_ENV
- PG_OS_THREADS
- PG_PREPARED_STATEMENTS
- PG_PREPARED_XACTS
- PG_PUBLICATION_TABLES
- PG_REPLICATION_ORIGIN_STATUS
- PG_REPLICATION_SLOTS
- PG_RLSPOLICIES
- PG_ROLES
- PG_RULES
- PG_RUNNING_XACTS
- PG_SECLABELS
- PG_SESSION_IOSTAT
- PG_SESSION_WLMSTAT
- PG_SETTINGS
- PG_SHADOW
- PG_STAT_ACTIVITY
- PG_STAT_ACTIVITY_NG
- PG_STAT_ALL_INDEXES
- PG_STAT_ALL_TABLES
- PG_STAT_BAD_BLOCK
- PG_STAT_BGWRITER
- PG_STAT_DATABASE
- PG_STAT_DATABASE_CONFLICTS
- PG_STAT_REPLICATION
- PG_STAT_SUBSCRIPTION
- PG_STAT_SYS_INDEXES
- PG_STAT_SYS_TABLES
- PG_STAT_USER_FUNCTIONS
- PG_STAT_USER_INDEXES
- PG_STAT_USER_TABLES
- PG_STAT_XACT_ALL_TABLES
- PG_STAT_XACT_SYS_TABLES
- PG_STAT_XACT_USER_FUNCTIONS
- PG_STAT_XACT_USER_TABLES
- PG_STATIO_ALL_INDEXES
- PG_STATIO_ALL_SEQUENCES
- PG_STATIO_ALL_TABLES
- PG_STATIO_SYS_INDEXES
- PG_STATIO_SYS_SEQUENCES
- PG_STATIO_SYS_TABLES
- PG_STATIO_USER_INDEXES
- PG_STATIO_USER_SEQUENCES
- PG_STATIO_USER_TABLES
- PG_STATS
- PG_TABLES
- PG_TDE_INFO
- PG_THREAD_WAIT_STATUS
- PG_TIMEZONE_ABBREVS
- PG_TIMEZONE_NAMES
- PG_TOTAL_MEMORY_DETAIL
- PG_TOTAL_USER_RESOURCE_INFO
- PG_TOTAL_USER_RESOURCE_INFO_OID
- PG_USER
- PG_USER_MAPPINGS
- PG_VARIABLE_INFO
- PG_VIEWS
- PG_WLM_STATISTICS
- PGXC_PREPARED_XACTS
- PLAN_TABLE
- PATCH_INFORMATION_TABLE
- Functions and Operators
- Logical Operators
- Comparison Operators
- Character Processing Functions and Operators
- Binary String Functions and Operators
- Bit String Functions and Operators
- Mode Matching Operators
- Mathematical Functions and Operators
- Date and Time Processing Functions and Operators
- Type Conversion Functions
- Geometric Functions and Operators
- Network Address Functions and Operators
- Text Search Functions and Operators
- JSON/JSONB Functions and Operators
- HLL Functions and Operators
- SEQUENCE Functions
- Array Functions and Operators
- Range Functions and Operators
- Aggregate Functions
- Window Functions(Analysis Functions)
- Security Functions
- Ledger Database Functions
- Encrypted Equality Functions
- Set Returning Functions
- Conditional Expression Functions
- System Information Functions
- System Administration Functions
- Configuration Settings Functions
- Universal File Access Functions
- Server Signal Functions
- Backup and Restoration Control Functions
- Snapshot Synchronization Functions
- Database Object Functions
- Advisory Lock Functions
- Logical Replication Functions
- Segment-Page Storage Functions
- Other Functions
- Undo System Functions
- Row-store Compression System Functions
- Statistics Information Functions
- Trigger Functions
- Event Trigger Functions
- Hash Function
- Prompt Message Function
- Global Temporary Table Functions
- Fault Injection System Function
- AI Feature Functions
- Dynamic Data Masking Functions
- Other System Functions
- Internal Functions
- Global SysCache Feature Functions
- Data Damage Detection and Repair Functions
- XML Functions
- Obsolete Functions
- Supported Data Types
- SQL Syntax
- ABORT
- ALTER AGGREGATE
- ALTER AUDIT POLICY
- ALTER DATABASE
- ALTER DATA SOURCE
- ALTER DEFAULT PRIVILEGES
- ALTER DIRECTORY
- ALTER EVENT
- ALTER EVENT TRIGGER
- ALTER EXTENSION
- ALTER FOREIGN DATA WRAPPER
- ALTER FOREIGN TABLE
- ALTER FUNCTION
- ALTER GLOBAL CONFIGURATION
- ALTER GROUP
- ALTER INDEX
- ALTER LANGUAGE
- ALTER LARGE OBJECT
- ALTER MASKING POLICY
- ALTER MATERIALIZED VIEW
- ALTER OPERATOR
- ALTER PACKAGE
- ALTER PROCEDURE
- ALTER PUBLICATION
- ALTER RESOURCE LABEL
- ALTER RESOURCE POOL
- ALTER ROLE
- ALTER ROW LEVEL SECURITY POLICY
- ALTER RULE
- ALTER SCHEMA
- ALTER SEQUENCE
- ALTER SERVER
- ALTER SESSION
- ALTER SUBSCRIPTION
- ALTER SYNONYM
- ALTER SYSTEM KILL SESSION
- ALTER SYSTEM SET
- ALTER TABLE
- ALTER TABLE PARTITION
- ALTER TABLE SUBPARTITION
- ALTER TABLESPACE
- ALTER TEXT SEARCH CONFIGURATION
- ALTER TEXT SEARCH DICTIONARY
- ALTER TRIGGER
- ALTER TYPE
- ALTER USER
- ALTER USER MAPPING
- ALTER VIEW
- ANALYZE | ANALYSE
- BEGIN
- CALL
- CHECKPOINT
- CLEAN CONNECTION
- CLOSE
- CLUSTER
- COMMENT
- COMMIT | END
- COMMIT PREPARED
- CONNECT BY
- COPY
- CREATE AGGREGATE
- CREATE AUDIT POLICY
- CREATE CAST
- CREATE CLIENT MASTER KEY
- CREATE COLUMN ENCRYPTION KEY
- CREATE DATABASE
- CREATE DATA SOURCE
- CREATE DIRECTORY
- CREATE EVENT
- CREATE EVENT TRIGGER
- CREATE EXTENSION
- CREATE FOREIGN DATA WRAPPER
- CREATE FOREIGN TABLE
- CREATE FUNCTION
- CREATE GROUP
- CREATE INCREMENTAL MATERIALIZED VIEW
- CREATE INDEX
- CREATE LANGUAGE
- CREATE MASKING POLICY
- CREATE MATERIALIZED VIEW
- CREATE MODEL
- CREATE OPERATOR
- CREATE PACKAGE
- CREATE PROCEDURE
- CREATE PUBLICATION
- CREATE RESOURCE LABEL
- CREATE RESOURCE POOL
- CREATE ROLE
- CREATE ROW LEVEL SECURITY POLICY
- CREATE RULE
- CREATE SCHEMA
- CREATE SEQUENCE
- CREATE SERVER
- CREATE SUBSCRIPTION
- CREATE SYNONYM
- CREATE TABLE
- CREATE TABLE AS
- CREATE TABLE PARTITION
- CREATE TABLESPACE
- CREATE TABLE SUBPARTITION
- CREATE TEXT SEARCH CONFIGURATION
- CREATE TEXT SEARCH DICTIONARY
- CREATE TRIGGER
- CREATE TYPE
- CREATE USER
- CREATE USER MAPPING
- CREATE VIEW
- CREATE WEAK PASSWORD DICTIONARY
- CURSOR
- DEALLOCATE
- DECLARE
- DELETE
- DELIMITER
- DO
- DROP AGGREGATE
- DROP AUDIT POLICY
- DROP CAST
- DROP CLIENT MASTER KEY
- DROP COLUMN ENCRYPTION KEY
- DROP DATABASE
- DROP DATA SOURCE
- DROP DIRECTORY
- DROP EVENT
- DROP EVENT TRIGGER
- DROP EXTENSION
- DROP FOREIGN DATA WRAPPER
- DROP FOREIGN TABLE
- DROP FUNCTION
- DROP GLOBAL CONFIGURATION
- DROP GROUP
- DROP INDEX
- DROP LANGUAGE
- DROP MASKING POLICY
- DROP MATERIALIZED VIEW
- DROP MODEL
- DROP OPERATOR
- DROP OWNED
- DROP PACKAGE
- DROP PROCEDURE
- DROP PUBLICATION
- DROP RESOURCE LABEL
- DROP RESOURCE POOL
- DROP ROLE
- DROP ROW LEVEL SECURITY POLICY
- DROP RULE
- DROP SCHEMA
- DROP SEQUENCE
- DROP SERVER
- DROP SUBSCRIPTION
- DROP SYNONYM
- DROP TABLE
- DROP TABLESPACE
- DROP TEXT SEARCH CONFIGURATION
- DROP TEXT SEARCH DICTIONARY
- DROP TRIGGER
- DROP TYPE
- DROP USER
- DROP USER MAPPING
- DROP VIEW
- DROP WEAK PASSWORD DICTIONARY
- EXECUTE
- EXECUTE DIRECT
- EXPLAIN
- EXPLAIN PLAN
- FETCH
- GRANT
- INSERT
- LOCK
- MERGE INTO
- MOVE
- PREDICT BY
- PREPARE
- PREPARE TRANSACTION
- PURGE
- REASSIGN OWNED
- REFRESH INCREMENTAL MATERIALIZED VIEW
- REFRESH MATERIALIZED VIEW
- REINDEX
- RELEASE SAVEPOINT
- RESET
- REVOKE
- ROLLBACK
- ROLLBACK PREPARED
- ROLLBACK TO SAVEPOINT
- SAVEPOINT
- SELECT
- SELECT INTO
- SET
- SET CONSTRAINTS
- SET ROLE
- SET SESSION AUTHORIZATION
- SET TRANSACTION
- SHOW
- SHOW EVENTS
- SHRINK
- SHUTDOWN
- SNAPSHOT
- START TRANSACTION
- TIMECAPSULE TABLE
- TRUNCATE
- UPDATE
- VACUUM
- VALUES
- SQL Reference
- MogDB SQL
- Keywords
- Constant and Macro
- Expressions
- Type Conversion
- Full Text Search
- System Operation
- DDL Syntax Overview
- DML Syntax Overview
- DCL Syntax Overview
- Subquery
- LLVM
- Alias
- Lock
- Transaction
- Ordinary Table
- Partitioned Table
- Index
- Constraints
- Cursors
- Anonymous Block
- Trigger
- INSERT_RIGHT_REF_DEFAULT_VALUE
- Appendix
- GUC Parameters
- GUC Parameter Usage
- GUC Parameter List
- File Location
- Connection and Authentication
- Resource Consumption
- Write Ahead Log
- HA Replication
- Query Planning
- Error Reporting and Logging
- Alarm Detection
- Statistics During the Database Running
- Load Management
- Automatic Vacuuming
- Default Settings of Client Connection
- Lock Management
- Version and Platform Compatibility
- Faut Tolerance
- Connection Pool Parameters
- MogDB Transaction
- Replication Parameters of Two Database Instances
- Developer Options
- Auditing
- CM Parameters
- Backend Compression
- Upgrade Parameters
- Miscellaneous Parameters
- Wait Events
- Query
- System Performance Snapshot
- Security Configuration
- Global Temporary Table
- HyperLogLog
- Scheduled Task
- Thread Pool
- User-defined Functions
- Backup and Restoration
- DCF Parameters Settings
- Flashback
- Rollback Parameters
- Reserved Parameters
- AI Features
- Global SysCache Parameters
- Multi-Level Cache Management Parameters
- Resource Pooling Parameters
- Parameters Related to Efficient Data Compression Algorithms
- Writer Statement Parameters Supported by Standby Servers
- Data Import and Export
- Delimiter
- Appendix
- Schema
- Information Schema
- DBE_PERF
- OS
- Instance
- Memory
- File
- Object
- STAT_USER_TABLES
- SUMMARY_STAT_USER_TABLES
- GLOBAL_STAT_USER_TABLES
- STAT_USER_INDEXES
- SUMMARY_STAT_USER_INDEXES
- GLOBAL_STAT_USER_INDEXES
- STAT_SYS_TABLES
- SUMMARY_STAT_SYS_TABLES
- GLOBAL_STAT_SYS_TABLES
- STAT_SYS_INDEXES
- SUMMARY_STAT_SYS_INDEXES
- GLOBAL_STAT_SYS_INDEXES
- STAT_ALL_TABLES
- SUMMARY_STAT_ALL_TABLES
- GLOBAL_STAT_ALL_TABLES
- STAT_ALL_INDEXES
- SUMMARY_STAT_ALL_INDEXES
- GLOBAL_STAT_ALL_INDEXES
- STAT_DATABASE
- SUMMARY_STAT_DATABASE
- GLOBAL_STAT_DATABASE
- STAT_DATABASE_CONFLICTS
- SUMMARY_STAT_DATABASE_CONFLICTS
- GLOBAL_STAT_DATABASE_CONFLICTS
- STAT_XACT_ALL_TABLES
- SUMMARY_STAT_XACT_ALL_TABLES
- GLOBAL_STAT_XACT_ALL_TABLES
- STAT_XACT_SYS_TABLES
- SUMMARY_STAT_XACT_SYS_TABLES
- GLOBAL_STAT_XACT_SYS_TABLES
- STAT_XACT_USER_TABLES
- SUMMARY_STAT_XACT_USER_TABLES
- GLOBAL_STAT_XACT_USER_TABLES
- STAT_XACT_USER_FUNCTIONS
- SUMMARY_STAT_XACT_USER_FUNCTIONS
- GLOBAL_STAT_XACT_USER_FUNCTIONS
- STAT_BAD_BLOCK
- SUMMARY_STAT_BAD_BLOCK
- GLOBAL_STAT_BAD_BLOCK
- STAT_USER_FUNCTIONS
- SUMMARY_STAT_USER_FUNCTIONS
- GLOBAL_STAT_USER_FUNCTIONS
- Workload
- Session/Thread
- SESSION_STAT
- GLOBAL_SESSION_STAT
- SESSION_TIME
- GLOBAL_SESSION_TIME
- SESSION_MEMORY
- GLOBAL_SESSION_MEMORY
- SESSION_MEMORY_DETAIL
- GLOBAL_SESSION_MEMORY_DETAIL
- SESSION_STAT_ACTIVITY
- GLOBAL_SESSION_STAT_ACTIVITY
- THREAD_WAIT_STATUS
- GLOBAL_THREAD_WAIT_STATUS
- LOCAL_THREADPOOL_STATUS
- GLOBAL_THREADPOOL_STATUS
- SESSION_CPU_RUNTIME
- SESSION_MEMORY_RUNTIME
- STATEMENT_IOSTAT_COMPLEX_RUNTIME
- LOCAL_ACTIVE_SESSION
- Transaction
- Query
- STATEMENT
- SUMMARY_STATEMENT
- STATEMENT_COUNT
- GLOBAL_STATEMENT_COUNT
- SUMMARY_STATEMENT_COUNT
- GLOBAL_STATEMENT_COMPLEX_HISTORY
- GLOBAL_STATEMENT_COMPLEX_HISTORY_TABLE
- GLOBAL_STATEMENT_COMPLEX_RUNTIME
- STATEMENT_RESPONSETIME_PERCENTILE
- STATEMENT_COMPLEX_RUNTIME
- STATEMENT_COMPLEX_HISTORY_TABLE
- STATEMENT_COMPLEX_HISTORY
- STATEMENT_WLMSTAT_COMPLEX_RUNTIME
- STATEMENT_HISTORY
- Cache/IO
- STATIO_USER_TABLES
- SUMMARY_STATIO_USER_TABLES
- GLOBAL_STATIO_USER_TABLES
- STATIO_USER_INDEXES
- SUMMARY_STATIO_USER_INDEXES
- GLOBAL_STATIO_USER_INDEXES
- STATIO_USER_SEQUENCES
- SUMMARY_STATIO_USER_SEQUENCES
- GLOBAL_STATIO_USER_SEQUENCES
- STATIO_SYS_TABLES
- SUMMARY_STATIO_SYS_TABLES
- GLOBAL_STATIO_SYS_TABLES
- STATIO_SYS_INDEXES
- SUMMARY_STATIO_SYS_INDEXES
- GLOBAL_STATIO_SYS_INDEXES
- STATIO_SYS_SEQUENCES
- SUMMARY_STATIO_SYS_SEQUENCES
- GLOBAL_STATIO_SYS_SEQUENCES
- STATIO_ALL_TABLES
- SUMMARY_STATIO_ALL_TABLES
- GLOBAL_STATIO_ALL_TABLES
- STATIO_ALL_INDEXES
- SUMMARY_STATIO_ALL_INDEXES
- GLOBAL_STATIO_ALL_INDEXES
- STATIO_ALL_SEQUENCES
- SUMMARY_STATIO_ALL_SEQUENCES
- GLOBAL_STATIO_ALL_SEQUENCES
- GLOBAL_STAT_DB_CU
- GLOBAL_STAT_SESSION_CU
- Utility
- REPLICATION_STAT
- GLOBAL_REPLICATION_STAT
- REPLICATION_SLOTS
- GLOBAL_REPLICATION_SLOTS
- BGWRITER_STAT
- GLOBAL_BGWRITER_STAT
- GLOBAL_CKPT_STATUS
- GLOBAL_DOUBLE_WRITE_STATUS
- GLOBAL_PAGEWRITER_STATUS
- GLOBAL_RECORD_RESET_TIME
- GLOBAL_REDO_STATUS
- GLOBAL_RECOVERY_STATUS
- CLASS_VITAL_INFO
- USER_LOGIN
- SUMMARY_USER_LOGIN
- GLOBAL_GET_BGWRITER_STATUS
- GLOBAL_SINGLE_FLUSH_DW_STATUS
- GLOBAL_CANDIDATE_STATUS
- Lock
- Wait Events
- Configuration
- Operator
- Workload Manager
- Global Plancache
- RTO
- DBE_PLDEBUGGER Schema
- DBE_PLDEBUGGER.turn_on
- DBE_PLDEBUGGER.turn_off
- DBE_PLDEBUGGER.local_debug_server_info
- DBE_PLDEBUGGER.attach
- DBE_PLDEBUGGER.info_locals
- DBE_PLDEBUGGER.next
- DBE_PLDEBUGGER.continue
- DBE_PLDEBUGGER.abort
- DBE_PLDEBUGGER.print_var
- DBE_PLDEBUGGER.info_code
- DBE_PLDEBUGGER.step
- DBE_PLDEBUGGER.add_breakpoint
- DBE_PLDEBUGGER.delete_breakpoint
- DBE_PLDEBUGGER.info_breakpoints
- DBE_PLDEBUGGER.backtrace
- DBE_PLDEBUGGER.disable_breakpoint
- DBE_PLDEBUGGER.enable_breakpoint
- DBE_PLDEBUGGER.finish
- DBE_PLDEBUGGER.set_var
- DB4AI Schema
- DBE_PLDEVELOPER
- DBE_SQL_UTIL Schema
- Tool Reference
- Tool Overview
- Client Tool
- Server Tools
- Tools Used in the Internal System
- dsscmd
- dssserver
- mogdb
- gs_backup
- gs_basebackup
- gs_ctl
- gs_initdb
- gs_install
- gs_postuninstall
- gs_preinstall
- gs_sshexkey
- gs_tar
- gs_uninstall
- gs_upgradectl
- gs_expansion
- gs_dropnode
- gs_probackup
- gstrace
- kdb5_util
- kadmin.local
- kinit
- klist
- krb5kdc
- kdestroy
- pg_config
- pg_controldata
- pg_recvlogical
- pg_resetxlog
- pg_archivecleanup
- pssh
- pscp
- transfer.py
- FAQ
- Functions of MogDB Executable Scripts
- System Catalogs and Views Supported by gs_collector
- Error Code Reference
- Description of SQL Error Codes
- Third-Party Library Error Codes
- GAUSS-00001 - GAUSS-00100
- GAUSS-00101 - GAUSS-00200
- GAUSS 00201 - GAUSS 00300
- GAUSS 00301 - GAUSS 00400
- GAUSS 00401 - GAUSS 00500
- GAUSS 00501 - GAUSS 00600
- GAUSS 00601 - GAUSS 00700
- GAUSS 00701 - GAUSS 00800
- GAUSS 00801 - GAUSS 00900
- GAUSS 00901 - GAUSS 01000
- GAUSS 01001 - GAUSS 01100
- GAUSS 01101 - GAUSS 01200
- GAUSS 01201 - GAUSS 01300
- GAUSS 01301 - GAUSS 01400
- GAUSS 01401 - GAUSS 01500
- GAUSS 01501 - GAUSS 01600
- GAUSS 01601 - GAUSS 01700
- GAUSS 01701 - GAUSS 01800
- GAUSS 01801 - GAUSS 01900
- GAUSS 01901 - GAUSS 02000
- GAUSS 02001 - GAUSS 02100
- GAUSS 02101 - GAUSS 02200
- GAUSS 02201 - GAUSS 02300
- GAUSS 02301 - GAUSS 02400
- GAUSS 02401 - GAUSS 02500
- GAUSS 02501 - GAUSS 02600
- GAUSS 02601 - GAUSS 02700
- GAUSS 02701 - GAUSS 02800
- GAUSS 02801 - GAUSS 02900
- GAUSS 02901 - GAUSS 03000
- GAUSS 03001 - GAUSS 03100
- GAUSS 03101 - GAUSS 03200
- GAUSS 03201 - GAUSS 03300
- GAUSS 03301 - GAUSS 03400
- GAUSS 03401 - GAUSS 03500
- GAUSS 03501 - GAUSS 03600
- GAUSS 03601 - GAUSS 03700
- GAUSS 03701 - GAUSS 03800
- GAUSS 03801 - GAUSS 03900
- GAUSS 03901 - GAUSS 04000
- GAUSS 04001 - GAUSS 04100
- GAUSS 04101 - GAUSS 04200
- GAUSS 04201 - GAUSS 04300
- GAUSS 04301 - GAUSS 04400
- GAUSS 04401 - GAUSS 04500
- GAUSS 04501 - GAUSS 04600
- GAUSS 04601 - GAUSS 04700
- GAUSS 04701 - GAUSS 04800
- GAUSS 04801 - GAUSS 04900
- GAUSS 04901 - GAUSS 05000
- GAUSS 05001 - GAUSS 05100
- GAUSS 05101 - GAUSS 05200
- GAUSS 05201 - GAUSS 05300
- GAUSS 05301 - GAUSS 05400
- GAUSS 05401 - GAUSS 05500
- GAUSS 05501 - GAUSS 05600
- GAUSS 05601 - GAUSS 05700
- GAUSS 05701 - GAUSS 05800
- GAUSS 05801 - GAUSS 05900
- GAUSS 05901 - GAUSS 06000
- GAUSS 06001 - GAUSS 06100
- GAUSS 06101 - GAUSS 06200
- GAUSS 06201 - GAUSS 06300
- GAUSS 06301 - GAUSS 06400
- GAUSS 06401 - GAUSS 06500
- GAUSS 06501 - GAUSS 06600
- GAUSS 06601 - GAUSS 06700
- GAUSS 06701 - GAUSS 06800
- GAUSS 06801 - GAUSS 06900
- GAUSS 06901 - GAUSS 07000
- GAUSS 07001 - GAUSS 07100
- GAUSS 07101 - GAUSS 07200
- GAUSS 07201 - GAUSS 07300
- GAUSS 07301 - GAUSS 07400
- GAUSS 07401 - GAUSS 07500
- GAUSS 50000 - GAUSS 50999
- GAUSS 51000 - GAUSS 51999
- GAUSS 52000 - GAUSS 52999
- GAUSS 53000 - GAUSS 53699
- Error Log Reference
- System Catalogs and System Views
- Common Faults and Identification
- Common Fault Locating Methods
- Common Fault Locating Cases
- Core Fault Locating
- Permission/Session/Data Type Fault Location
- Service/High Availability/Concurrency Fault Location
- Standby Node in the Need Repair (WAL) State
- Service Startup Failure
- Primary Node Is Hung in Demoting During a Switchover
- "too many clients already" Is Reported or Threads Failed To Be Created in High Concurrency Scenarios
- Performance Deterioration Caused by Dirty Page Flushing Efficiency During TPCC High Concurrentcy Long Term Stable Running
- A Query Error Is Reported Due to Predicate Pushdown
- Table/Partition Table Fault Location
- File System/Disk/Memory Fault Location
- After You Run the du Command to Query Data File Size In the XFS File System, the Query Result Is Greater than the Actual File Size
- File Is Damaged in the XFS File System
- Insufficient Memory
- "Error:No space left on device" Is Displayed
- When the TPC-C is running and a disk to be injected is full, the TPC-C stops responding
- Disk Space Usage Reaches the Threshold and the Database Becomes Read-only
- Shared Memory Leakage
- SQL Fault Location
- Index Fault Location
- CM Two-Node Fault Location
- Source Code Parsing
- FAQs
- Glossary
- Communication Matrix
- Mogeaver
ALTER TABLE
Function
ALTER TABLE modifies tables, including modifying table definitions, renaming tables, renaming specified columns in tables, renaming table constraints, setting table schemas, enabling or disabling row-level security policies, and adding or updating multiple columns.
Precautions
-
The owner of a table, users granted with the ALTER permission on the table, or users granted with the ALTER ANY TABLE permission can run the ALTER TABLE statement. The system administrator has the permission to run the command by default. To modify the owner or schema of a table, you must be the table owner or system administrator and a member of the new owner role.
-
The tablespace of a partitioned table cannot be modified, but the tablespace of the partition can be modified.
-
The storage parameter ORIENTATION cannot be modified.
-
Currently, SET SCHEMA can only set schemas to user schemas. It cannot set a schema to a system internal schema.
-
Column-store tables support only the PARTIAL CLUSTER KEY, UNIQUE, and PRIMARY KEY table-level constraints, but do not support foreign key table-level constraints.
-
In a column-store table, you can perform ADD COLUMN, ALTER TYPE, SET STATISTICS, DROP COLUMN operations, and change table name and space. The types of new and modified columns should be the Data Types supported by column-store. The USING option of ALTER TYPE only supports constant expression and expression involved in the column.
-
The column constraints supported by column-store tables include NULL, NOT NULL, DEFAULT constant values, UNIQUE and PRIMARY KEY. Only the DEFAULT value can be modified (by using SET DEFAULT and DROP DEFAULT). Currently, NULL and NOT NULL constraints cannot be modified.
-
Auto-increment columns cannot be added, or a column whose DEFAULT value contains the nextval() expression cannot be added.
-
Row-access control cannot be enabled for foreign tables and temporary tables.
-
When you delete a PRIMARY KEY constraint by constraint name, the NOT NULL constraint is not deleted. If necessary, manually delete the NOT NULL constraint.
-
When JDBC is used, the DEFAULT value can be set through PrepareStatement.
Syntax
-
Modify the definition of a table.
AlterTable ::= ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) } action [, ... ];
There are several clauses of action:
action ::= column_clause | ADD table_constraint [ NOT VALID ] | ADD table_constraint_using_index | VALIDATE CONSTRAINT constraint_name | DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] | CLUSTER ON index_name | SET WITHOUT CLUSTER | SET ( {storage_parameter = value} [, ... ] ) | RESET ( storage_parameter [, ... ] ) | OWNER TO new_owner | SET TABLESPACE new_tablespace | SET {COMPRESS|NOCOMPRESS} | TO { GROUP groupname | NODE ( nodename [, ... ] ) } | ADD NODE ( nodename [, ... ] ) | DELETE NODE ( nodename [, ... ] ) | DISABLE TRIGGER [ trigger_name | ALL | USER ] | ENABLE TRIGGER [ trigger_name | ALL | USER ] | ENABLE REPLICA TRIGGER trigger_name | ENABLE ALWAYS TRIGGER trigger_name | DISABLE '/' ENABLE [ REPLICA | ALWAYS ] RULE | DISABLE ROW LEVEL SECURITY | ENABLE ROW LEVEL SECURITY | FORCE ROW LEVEL SECURITY | NO FORCE ROW LEVEL SECURITY | ENCRYPTION KEY ROTATION | SET WITH OIDS | SET WITHOUT OIDS | INHERIT parents | NO INHERIT parents | OF type_name | NOT OF | REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING } | AUTO_INCREMENT [ = ] value | COMMENT [ = ] 'text'
NOTE:
- ADD table_constraint [ NOT VALID ] Adds a table constraint.
- ADD table_constraint_using_index Adds a primary key constraint or unique constraint to a table based on the existing unique index.
- VALIDATE CONSTRAINT constraint_name Validates a check-class constraint created with the NOT VALID option, and scans the entire table to ensure that all rows meet the constraint. Nothing happens if the constraint is already marked valid.
- DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] Deletes a table constraint.
- CLUSTER ON index_name Selects the default index for future CLUSTER operations. Actually, the table is not re-clustered.
- SET WITHOUT CLUSTER Deletes the most recently used CLUSTER index from the table. This affects future CLUSTER operations that do not specify an index.
- SET ( {storage_parameter = value} [, … ] ) Changes one or more storage parameters for the table.
- RESET ( storage_parameter [, … ] ) Resets one or more storage parameters to their defaults. As with SET, a table rewrite might be needed to update the table entirely.
- OWNER TO new_owner Changes the owner of a table, sequence, or view to the specified user.
- SET TABLESPACE new_tablespace Changes the table's tablespace to the specified tablespace and moves the data files associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE option in ALTER INDEX.
- SET {COMPRESS|NOCOMPRESS} Sets the compression feature of a table. The table compression feature affects only the storage mode of data inserted in a batch subsequently and does not affect storage of existing data. Setting the table compression feature will result in the fact that there are both compressed and uncompressed data in the table.
- TO { GROUP groupname | NODE ( nodename [, … ] ) } The syntax is only available in extended mode (when GUC parameter support_extended_features is on). Exercise caution when enabling the mode. It is mainly used for tools like internal dilatation tools. Common users should not use the mode.
- ADD NODE ( nodename [, … ] ) It is only available for internal scale-out tools. Common users should not use the syntax. - DELETE NODE ( nodename [, … ] ) It is only available for internal scale-in tools. Common users should not use the syntax.
- DISABLE TRIGGER [ trigger_name | ALL | USER ] Disables a single trigger specified by trigger_name, disables all triggers, or disables only user triggers (excluding internally generated constraint triggers, for example, deferrable unique constraint triggers and exclusion constraints triggers). Exercise caution when using this function because data integrity cannot be ensured as expected if the triggers are not executed.
- | ENABLE TRIGGER [ trigger_name | ALL | USER ] Enables a single trigger specified by trigger_name, enables all triggers, or enables only user triggers.
- | ENABLE REPLICA TRIGGER trigger_name Determines that the trigger firing mechanism is affected by the configuration variable session_replication_role. When the replication role is origin (default value) or local, a simple trigger is fired. When ENABLE REPLICA is configured for a trigger, it is fired only when the session is in replica mode.
- | ENABLE ALWAYS TRIGGER trigger_name Determines that all triggers are fired regardless of the current replication mode.
- | DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE Enables or disables a rule for tables. Disabled rules are still visible in the system, but are not applied during query rewriting. The ON SELECT rule cannot be disabled because it is related to the view implementation. Rules configured as ENABLE REPLICA are enabled only when the session is in replica mode, while those configured as ENABLE ALWAYS can be enabled regardless of the replica mode. Rule triggering is also affected by configuration variables in session_replication_role, which is similar to the preceding trigger setting.
- | DISABLE/ENABLE ROW LEVEL SECURITY Enables or disables row-level access control for a table. If row-level access control is enabled for a data table but no row-level access control policy is defined, the row-level access to the data table is not affected. If row-level access control for a table is disabled, the row-level access to the table is not affected even if a row-level access control policy has been defined. For details, see CREATE ROW LEVEL SECURITY POLICY.
- | NO FORCE/FORCE ROW LEVEL SECURITY Forcibly enables or disables row-level access control for a table. By default, the table owner is not affected by the row-level access control feature. However, if row-level access control is forcibly enabled, the table owner (excluding system administrators) will be affected. System administrators are not affected by any row-level access control policies.
- | REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING} Adjusts the amount of information written to WALs during logical replication. This option is valid only when wal_level is set to logical. When the original data table is updated, the default logical replication flow contains only the historical records of the primary key. If you need to output the historical records of column update or delete operations, you can modify this parameter. DEFAULT (not the default value in the system catalog) records the old value of the primary key column. USING INDEX records the old values of the columns contained in the index_name index. All columns of the index must be NOT NULL. FULL records the old values of all columns. NOTHING (default value in the system catalog) does not record information about old values.
- SET WITH OIDS Adds an OID system column to a data table. If the OID already exists in the table, the syntax does not change anything.
- SET WITHOUT OIDS Deletes an OID system column from a data table. If there is no OID in the table, the syntax does not change anything.
- INHERIT parent_table Adds the target data table to a specified parent data table as a new child data table. After that, the query for the parent data table will contain the data in the target data table. Before being added as a child data table, the target data table must contain all the columns in the parent data table. These columns must have matching data categories, and if they have NOT NULL constraints in the parent data table, they must also have NOT NULL constraints in the child data table. For all CHECK constraints in the parent data table, there must be corresponding constraints in the child data table, unless the parent data table is marked as non-inheritable.
- NO INHERIT parent_table Generates the target data table from the child data table of a specified parent data table. Queries for the parent data table will no longer contain records generated from the target data table.
- OF type_name Joins a table to a composite type, which is similar to table creation by using the CREATE TABLE OF option. The name and type of a table column must exactly match those defined in the composite type, but the OID system column can be different. The table cannot be inherited from any other table. These restrictions ensure that the CREATE TABLE OF option allows the same table definition.
- NOT OF Removes the association between a table and a type.
- REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING } DEFAULT records the old value of the primary key column. USING INDEX records the old values of columns covered by the named indexes. These values must be unique, non-local, and non-deferrable, and contain the values of columns marked NOT NULL. FULL records the old values of all columns in the row. NOTHING does not record information in old rows. In all cases, no old values are recorded unless at least one of the columns to be recorded in the new and old rows is different.
- COMMENT 'text' Comment a table object.
-
There are several clauses of column_clause:
column_clause ::= ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ] | MODIFY column_name data_type [ ON UPDATE update_expr ] | MODIFY [ COLUMN ] column_name [ COMMENT 'text'] | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | MODIFY column_name [ CONSTRAINT constraint_name ] NULL | DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ] | ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ] | ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT } | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL | ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer | ADD STATISTICS ( column_1_name, column_2_name [, ...] ) | DELETE STATISTICS ( column_1_name, column_2_name [, ...] ) | ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] ) | ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] ) | ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
NOTE:
ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ] [ COMMENT {=| } 'text'] Adds a column to a table. If a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified).
ADD ( { column_name data_type [ compress_mode ] } [ COMMENT {=| } 'text'] } [, ...] ) Adds columns in the table.
MODIFY [ COLUMN ] column_name [ COMMENT {=| } 'text'] Comments a column.
MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] ) Modifies the data type of an existing column in the table.
DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ] Drops a column from a table. Indexes and constraints related to the column are automatically dropped. If an object not belonging to the table depends on the column, CASCADE must be specified, such as a view. The DROP COLUMN statement does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent INSERT and UPDATE operations in the table will store a NULL value for the column. Therefore, column deletion takes a short period of time but does not immediately release the tablespace on the disks, because the space occupied by the deleted column is not recycled. The space will be recycled when VACUUM is executed.
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ] Modifies the type of a column in a table. Indexes and simple table constraints on the column will automatically use the new data type by reparsing the originally supplied expression. ALTER TYPE requires an entire table be rewritten. This is an advantage sometimes, because it frees up unnecessary space from a table. For example, to recycle the space occupied by a deleted column, the fastest method is to use the following statement.
ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
In this statement, anycol indicates any column existing in the table and anytype indicates the type of the prototype of the column. ALTER TYPE does not change the table except that the table is forcibly rewritten. In this way, the data that is no longer used is deleted.
ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT } Sets or removes the default value for a column. The default values only apply to subsequent INSERT operations; they do not cause rows already in the table to change. Defaults can also be created for views, in which case they are inserted into INSERT statements on the view before the view's ON INSERT rule is applied.
ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL Changes whether a column is marked to allow null values or to reject null values. You can only use SET NOT NULL when the column contains no null values.
ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer Specifies the per-column statistics-gathering target for subsequent ANALYZE operations. The target can be set in the range from 0 to 10000. Set it to -1 to revert to using the default system statistics target.
{ADD | DELETE} STATISTICS ((column_1_name, column_2_name [, …])) Adds or deletes the declaration of collecting multi-column statistics to collect multi-column statistics as needed when ANALYZE is performed for a table or a database. If the GUC parameter enable_functional_dependency is disabled, the statistics about a maximum of 32 columns can be collected at a time. If the GUC parameter enable_functional_dependency is enabled, the statistics about a maximum of 4 columns can be collected at a time. You are not allowed to add or delete such declaration for system catalogs or foreign tables.
ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, … ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, … ] ) Sets or resets per-attribute options. Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited. n_distinct affects statistics of a table, while n_distinct_inherited affects the statistics of the table and its subtables. Currently, only SET/RESET n_distinct is supported, and SET/RESET n_distinct_inherited is forbidden.
ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } Sets the storage mode for a column. This clause specifies whether this column is held inline or in a secondary TOAST table, and whether the data should be compressed. It is set only for row-store tables and is invalid for column-store tables. If it is set for column-store tables, an error will be displayed when the statement is executed. SET STORAGE itself does not change anything in the table. It sets the strategy to be pursued during future table updates.
-
column_constraint is as follows:
column_constraint ::= [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | DEFAULT default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters | ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key , ENCRYPTION_TYPE = encryption_type_value ) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
-
compress_mode is as follows:
compress_mode ::= [ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS ]
-
table_constraint_using_index used to add the primary key constraint or unique constraint based on the unique index is as follows:
table_constraint_using_index ::= [ CONSTRAINT constraint_name ] { UNIQUE | PRIMARY KEY } USING INDEX index_name [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
-
table_constraint is as follows:
table_constraint ::= [ CONSTRAINT constraint_name ] { CHECK ( expression ) | UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | PARTIAL CLUSTER KEY ( column_name [, ... ] ) FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
index_parameters is as follows:
index_parameters ::= [ WITH ( {storage_parameter = value} [, ... ] ) ] [ USING INDEX TABLESPACE tablespace_name ]
-
-
Rename a table. The renaming does not affect stored data.
AlterTable ::= ALTER TABLE [ IF EXISTS ] table_name RENAME TO new_table_name;
-
Rename the specified column in the table.
AlterTable ::= ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )} RENAME [ COLUMN ] column_name TO new_column_name;
-
Rename the constraint of the table.
AlterTable ::= ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) } RENAME CONSTRAINT constraint_name TO new_constraint_name;
-
Set the schema of the table.
AlterTable ::= ALTER TABLE [ IF EXISTS ] table_name SET SCHEMA new_schema;
NOTE:
- The schema setting moves the table into another schema. Associated indexes and constraints owned by table columns are migrated as well. Currently, the schema for sequences cannot be changed. If the table has sequences, delete the sequences, and create them again or delete the ownership between the table and sequences. In this way, the table schema can be changed.
- To change the schema of a table, you must also have the CREATE permission on the new schema. To add the table as a new child of a parent table, you must own the parent table as well. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have the CREATE permission on the table's schema. These restrictions enforce that the user can only recreate and delete the table. However, a system administrator can alter the ownership of any table anyway.
- All the actions except for RENAME and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several columns or alter the type of several columns in a single statement. This is useful with large tables, since only one pass over the tables need to be made.
- Adding a CHECK or NOT NULL constraint will scan the table to validate that existing rows meet the constraint.
- Adding a column with a non-NULL default or changing the type of an existing column will rewrite the entire table. Rewriting a large table may take much time and temporarily needs doubled disk space.
-
Add columns.
AlterTable ::= ALTER TABLE [ IF EXISTS ] table_name ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );
-
Update columns.
AlterTable ::= ALTER TABLE [ IF EXISTS ] table_name MODIFY ( { column_name data_type [ ON UPDATE update_expr ]| column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
Parameter Description
-
IF EXISTS
Sends a notice instead of an error if no tables have identical names. The notice prompts that the table you are querying does not exist.
-
table_name [*] | ONLY table_name | ONLY ( table_name )
table_name is the name of the table that you need to modify.
If ONLY is specified, only the table is modified. If ONLY is not specified, the table and all subtables are modified. You can add the asterisk (*) option following the table name to specify that all subtables are scanned, which is the default operation.
-
constraint_name
- Specifies the name of an existing constraint to drop in the DROP CONSTRAINT operation.
- Specifies the name of a new constraint in the ADD CONSTRAINT operation.
NOTICE: For a new constraint, constraint_name is optional in B-compatible mode (sql_compatibility = 'B'). For other modes, constraint_name must be added.
-
index_name
Specifies the name of an index.
NOTICE: In the ADD CONSTRAINT operation:
- index_name is supported only in B-compatible databases (that is, sql_compatibility = 'B').
- For foreign key constraints, if constraint_name and index_name are specified at the same time, constraint_name is used as the index name.
- For a unique key constraint, if both constraint_name and index_name are specified, index_name is used as the index name.
-
USING method
Specifies the name of the index method to be used.
For details about the value range, see USING method.
NOTICE: In the ADD CONSTRAINT operation:
- The USING method is supported only in B-compatible databases (that is, sql_compatibility = 'B').
- In B-compatible mode, if USING method is not specified, the default index method is btree for ASTORE or ubtree for USTORE.
-
ASC | DESC
ASC specifies an ascending (default) sort order. DESC specifies a descending sort order.
NOTICE: In ADD CONSTRAINT, ASC|DESC is supported only in B-compatible databases (sql_compatibility = 'B').
-
expression
Specifies an expression index constraint created based on one or more columns of the table. The expression index must be written with surrounding parentheses.
NOTICE: Expression indexes are supported only in B-compatible databases (that is, sql_compatibility = 'B').
-
storage_parameter
Specifies the name of a storage parameter.
The following option is added for creating an index:
-
parallel_workers (int type)
Value range: [0,32]. The value 0 indicates that concurrency is disabled.
Number of bgworker threads started when an index is created. For example, 2 indicates that two bgworker threads are started to create indexes concurrently.
If this parameter is not set, the number of started bgworker threads is related to the table size. Generally, the number of started bgworker threads does not exceed four.
-
hasuids (Boolean type)
Default value: off
If this parameter is set to on, a unique table-level ID is allocated to a tuple when the tuple is updated.
-
-
new_owner
Specifies the name of the new table owner.
-
new_tablespace
Specifies the new name of the tablespace to which the table belongs.
-
column_name, column_1_name, column_2_name
Specifies the name of a new or existing column.
-
data_type
Specifies the type of a new column or a new type of an existing column.
-
compress_mode
Compression option of a table field. The clause specifies the compression algorithm preferentially used by the column.
-
collation
Specifies the collation rule name of a column. The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column. You can run the select * from pg_collation; command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result.
-
USING expression
Specifies how to compute the new column value from the old; if omitted, the default conversion is an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from the old to new type.
NOTE: USING in ALTER TYPE can specify any expression involving the old values of the row; that is, it can refer to any columns other than the one being cast. This allows general casting to be done with the ALTER TYPE syntax. Because of this flexibility, the USING expression is not applied to the column's default value (if any); the result might not be a constant expression as required for a default. This means that when there is no implicit or assignment cast from old to new type, ALTER TYPE might fail to convert the default even though a USING clause is supplied. In such cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET DEFAULT to add a suitable new default. Similar considerations apply to indexes and constraints involving the column.
-
NOT NULL | NULL
Sets whether the column allows null values.
-
integer
Specifies the constant value of a signed integer. When using PERCENT, the range of integer is from 0 to 100.
-
attribute_option
Specifies an attribute option.
-
PLAIN | EXTERNAL | EXTENDED | MAIN
Specifies a column-store mode.
- PLAIN must be used for fixed-length values (such as integers). It must be inline and uncompressed.
- MAIN is for inline, compressible data.
- EXTERNAL is for external, uncompressed data. Use of EXTERNAL will make substring operations on text and bytea values run faster, at the penalty of increased storage space.
- EXTENDED is for external, compressed data. EXTENDED is the default for most data types that support non-PLAIN storage.
-
CHECK ( expression )
New rows or rows to be updated must satisfy for an expression to be true. If any row produces a false result, an error is raised and the database is not modified.
A check constraint specified as a column constraint should reference only the column's values, while an expression in a table constraint can reference multiple columns.
Currently, CHECK ( expression ) does not include subqueries and cannot use variables apart from the current column.
-
DEFAULT default_expr
Assigns a default data value for a column.
The data type of the default expression must match the data type of the column.
The default expression will be used in any insert operation that does not specify a value for the column. If there is no default value for a column, then the default value is null.
-
UNIQUE index_parameters
UNIQUE ( column_name [, … ] ) index_parameters
Specifies that a group of one or more columns of a table can contain only unique values.
-
PRIMARY KEY index_parameters
PRIMARY KEY ( column_name [, … ] ) index_parameters
Specifies that a column or columns of a table can contain only unique (non-duplicate) and non-null values.
-
REFERENCES reftable [ ( refcolum ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint)
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table constraint)
The foreign key constraint requires that the group consisting of one or more columns in the new table should contain and match only the referenced column values in the referenced table. If refcolum is omitted, the primary key of reftable is used. The referenced column should be the only column or primary key in the referenced table. A foreign key constraint cannot be defined between a temporary table and a permanent table.
There are three types of matching between a reference column and a referenced column:
-
MATCH FULL: A column with multiple foreign keys cannot be NULL unless all foreign key columns are NULL.
-
MATCH SIMPLE (default): Any unexpected foreign key column can be NULL.
-
MATCH PARTIAL: This option is not supported currently.
In addition, when you perform certain operations on the data in the referenced table, the operations are performed on the corresponding columns in the new table. ON DELETE: specifies the operations to be executed after a referenced row in the referenced table is deleted. ON UPDATE: specifies the operation to be performed when the referenced column data in the referenced table is updated. The possible actions of the ON DELETE and ON UPDATE clauses are as follows:
-
NO ACTION (default): When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created. If the constraint is deferrable and there are still any referenced rows, this error will occur when the constraint is checked.
-
RESTRICT: When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created. It is the same as NO ACTION except that the action cannot be delayed.
-
CASCADE: deletes any row that references the deleted row from the new table, or update the field value of the referenced row in the new table to the new value of the referenced column.
-
SET NULL: sets the referenced field to NULL.
-
SET DEFAULT: sets referenced fields to their default values.
-
-
DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE
Sets whether the constraint can be deferrable.
- DEFERRABLE: deferrable to the end of the transaction and checked using SET CONSTRAINTS.
- NOT DEFERRABLE: checks immediately after the execution of each command.
- INITIALLY IMMEDIATE: checks immediately after the execution of each statement.
- INITIALLY DEFERRED: checks when the transaction ends.
NOTE: Ustore tables do not support the DEFERRABLE and INITIALLY DEFERRED constraints.
-
PARTIAL CLUSTER KEY
Specifies a partial cluster key for storage. When importing data to a column-store table, you can perform local data sorting by specified columns (single or multiple).
-
WITH ( {storage_parameter = value} [, … ] )
Specifies an optional storage parameter for a table or an index.
-
tablespace_name
Specifies the name of the tablespace where the index locates.
-
COMPRESS|NOCOMPRESS
- NOCOMPRESS: If the NOCOMPRESS keyword is specified, the existing compression feature of the table will not be changed.
- COMPRESS: If the COMPRESS keyword is specified, the table compression feature will be triggered by batch tuple insertion.
-
new_table_name
Specifies the new table name.
-
new_column_name
Specifies the new name of a specific column in a table.
-
new_constraint_name
Specifies the new name of a table constraint.
-
new_schema
Specifies the new schema name.
-
CASCADE
Automatically drops objects that depend on the dropped column or constraint (for example, views referencing the column).
-
RESTRICT
Refuses to drop the column or constraint if there are any dependent objects. This is the default processing.
-
schema_name
Specifies the schema name of a table.
Examples
See Example in CREATE TABLE.