The content of this page has been automatically translated by AI. If you encounter any problems while reading, you can view the corresponding content in Chinese.
Help & Documentation>Tencent Cloud TCHouse-P>Development Guide>Basic Database Development>INSERT statement

INSERT statement

Last updated: 2024-08-22 17:00:40

On this page:

SQL INSERT statement

Insert a single record

Specify all fields:
  postgres=# insert into tbase(id, nickname) values(1, 'hello TBase');
Result: INSERT 0 1
Specify some fields; unspecified fields will use default values:
postgres=# insert into tbase(nickname) values('TBase 好');
Result: INSERT 0 1
If no fields are specified, all fields will be as defined during table creation:
postgres=# insert into tbase values(nextval('t_id_seq'::regclass), 'TBase 好');
Result: INSERT 0 1
The order of fields can be arranged arbitrarily:
postgres=# insert into tbase(nickname, id) values('TBase swap', 5);
Result: INSERT 0 1
Use the default keyword to set values to their default as specified during table creation:
postgres=# insert into tbase(id, nickname) values(default, 'TBase default');
Result: INSERT 0 1
Data after inserting the records:
postgres=# select * from tbase;
id
nickname
1
hello TBase
2
TBase OK
5
TBase swap
3
TBase OK
4
TBase default

Insert multiple records

Insert multiple records:
postgres=# insert into tbase(id, nickname) values(1, 'hello TBase'), (2, 'TBase OK');
Result: INSERT 0 2
Query result after insertion:
postgres=# select * from tbase;
id
nickname
1
hello TBase
2
TBase OK

Insert data using subquery

Insert data using subquery:
postgres=# insert into tbase(id, nickname) values(1, (select relname from pg_class limit 1));
Result: INSERT 0 1
Query result after insertion:
postgres=# select * from tbase;
id
nickname
1
pg_statistic

Batch insert data from another table

Batch insert data from another table:
postgres=# insert into tbase(nickname) select relname from pg_class limit 3;
Result: INSERT 0 3
Query result after insertion:
postgres=# select * from tbase;
id
nickname
5
pg_type
6
pg_toast_2619
4
pg_statistic

Generating data in bulk

Using the generate_series function to generate data in bulk:
postgres=# insert into tbase select t, md5(random()::text) from generate_series(1,10000) as t;
Result: INSERT 0 10000
Count after insertion:
postgres=# select count(1) from tbase;
count
10000

Return the inserted data, easily get the inserted record's serial value

Insert record and return all fields:
postgres=# insert into tbase(nickname) values('TBase OK') returning *;
id
nickname
7
TBase OK
Specify return fields:
postgres=# insert into tbase(nickname) values('hello TBase') returning id;
id
8

Insert..Update update

Use the ON CONFLICT clause to update:
postgres=# insert into t values(1,'pgxz') ON CONFLICT (id) DO UPDATE SET nc = 'tbase';
Result: INSERT 0 1
Query result after update:
postgres=# select * from t;
id
nc
1
tbase

insert all

Use the insert all statement:
postgres=# create table t5(f1 int, f2 int);
postgres=# create table t6(f1 int, f2 int);
postgres=# insert all into t5 values(1, 1) into t6 values(1, 1) select 1 as f1, 1 as f2;
Result: INSERT 0 2
Query t5 table:
postgres=# select * from t5;
f1
f2
1
1
Query t6 table:
postgres=# select * from t6;
f1
f2
1
1



中国站 - English