Inserting Data to Tables

Context

In case of different character sets used on the client and server, the length of a character on both ends may differ. A string entered on the client will be processed on the server based on the server's character set, and therefore the result may differ from the expected result.

More than one of the preceding operations may be performed on a string. For example, if the character sets of the client and server are different and the string is truncated after being processed, the result will also be unexpected. For details, see Table 2.

Ultra long strings are truncated only if DBCOMPATIBILITY is set to TD and the GUC parameter td_compatible_truncation is set to on.

Create table1 and table2 to be used in the examples.

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));

1

gaussdb=# INSERT INTO table1 VALUES(1,reverse('123AA78'),reverse('123AA78'),reverse('123AA78'));

1
2
3

id |a|b|c
----+------+------+------
1 | 87| 87| 87

1

gaussdb=# INSERT INTO table1 VALUES(2,reverse('123A78'),reverse('123A78'),reverse('123A78'));

1
2
3

id |a|b|c
----+------+------+------
2 | 873| 873| 873

1

gaussdb=# INSERT INTO table1 VALUES(3,'87A123','87A123','87A123');

1
2
3

id |   a   |   b   |   c
----+-------+-------+-------
  3 | 87A1 | 87A1 | 87A1

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

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 scalar expressions are also allowed.

  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. In the INSERT statement, if the column name of the specified target table is not displayed, the values to be inserted in the VALUES clause correspond to the columns of the target table based on the column number. That is, the first value in the VALUES clause corresponds to the first column of the target table, the second value of the VALUES clause corresponds to the second column of the target table, and so on. Columns that do not have corresponding values in the VALUES clause are automatically filled with default values or NULL. For 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;

• To insert multiple rows to a table, run the following command:

  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.

• Insert data into a table by using a table alias without the keyword AS.

  1 2 3

  gaussdb=# CREATE TABLE T1(A INT,B TEXT,C TIMESTAMP,D FLOAT); gaussdb=# INSERT INTO T1 T VALUES(1,'HA','1997-01-01 10:00:00'::TIMESTAMP,1.234); gaussdb=# INSERT INTO T1 TT(B,C,D) VALUES('HA','1997-01-01 10:00:00'::TIMESTAMP,1.234);

  

  • When the INSERT statement is used without AS, the table alias cannot be a keyword (such as SELECT or VALUE) or an expression. The alias must comply with the identifier naming rule.
  • When the INSERT statement is used without AS, the table alias does not support this format: INSERT INTO table_name alias_name(alias_name.col1,...,alias_name.coln) VALUES(xxx).
  • To insert data into a partitioned table by using a table alias without the keyword AS, the target partition cannot be specified.
• 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 the corresponding tables first.