Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
DataArts Fabric
IoT
IoT Device Access
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
Huawei Cloud Astro Canvas
Huawei Cloud Astro Zero
CodeArts Governance
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance (CCI)
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Cloud Transformation
Well-Architected Framework
Cloud Adoption Framework
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Blockchain
Blockchain Service
Web3 Node Engine Service
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

INSERT

Updated on 2025-05-29 GMT+08:00

Description

Inserts one or more rows of data into a table.

Precautions

  • The owner of a table, users with the INSERT permission on the table, or users with the INSERT ANY TABLE permission can insert data into the table. System administrators have the permission to insert data into the table by default when separation of duties is disabled.
  • Use of the RETURNING clause requires the SELECT permission on all columns mentioned in RETURNING.
  • If ON DUPLICATE KEY UPDATE is used, you must have the INSERT and UPDATE permissions on the table and the SELECT permission on the columns of the UPDATE clause.
  • If you use the query clause to insert rows from a query, you need to have the SELECT permission on any table or column used in the query.
  • If you use the query clause to insert data from the dynamic data anonymization column, the inserted result is the anonymized value and cannot be restored.
  • When you connect to a database compatible to Teradata and td_compatible_truncation is on, a long string will be automatically truncated. If later INSERT statements (not involving foreign tables) insert long strings to columns of CHAR- and VARCHAR-typed columns in the target table, the system will truncate the long strings to ensure no strings exceed the maximum length defined in the target table.
    NOTE:

    If inserting multi-byte character data (such as Chinese characters) to a database with the character set byte encoding (SQL_ASCII, LATIN1), and the character data crosses the truncation position, the string is truncated based on its bytes instead of characters. Unexpected result will occur in tail after the truncation. If you want correct truncation result, you are advised to adopt encoding set such as UTF8, which has no character data crossing the truncation position.

Syntax

1
2
3
4
5
6
7
8
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT [/*+ plan_hint */] [ IGNORE ] INTO table_name [ { [alias_name] [ ( column_name [, ...] ) ] } | { [partition_clause] [ AS alias ] [ ( column_name [, ...] ) ] } ] 
    { DEFAULT VALUES
    | { VALUES | VALUE } {( { expression | DEFAULT } [, ...] ) }[, ...] 
    | query }
    [ { ON DUPLICATE KEY UPDATE { NOTHING | { column_name = { expression | DEFAULT } } [, ...] [ WHERE condition ] } } 
 | { [ ON CONFLICT [ conflict_target ] conflict_action ] } ]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];
Insert subqueries and views.
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT [/*+ plan_hint */] INTO {subquery | view_name} [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES
    | { VALUES | VALUE } {( { expression | DEFAULT } [, ...] ) }[, ...] 
    | query }
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];
  • Detailed information of subquery:
    [ WITH [ RECURSIVE ] with_query [, ...] ]
    SELECT [/*+ plan_hint */] [ ALL ]
    { * | {expression [ [ AS ] output_name ]} [, ...] }
    [ into_option ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
    [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ into_option ];
  • The specified subquery source from_item is as follows:
    {[ ONLY ] {table_name | view_name} [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}
  • Detailed information of with_query:
    with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ]
    ( {SELECT | VALUES | INSERT | UPDATE | DELETE} )
  • Detailed information of partition_clause:
    PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } 
  • Detailed information of conflict_target:
    ( { index_column_name | ( index_expression ) } [, ...] ) [ WHERE index_predicate ]
  • Detailed information of conflict_action:
    DO NOTHING | 
    DO UPDATE SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ WHERE condition ]

Parameters

  • WITH [ RECURSIVE ] with_query [, ...]

    Specifies one or more subqueries that can be referenced by name in the main query, which is equivalent to a temporary table.

    If RECURSIVE is specified, a SELECT subquery can be referenced by name.

    with_query:

    with_query_name specifies the name of the result set generated by a subquery. Such names can be used to access the result sets of subqueries in a query.

    -- column_name specifies the column name displayed in the subquery result set.

    – Each subquery can be a SELECT, VALUES, INSERT, UPDATE or DELETE statement.

    NOTICE:

    If a subquery is a DML statement with a RETURNING statement, the number of command output records of the INSERT statement is determined by the subquery. Assume that table T1 exists. Run the following statement:

    WITH CTE AS (INSERT INTO T1 VALUES(1,2) RETURNING *) INSERT INTO T1 SELECT * FROM CTE;

    The number of command output records of the preceding statement is determined by the following part instead of the entire statement. That is, the command output is "INSERT 0 1" instead of "INSERT 0 2".

    INSERT INTO T1 VALUES(1,2) RETURNING *
    – You can use MATERIALIZED or NOT MATERIALIZED to modify the CTE.
    • If MATERIALIZED is specified, the WITH query will be materialized, and a copy of the subquery result set is generated. The copy is directly queried at the reference point. Therefore, the WITH subquery cannot be jointly optimized with the SELECT statement trunk (for example, predicate pushdown and equivalence class transfer). In this scenario, you can use NOT MATERIALIZED for modification. If the WITH query can be executed as a subquery inline, the preceding optimization can be performed.
    • If the user does not explicitly declare the materialized attribute, comply with the following rules: If the CTE is referenced only once in the trunk statement to which it belongs and semantically supports inline execution, it will be rewritten as subquery inline execution. Otherwise, the materialized execution will be performed in CTE Scan mode.
    NOTE:

    INSERT ON DUPLICATE KEY UPDATE does not support the WITH or WITH RECURSIVE clauses.

  • plan_hint clause

    Follows the INSERT keyword in the /*+ */ format. It is used to optimize the plan of an INSERT statement block. For details, see Hint-based Optimization. In each statement, only the first /*+ plan_hint */ comment block takes effect as a hint. Multiple hints can be written.

  • IGNORE

    When the INSERT statement uses the IGNORE keyword, some ERROR-level errors can be degraded to WARNING-level errors, and invalid values can be adjusted to the closest values based on error scenarios. GaussDB supports the following error codes for error downgrade:

    • Damage to the NOT NULL constraint
    • UNIQUE KEY conflict
    • No partition found for the inserted value
    • Unmatch between the inserted data and the specified partition
    • Multiple rows returned for a subquery
    • Oversized data
    • Time function overflow
    • Division by 0
    • Incorrect value
    NOTE:
    1. INSERT IGNORE is supported only when sql_compatibility is set to 'MySQL', b_format_version set to '5.7', and b_format_dev_version set to 's1'.
    2. INSERT IGNORE does not support encrypted tables and foreign tables.
    3. INSERT IGNORE does not support PRIMARY KEY constraints or UNIQUE KEY constraints that take effect with a delay.
    4. The OIDs of each data type that supports damage to NOT NULL constraints with NULL values are TIMESTAMPOID, TIMESTAMPTZOID, TIMEOID, TIMETZOID, RELTIMEOID, INTERVALOID, TINTERVALOID, SMALLDATETIMEOID, DATEOID, NAMEOID, POINTOID, PATHOID, POLYGONOID, CIRCLEOID, LSEGOID, BOXOID, JSONOID, JSONBOID, XMLOID, XMLTYPEOID, VARBITOID, NUMERICOID, CIDROID, INETOID, MACADDROID, NUMRANGEOID, INT8RANGEOID, INT4RANGEOID, TSRANGEOID, TSTZRANGEOID, DATERANGEOID, ABSTIMEOID, BOOLOID, INT8OID, INT4OID, INT2OID, INT1OID, FLOAT4OID, FLOAT8OID, CASHOID, UINT1OID, UINT2OID, UINT4OID, UINT8OID, BPCHAROID, VARCHAROID, NVARCHAR2OID, CHAROID, BYTEAOID, RAWOID, BLOBOID, CLOBOID, TEXTOID, and YEAROID. Table 1 lists the zero values of each data type.
    5. When the default zero value is used for calculation, exercise caution when using IGNORE to ignore the NOT NULL constraint.
    6. INSERT IGNORE does not support tables with global secondary indexes.
    7. An error generated on a CN is degraded to the warning information while an error generated on a DN is not degraded to the warning information.
    Table 1 Default zero values for the data type

    OID of Each Data Type

    Default Zero Value

    INT8OID, INT4OID, INT2OID, INT1OID, UINT1OID, UINT2OID, UINT4OID, UINT8OID, FLOAT4OID, FLOAT8OID, NUMERICOID

    0 or 0.00 (The number of 0s after the decimal point is specified by a parameter.)

    BPCHAROID, VARCHAROID, CHAROID, BYTEAOID, RAWOID, BLOBOID, NVARCHAR2OID, CLOBOID, TEXTOID, VARBITOID, NAMEOID

    Empty string

    NUMRANGEOID, INT8RANGEOID, INT4RANGEOID, TSRANGEOID, TSTZRANGEOID, DATERANGEOID

    empty

    TIMEOID

    time '00:00:00'

    TIMETZOID

    timetz '00:00:00'

    INTERVALOID

    interval '00:00:00'

    TINTERVALOID

    tinterval(abstime '1970-01-01 00:00:00', abstime '1970-01-01 00:00:00')

    SMALLDATETIMEOID

    smalldatetime '1970-01-01 00:00:00'

    ABSTIMEOID

    abstime '1970-01-01 00:00:00'

    RELTIMEOID

    reltime '00:00:00'

    TIMESTAMPOID, TIMESTAMPTZOID

    1970-01-01 00:00:00

    DATEOID

    1970-01-01

    YEAROID

    0000

    POINTOID

    (0,0)

    PATHOID, POLYGONOID

    ((0,0))

    CIRCLEOID

    <(0,0),0>

    LSEGOID

    [(0,0),(0,0)]

    BOXOID

    (0,0),(0,0)

    JSONOID, JSONBOID, XMLOID

    'null'

    XMLTYPEOID

    '<null/>'

    CIDROID

    0.0.0.0/32

    INETOID

    0.0.0.0

    MACADDROID

    00:00:00:00:00:00

    BOOLOID

    f

    CASHOID

    $0.00

  • table_name

    Specifies the name of the target table where data will be inserted.

    Value range: an existing table name

    NOTE:

    You can use database links to perform operations on remote tables. For details, see DATABASE LINK.

  • subquery

    The inserted object can be a subquery. When a subquery is inserted, the subquery is regarded as a temporary view. The CHECK OPTION option can be added to the end of the subquery.

    If a subquery contains only one table, data is inserted into the table. If a subquery contains multiple tables or has nested relationships, check whether a key-preserved table exists to determine whether data can be inserted. For details about key-preserved tables and WITH CHECK OPTION, see CREATE VIEW.

  • view_name

    Indicates the target view to be inserted.

    NOTE:

    The restrictions on inserting views and subqueries are as follows:

    • The INSERT operation can be performed only on columns that directly reference user columns in the base table.
    • A subquery or view must contain at least one updatable column. For details about updatable columns, see CREATE VIEW.
    • Views and subqueries that contain the DISTINCT, GROUP BY, HAVING, LIMIT or OFFSET clause at the top layer are not supported.
    • Views and subqueries that contain set operations (UNION, INTERSECT, EXCEPT, and MINUS) at the top layer are not supported.
    • Views and subqueries whose target lists contain aggregate functions, window functions, or return set functions (such as array_agg, json_agg, and generate_series) are not supported.
    • Views with BEFORE/AFTER triggers but without INSTEAD OF triggers or INSTEAD rules are not supported.
    • The ON DUPLICATE KEY UPDATE function is not supported.
    • Table types supported in views and subqueries include ordinary tables, temporary tables, global temporary tables, partitioned tables, level-2 partitioned tables, Ustore tables, and Astore tables.
    • For a multi-table join view or subquery, only one base table can be inserted at a time.
    • When INSERT joins a view or subquery, explicitly specified columns to be inserted or implicitly specified columns (columns specified during view or subquery creation) cannot reference columns of non-key-preserved tables; if the WITH CHECK OPTION clause is used, INSERT operations cannot be performed on join columns in join views or subqueries. For details about the key-preserved table, see CREATE VIEW.
    • The INSERT operation cannot be performed on the system view.
  • alias_name

    Specifies the table alias when the INSERT statement is used without AS alias.

    NOTE:
    1. When the INSERT statement is used without AS alias, the table alias cannot be a keyword (such as SELECT and VALUE) or an expression. The alias must comply with the identifier naming rule.
    2. When the INSERT statement is used without AS alias, data cannot be inserted into the specified partition.
  • partition_clause

    Inserts data to a specified partition.

    For details about the keywords, see SELECT.

    If the value of the VALUE clause is inconsistent with that of the specified partition, an exception is displayed.

  • column_name

    Specifies the name of a column in a table.

    • The column name can be qualified with a subcolumn name or array index, if needed.
    • Each column not present in the column list will be filled with a default value, either its declared default value or NULL if there is none. Inserting data into only some columns of a composite type leaves the other columns NULL.
    • The target column names column_name can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.
    • The target columns are the first N column names, if there are only N columns provided by the VALUE clause and QUERY.
    • The values provided by the VALUE clause and QUERY are joined with the corresponding columns from left to right in the table.
    • Data can be inserted in the format of table alias.column name.

    Value range: an existing column

  • expression

    Specifies an expression or a value to assign to the corresponding column.

    • In the INSERT ON DUPLICATE KEY UPDATE statement, expression can be VALUES(column_name) or EXCLUDED.column_name, indicating that the value of column_name corresponding to the conflict row is referenced. VALUES(column_name) can be nested in a compound expression, for example, VALUES(column_name)+1, VALUES(column_name)+VALUES(column_name), and function_name(VALUES(column_name)).
      NOTE:
      • VALUES(column_name) can be used only in the ON DUPLICATE KEY UPDATE clause.
      • VALUES(column_name) cannot be used in the IN or NOT IN expressions.
    • If single-quotation marks are inserted in a column, the single-quotation marks need to be used for escape.
    • If the expression for any column is not of the correct data type, automatic type conversion will be attempted. If the attempt fails, data insertion fails, and the system returns an error message.
  • DEFAULT

    Specifies the default value of a column. The value is NULL if no default value is assigned to it.

  • query

    Specifies a query statement (SELECT statement) that uses the query result as the inserted data.

  • RETURNING

    Returns the inserted rows. The syntax of the RETURNING list is identical to that of the output list of SELECT.

  • output_expression

    Specifies an expression used to calculate the output result of the INSERT statement after each row is inserted.

    Value range: The expression can use any column in the table. You can use the asterisk (*) to return all columns of the inserted row.

  • output_name

    Specifies a name to use for a returned column.

    Value range: a string that complies with the Identifier Naming Conventions.

  • ON DUPLICATE KEY UPDATE

    For a table with a UNIQUE constraint (UNIQUE INDEX or PRIMARY KEY), if the inserted data violates the UNIQUE constraint, the UPDATE clause is executed to update the conflicting rows. If NOTHING is specified for the UPDATE clause, no operation will be performed. You can use EXCLUDED. or VALUES() to select the column corresponding to the source data.

    For a table without a UNIQUE constraint, only INSERT is performed.

    • Row triggers are supported. The execution sequence of triggers is determined by the actual execution process.

      Executing INSERT will trigger the BEFORE INSERT and AFTER INSERT triggers.

      Executing UPDATE will trigger the BEFORE INSERT, BEFORE UPDATE, and AFTER UPDATE triggers.

      Executing UPDATE NOTHING will trigger the BEFORE INSERT trigger.

    • Statement triggers are supported. The triggers are determined by the statement to be executed.

      Executing UPDATE NOTHING will trigger the BEFORE INSERT and AFTER INSERT triggers.

      Executing UPDATE will trigger the BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, and AFTER UPDATE.

    • The UNIQUE constraint or primary key of DEFERRABLE is not supported.
    • If a table has multiple UNIQUE constraints and the inserted data violates multiple UNIQUE constraints, only the first row that has a conflict is updated. (The check sequence is closely related to index maintenance. Generally, the conflict check is performed on the index that is created first.)
    • Primary keys and unique index columns cannot be updated.
    • Foreign tables are not supported.
    • The WHERE clause and expression of UPDATE cannot contain sublinks.
  • ON CONFLICT

    If a UNIQUE constraint or exclusive constraint conflict occurs during an insertion, the insertion is canceled when ON CONFLICT is followed by DO NOTHING, and the update is performed when it is followed by DO UPDATE SET. If no conflict occurs, the common INSERT process is executed.

    • index_column_name: Name of the index column.
    • index_expression: Similar to index_column_name, it is an expression index based on one or more columns of the table and can be used for fast access to a variation of the basic data.
    • index_predicate: condition expression that specifies the range of a partially unique index.
    • expression: expression or value to be assigned to the corresponding column.
    • DEFAULT: The corresponding column will be filled with the default value.
    NOTE:

    The INSERT... ON CONFLICT syntax has the following restrictions:

    1. Only PG compatibility is supported.
    2. The [ COLLATE collation ] [ opclass ] syntax is not supported.
    3. The ON CONSTRAINT constraint_name syntax is not supported.
    4. Users must have the UPDATE permission before using DO UPDATE SET.
    5. Nesting in the conflict_target, UPDATE SET, or UPDATE SET clause is not supported.
    6. Column-format tables, foreign tables, STREAM objects, and CONTVIEW objects are not supported.
    7. Distribution keys cannot be updated.
    8. In distributed mode, if the execution plan is stream, light proxy, or fqs, multiple data records with the same unique key cannot be inserted into the same statement at a time. If the execution plan is pgxc, the preceding operation is allowed.
    9. GSIs are not supported.
    10. Database links are not supported.
    11. Subqueries cannot be inserted.

Examples

  • Insert a data record.
    Example:
    -- Create a table.
    gaussdb=# CREATE TABLE test_t1(col1 INT,col2 VARCHAR);
    
    -- Insert data.
    gaussdb=# INSERT INTO test_t1 (col1, col2) VALUES (1,'AB');
    
    -- Add values to some columns of the table.
    gaussdb=# INSERT INTO test_t1 (col1) VALUES (2);
    
    -- There is no parenthesis on the left of the VALUES keyword. Values must be added to all fields in the parenthesis on the right according to the table structure sequence.
    gaussdb=# INSERT INTO test_t1 VALUES (3,'AC');
    
    -- Query the table.
    gaussdb=# SELECT * FROM test_t1;
     col1 | col2 
    ------+------
        1 | AB
        2 | 
        3 | AC
    (3 rows)
    -- Drop.
    gaussdb=# DROP TABLE test_t1;
  • Insert multiple data records.
    Example:
    -- Create a table.
    gaussdb=# CREATE TABLE test_t2(col1 INT,col2 VARCHAR);
    gaussdb=# CREATE TABLE test_t3(col1 INT,col2 VARCHAR);
    
    -- Insert multiple data records.
    gaussdb=# INSERT INTO test_t2 (col1, col2) VALUES (10,'AA'),(20,'BB'),(30,'CC');
    
    -- Query.
    gaussdb=# SELECT * FROM test_t2;
     col1 | col2 
    ------+------
       10 | AA
       20 | BB
       30 | CC
    (3 rows)
    
    -- Insert data in test_t2 into test_t3.
    gaussdb=# INSERT INTO test_t3 SELECT * FROM test_t2;
    
    -- Query.
    gaussdb=# SELECT * FROM test_t3;
     col1 | col2 
    ------+------
       10 | AA
       20 | BB
       30 | CC
    (3 rows)
    
    -- Drop.
    gaussdb=# DROP TABLE test_t2;
    DROP TABLE test_t3;
  • ON DUPLICATE KEY UPDATE

    Example:

    -- Create a table.
    gaussdb=# CREATE TABLE test_t4 (id INT PRIMARY KEY, info VARCHAR(10));
    gaussdb=# INSERT INTO test_t4 VALUES (1, 'AA'), (2,'BB'), (3, 'CC');
    
    -- Use the ON DUPLICATE KEY UPDATE keyword.
    gaussdb=# INSERT INTO test_t4 VALUES (3, 'DD'), (4, 'EE') ON DUPLICATE KEY UPDATE info = VALUES(info);
    
    -- Query.
    gaussdb=# SELECT * FROM test_t4;
     id | info 
    ----+------
      1 | AA
      2 | BB
      4 | EE
      3 | DD
    
    -- Drop.
    gaussdb=# DROP TABLE test_t4;
  • ON CONFLICT

    Example:

    -- Create a table.
    gaussdb=# CREATE DATABASE pg_db dbcompatibility = 'PG';
    gaussdb=# \c pg_db
    pg_db=# CREATE TABLE insert_onconflict_tb1(id INT, a INT PRIMARY KEY, b INT, c VARCHAR2(20));
    pg_db=# INSERT INTO insert_onconflict_tb1 VALUES (1, 1, 2, 'aaa'), (2, 2, 0, 'zzz');
    -- Query the table.
    pg_db=# SELECT * FROM insert_onconflict_tb1;
     id | a | b |  c  
    ----+---+---+-----
      1 | 1 | 2 | aaa
      2 | 2 | 0 | zzz
    (2 rows)
    -- Use the ON CONFLICT keyword.
    pg_db=# INSERT INTO insert_onconflict_tb1 VALUES (1, 1, 5, 'ddd'), (2, 2, 1, 'yyy') ON CONFLICT (a) DO UPDATE SET b=excluded.b, c=excluded.c;
    pg_db=# INSERT INTO insert_onconflict_tb1 VALUES (1, 1, 5, 'ddd'), (2, 2, 1, 'yyy') ON CONFLICT (a) DO NOTHING;
    -- Query the table.
    pg_db=# SELECT * FROM insert_onconflict_tb1;
     id | a | b |  c  
    ----+---+---+-----
      1 | 1 | 5 | ddd
      2 | 2 | 1 | yyy
    (2 rows)
    -- Drop the table.
    pg_db=# DROP TABLE insert_onconflict_tb1;
    pg_db=# \c postgres
    -- Drop the PG-compatible library.
    gaussdb=# DROP DATABASE pg_db;
  • INSERT IGNORE

    Example 1: Damage to the NOT NULL constraint

    -- Create a table.
    gaussdb=# CREATE TABLE test_t5(f1 INT NOT NULL);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    
    -- Use the IGNORE keyword.
    gaussdb=# INSERT IGNORE INTO test_t5 VALUES(NULL);
    WARNING:  null value in column "f1" violates not-null constraint
    DETAIL:  Failing row contains (null).
    INSERT 0 1
    
    -- Query the table.
    gaussdb=# SELECT * FROM test_t5;
     f1
    ----
      0
    (1 row)
    
    -- Drop the table.
    gaussdb=# DROP TABLE test_t5;
    DROP TABLE

    Example 2: UNIQUE KEY conflict

    -- Create a table.
    gaussdb=# CREATE TABLE test_t6(f1 INT PRIMARY KEY);
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_t6_pkey" for table "test_t6"
    CREATE TABLE
    
    -- Insert data.
    gaussdb=# INSERT INTO test_t6 VALUES(1);
    INSERT 0 1
    
    -- Use the IGNORE keyword.
    gaussdb=# INSERT IGNORE INTO test_t6 VALUES(1);
    INSERT 0 0
    
    -- Query the table.
    gaussdb=# SELECT * FROM test_t6;
     f1
    ----
      1
    (1 row)
    
    -- Drop the table.
    gaussdb=# DROP TABLE test_t6;
    DROP TABLE

    Example 3: No partition found for the inserted value

    -- Create a table.
    gaussdb=# CREATE TABLE test_t7(f1 INT, f2 INT) PARTITION BY LIST(f1) (PARTITION p0 VALUES(1, 4, 7), PARTITION p1 VALUES (2, 5, 8));
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    
    -- Use the IGNORE keyword.
    gaussdb=# INSERT IGNORE INTO test_t7 VALUES(3, 5);
    INSERT 0 0
    
    -- Query the table.
    gaussdb=# SELECT * FROM test_t7;
     f1 | f2
    ----+----
    (0 rows)
    
    -- Drop the table.
    gaussdb=# DROP TABLE test_t7;
    DROP TABLE

    Example 4: Unmatch between the inserted data and the specified partition

    -- Create a table.
    gaussdb=# CREATE TABLE test_t8(f1 INT NOT NULL, f2 TEXT, f3 INT) PARTITION BY RANGE(f1)(PARTITION p0 VALUES LESS THAN(5), PARTITION p1 VALUES LESS THAN(10), PARTITION p2 VALUES LESS THAN(15), PARTITION p3 VALUES LESS THAN(MAXVALUE));
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    
    -- Use the IGNORE keyword.
    gaussdb=# INSERT IGNORE INTO test_t8 PARTITION(p2) VALUES(20, 'Jan', 1);
    INSERT 0 0
    
    -- Query the table.
    gaussdb=# SELECT * FROM test_t8;
     f1 | f2 | f3
    ----+----+----
    (0 rows)
    
    -- Drop the table.
    gaussdb=# DROP TABLE test_t8;
    DROP TABLE

    Example 5: Multiple rows returned for a subquery

    -- Create a table.
    gaussdb=# CREATE TABLE test_t9(f1 INT, f2 INT);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    
    -- Insert data.
    gaussdb=# INSERT INTO test_t9 VALUES(1, 1), (2, 2), (3, 3);
    INSERT 0 3
    
    -- Use the IGNORE keyword.
    gaussdb=# INSERT IGNORE INTO test_t9 VALUES((SELECT f1 FROM test_t9), 0);
    WARNING:  more than one row returned by a subquery used as an expression
    CONTEXT:  referenced column: f1
    INSERT 0 1
    
    -- Query the table.
    gaussdb=# SELECT * FROM test_t9 WHERE f2 = 0;
     f1 | f2
    ----+----
        |  0
    (1 row)
    
    -- Drop the table.
    gaussdb=# DROP TABLE test_t9;
    DROP TABLE

    Example 6: Oversized data

    -- Create a table.
    gaussdb=# CREATE TABLE test_t10(f1 VARCHAR(5));
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    
    -- Use the IGNORE keyword.
    gaussdb=# INSERT IGNORE INTO test_t10 VALUES('aaaaaaaaa');
    WARNING:  value too long for type character varying(5)
    CONTEXT:  referenced column: f1
    INSERT 0 1
    
    -- Query the table.
    gaussdb=# SELECT * FROM test_t10;
      f1
    -------
     aaaaa
    (1 row)
    
    -- Drop the table.
    gaussdb=# DROP TABLE test_t10;
    DROP TABLE

    Example 7: Time function overflow

    -- Create a table.
    gaussdb=# CREATE TABLE test_t11(f1 DATETIME);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    
    -- Use the IGNORE keyword.
    gaussdb=# INSERT IGNORE INTO test_t11 VALUES(date_sub('2000-01-01', INTERVAL 2001 YEAR));
    WARNING:  Datetime function: datetime field overflow
    CONTEXT:  referenced column: f1
    INSERT 0 1
    
    -- Query the table.
    gaussdb=# SELECT * FROM test_t11;
     f1
    ----
    
    (1 row)
    
    -- Drop the table.
    gaussdb=# DROP TABLE test_t11;
    DROP TABLE

    Example 8: Division by 0

    -- Create a table.
    gaussdb=# CREATE TABLE test_t12(f1 INT);
    CREATE TABLE
    
    -- Use the IGNORE keyword.
    gaussdb=# INSERT IGNORE INTO test_t12 VALUES(1/0);
    WARNING:  division by zero
    CONTEXT:  referenced column: f1
    INSERT 0 1
    
    -- Query the table.
    gaussdb=# SELECT * FROM test_t12;
     f1
    ----
    
    (1 row)
    
    -- Drop the table.
    gaussdb=# DROP TABLE test_t12;
    DROP TABLE

    Example 9: Incorrect value

    -- Create a table.
    gaussdb=# CREATE TABLE test_t13(f0 INT, f1 FLOAT);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f0' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    
    -- Use the IGNORE keyword.
    gaussdb=# INSERT IGNORE INTO test_t13 VALUES(1, '1.11aaa');
    WARNING:  invalid input syntax for type real: "1.11aaa"
    LINE 1: INSERT IGNORE INTO test_t13 VALUES(1, '1.11aaa');
                                                  ^
    CONTEXT:  referenced column: f1
    INSERT 0 1
    
    -- Query the table.
    gaussdb=# SELECT * FROM  test_t13;
     f0 |  f1
    ----+------
      1 | 1.11
    (1 row)
    
    -- Drop the table.
    gaussdb=# DROP TABLE test_t13;
    DROP TABLE
    Example 10: Insert a data record using a table alias.
    -- Create a table.
    gaussdb=# create table tb1 (va int , vb int);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'va' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    
     -- Use a table alias.
    gaussdb=# insert into tb1 as tt(tt.va, tt.vb) values (1,2);
    INSERT 0 1
    gaussdb=# insert into tb1 tt(tt.va, tt.vb) values (3,4);
    INSERT 0 1                          
    
    -- Query the table.
    gaussdb=# select * from tb1;
     va | vb 
    ----+----
      1 |  2
      3 |  4
    (2 rows)
    
    -- Drop the table.
    gaussdb=# DROP TABLE tb1;
    DROP TABLE

  • WITH [ RECURSIVE ] with_query [, ...]
    Example:
    -- Grade table.
    gaussdb=# CREATE TABLE grade (
        sid INT,
        course VARCHAR(20),
        score FLOAT
    );
    -- Student table.
    gaussdb=# CREATE TABLE student(
        sid INT PRIMARY KEY,
        class INT,
        name  VARCHAR(50),
        sex INT CHECK (sex = 0 or sex = 1)
    );
    
    -- Insert data.
    gaussdb=# WITH student_sid AS ( INSERT INTO student ( sid, CLASS, NAME, sex ) VALUES ( 1, 1, 'scott', 1 ) RETURNING sid ) 
        INSERT INTO grade ( sid, course, score )
        VALUE ( ( SELECT sid FROM student_sid ), 'match', '96' ),
       ( ( SELECT sid FROM student_sid ), 'chinese', '82' ),
       ( ( SELECT sid FROM student_sid ), 'english', '86' );
    
    -- Query data.
    gaussdb=# SELECT * FROM student;
     sid | class | name  | sex 
    -----+-------+-------+-----
       1 |     1 | scott |   1
    (1 row)
    
    gaussdb=# SELECT * FROM grade;
     sid | course  | score 
    -----+---------+-------
       1 | match   |    96
       1 | chinese |    82
       1 | english |    86
    (3 rows)
    
    -- Drop.
    gaussdb=# DROP TABLE student;
    gaussdb=# DROP TABLE grade;
  • Insert data into a view or subquery.

    Example 1: Insert a subquery.

    -- Create a schema.
    gaussdb=# CREATE SCHEMA ins_subqry;
    CREATE SCHEMA
    gaussdb=# SET CURRENT_SCHEMA = 'ins_subqry';
    SET
    
    -- Create tables.
    gaussdb=# CREATE TABLE t1 (x1 int, y1 int);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'x1' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    gaussdb=# CREATE TABLE t2 (x2 int PRIMARY KEY, y2 int);
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
    CREATE TABLE
    
    -- Insert data into t1 through a subquery.
    gaussdb=# INSERT INTO (SELECT * FROM t1) VALUES (1, 1);
    INSERT 0 1
    gaussdb=# INSERT INTO (SELECT * FROM t1 WHERE y1 < 3) VALUES (5, 5);
    INSERT 0 1
    
    -- Insert a subquery with CHECK OPTION specified.
    gaussdb=# INSERT INTO (SELECT * FROM t1 WHERE y1 < 3 WITH CHECK OPTION) VALUES (5, 5);
    ERROR:  new row violates WITH CHECK OPTION for view "__unnamed_subquery__"
    DETAIL:  Failing row contains (5, 5).
    
    -- Insert a subquery with READONLY specified.
    gaussdb=# INSERT INTO (SELECT * FROM t1 WITH READ ONLY) VALUES (5, 5);
    ERROR:  cannot perform a DML operation on a read-only subquery.
    
    -- Insert a multi-table join subquery.
    gaussdb=# INSERT INTO (SELECT * FROM t1, t2 WHERE x1 = x2) (x1, y1) VALUES (2, 2);
    INSERT 0 1
    
    -- Drop a schema.
    gaussdb=# RESET CURRENT_SCHEMA;
    RESET
    gaussdb=# DROP SCHEMA ins_subqry CASCADE;
    NOTICE:  drop cascades to 2 other objects
    DETAIL:  drop cascades to table ins_subqry.t1
    drop cascades to table ins_subqry.t2
    DROP SCHEMA

    Example 2: Insert a view.

    -- Create a schema.
    gaussdb=# CREATE SCHEMA ins_view;
    CREATE SCHEMA
    gaussdb=# SET CURRENT_SCHEMA = 'ins_view';
    SET
    
    -- Create tables.
    gaussdb=# CREATE TABLE t1 (x1 int, y1 int);
    NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'x1' as the distribution column by default.
    HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
    CREATE TABLE
    gaussdb=# CREATE TABLE t2 (x2 int PRIMARY KEY, y2 int);
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
    CREATE TABLE
    
    -- Create a single table view.
    gaussdb=# CREATE VIEW v_ins1 AS SELECT * FROM t1;
    CREATE VIEW
    gaussdb=# CREATE VIEW v_ins2 AS SELECT * FROM t1 WHERE y1 < 3;
    CREATE VIEW
    gaussdb=# CREATE VIEW v_ins2_wco AS SELECT * FROM t1 WHERE y1 < 3 WITH CHECK OPTION;
    CREATE VIEW
    gaussdb=# CREATE VIEW v_ins_read AS SELECT * FROM t1 WITH READ ONLY;
    CREATE VIEW
    
    -- Insert data to t1 through a view.
    gaussdb=# INSERT INTO v_ins1 VALUES (1, 1);
    INSERT 0 1
    gaussdb=# INSERT INTO v_ins2 VALUES (5, 5);
    INSERT 0 1
    gaussdb=# INSERT INTO v_ins2_wco VALUES (5, 5);
    ERROR:  new row violates WITH CHECK OPTION for view "v_ins2_wco"
    DETAIL:  Failing row contains (5, 5).
    
    gaussdb=# INSERT INTO v_ins_read VALUES (5, 5);
    ERROR:  cannot perform a DML operation on a read-only subquery.
    
    -- Create a multi-table view.
    gaussdb=# CREATE VIEW vv_ins AS SELECT * FROM t1, t2 WHERE x1 = x2;
    CREATE VIEW
    
    -- Insert data to t1 through a view.
    gaussdb=# INSERT INTO vv_ins (x1, y1) VALUES (2, 2);
    INSERT 0 1
    
    -- Drop a schema.
    gaussdb=# RESET CURRENT_SCHEMA;
    RESET
    gaussdb=# DROP SCHEMA ins_view CASCADE;
    NOTICE:  drop cascades to 7 other objects
    DETAIL:  drop cascades to table ins_view.t1
    drop cascades to table ins_view.t2
    drop cascades to view ins_view.v_ins1
    drop cascades to view ins_view.v_ins2
    drop cascades to view ins_view.v_ins2_wco
    drop cascades to view ins_view.v_ins_read
    drop cascades to view ins_view.vv_ins
    DROP SCHEMA

Suggestions

  • VALUES

    When you run the INSERT statement to insert data in batches, you are advised to combine multiple records into one statement to improve data loading performance.

    Example:

    INSERT INTO sections VALUES (30, 'Administration', 31, 1900),(40, 'Development', 35, 2000), (50, 'Development' , 60 , 2001);

    If values of an INSERT statement are distributed on a DN, GaussDB can push the statement down to the corresponding DN for execution. Currently, only constants, simple expressions, and pushdown functions (provolatile in pg_proc is set to 'i') are supported. If a column in the table has a default value, the value must be a constant or a simple expression. Neither a single-value statement nor a multi-value statement can be pushed down to a single DN.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback