Updated on 2024-05-07 GMT+08:00

Inserting Data to a Table

A new table contains no data. You need to insert data to the table before using it. This section describes how to insert a row or multiple rows of data using the INSERT command and to insert data from a specified table. Contact the administrator if a large amount of data needs to be imported.

Context

The length of a character on the server and client may vary by the character sets they use. A string entered on the client will be processed based on the server's character set, so the result may differ from expected.

Table 1 Comparison of character set output between the client and server

Procedure

Server and Client Use Same Encoding

Server and Client Use Different Encoding

No operations are performed on the string while it is saved and read.

Your expected result is returned.

If the encoding for input and output on the client is the same, your expected result is returned.

Operations (such as executing string functions) are performed to the string while it is saved and read.

Your expected result is returned.

The result may not be as expected, depending on the operations performed on the string.

A long string is truncated while it is saved.

Your expected result is returned.

If the character sets used on the client and server have different character length, the result may not be as expected.

More than one of the preceding operations can be performed to a string. For example, if the character sets of the client and server are different, a string may be processed and then truncated. In this case, the result will also be unexpected. For details, see Table 2.

Long strings are truncated only if DBCOMPATIBILITY is set to TD (compatible with Teradata) and the GUC parameter td_compatible_truncation is set to on.

Create table1 and table2 to be used in the example.

1
2
gaussdb=# CREATE TABLE table1(id int, a char(6), b varchar(6),c varchar(6));
gaussdb=# CREATE TABLE table2(id int, a char(20), b varchar(20),c varchar(20));
Table 2 Example

No.

Server Character Set

Client Character Set

Automatic Truncation Enabled

Example

Result

Description

1

SQL_ASCII

UTF8

Yes

1
gaussdb=# INSERT INTO table1 VALUES(1,reverse('123AA78'),reverse(''123AA78''),reverse('123AA78'));
1
2
3
id |a|b|c
----+------+------+------
1 | 87| 87| 87

A string is reversed on the server and then truncated. Because character sets used by the server and client are different, character A is displayed in multiple bytes on the server and the result is incorrect.

2

SQL_ASCII

UTF8

Yes

1
gaussdb=# INSERT INTO table1 VALUES(2,reverse('123A78'),reverse('123A78'),reverse('123A78'));
1
2
3
id |a|b|c
----+------+------+------
2 | 873| 873| 873

A string is reversed and then automatically truncated. Therefore, the result is unexpected.

3

SQL_ASCII

UTF8

Yes

1
gaussdb=# INSERT INTO table1 VALUES(3,'87A123','87A123','87A123');
1
2
3
id |   a   |   b   |   c
----+-------+-------+-------
  3 | 87A1 | 87A1 | 87A1

The column length in the string type is an integer multiple of the length in client character encoding. Therefore, the result is correct after truncation.

4

SQL_ASCII

UTF8

No

1
2
gaussdb=# INSERT INTO table2 VALUES(1,reverse('123AA78'),reverse('123AA78'),reverse('123AA78'));
gaussdb=# INSERT INTO table2 VALUES(2,reverse('123A78'),reverse('123A78'),reverse('123A78'));
1
2
3
4
id |a|b|c
----+-------------------+--------+--------
1 | 87 321| 87 321 | 87 321
2 | 87321| 87321| 87321

Similar to the first example, multi-byte characters no longer indicate the original characters after being reversed.

Procedure

You need to create a table before inserting data to it. For details about how to create a table, see Creating and Managing Tables.

  • Insert a row to table customer_t1.
    Data values are arranged in the same order as the columns in the table and are separated by commas (,). Generally, column values are text values (constants). But column values can also be scalar expressions.
    1
    gaussdb=# INSERT INTO customer_t1(c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', 'Grace');
    
    If you know the sequence of the columns in the table, you can obtain the same result without listing these columns. For example, the following command generates the same result as the preceding command:
    1
    gaussdb=# INSERT INTO customer_t1 VALUES (3769, 'hello', 'Grace');
    
    If you do not know some of the column values, you can omit them. If no value is specified for a column, the column is set to the default value. Example:
    1
    2
    3
    gaussdb=# INSERT INTO customer_t1 (c_customer_sk, c_first_name) VALUES (3769, 'Grace');
    
    gaussdb=# INSERT INTO customer_t1 VALUES (3769, 'hello');
    
    You can also specify the default value of a column or row:
    1
    2
    3
    gaussdb=# INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', DEFAULT);
    
    gaussdb=# INSERT INTO customer_t1 DEFAULT VALUES;
    
  • Insert multiple rows to a table.
    1
    2
    3
    4
    gaussdb=# INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES 
        (6885, 'maps', 'Joes'),
        (4321, 'tpcds', 'Lily'),
        (9527, 'world', 'James');
    

    You can also insert multiple rows by running the command for inserting one row for multiple times. However, you are advised to run this command to improve efficiency.

  • Assume that you have created a backup table customer_t2 for table customer_t1. To insert data from customer_t1 to customer_t2, run the following commands:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    gaussdb=# CREATE TABLE customer_t2
    (
        c_customer_sk             integer,
        c_customer_id             char(5),
        c_first_name              char(6),
        c_last_name               char(8)
    );
    
    gaussdb=# INSERT INTO customer_t2 SELECT * FROM customer_t1;
    

    If implicit conversion is not implemented between the column data types of the specified table and those of the current table, the two tables must have the same column data types when data is inserted from the specified table to the current table.

  • Delete a backup table.
    1
    gaussdb=# DROP TABLE customer_t2 CASCADE;
    

    If the table to be deleted is dependent on other tables, you need to delete its dependent tables first.