INSERT語法
更新時間 2025-02-05 09:36:44
最近更新時間: 2025-02-05 09:36:44
分享文章
本頁介紹天翼云TeleDB數據庫的INSERT語法。
插入單條記錄
指定所有字段。
teledb=# insert into teledb_pg1(id, nickname) values(1, 'teledb');
INSERT 0 1
指定某些字段，不指定時，如果該字段有默認值則會帶上默認值。
teledb=# insert into teledb_pg1(id) values(3);
INSERT 0 1
字段順序可以任意排列。
teledb=# insert into teledb_pg1 (nickname, id) values('teledb', 7);
INSERT 0 1
使用default 關鍵字，即值為建表時指定的默認值方式。
teledb=# insert into teledb_pg1(id, nickname) values(default, 'teledb default');
INSERT 0 1
插入多條記錄
teledb=# insert into teledb_pg1(id,nickname) values(1,'hello teledb'),(2,'teledx好');
COPY 2
teledb=# select * from teledb_pg1;
id | nickname
----+---------------
1 | hello teledb
2 | teledx好
(2 rows)
使用子查詢插入數據
teledb=# insert into teledb_pg1(id,nickname) values(1,(select relname from pg_class limit 1));
INSERT 0 1
teledb=# select * from teledb_pg1;
id | nickname
----+---------------
1 | hello teledb
2 | teledx好
1 | db_pipes
(3 rows)
從另外一個表取數據進行批量插入
teledb=# insert into teledb_pg1(nickname) select nickname from teledb_pg1 limit 2;
INSERT 0 2
teledb=# select * from teledb_pg1;
id | nickname
----+---------------
1 | hello teledb
2 | teledx好
0 | hello teledb
0 | teledx好
(4 rows)
大批量的生成數據
teledb=# truncate table teledb_pg1;
TRUNCATE TABLE
teledb=# insert into teledb_pg1 select t,md5(random()::text) from generate_series(1,10000) as t;
INSERT 0 10000
teledb=# select count(1) from teledb_pg1;
count
-------
10000
(1 row)
返回插入數據，輕松獲取插入記錄的serial值
teledb=# create table teledb_serial(id serial, nickname varchar);
CREATE TABLE
teledb=# insert into teledb_serial(nickname) values('hello teledb') returning *;
id | nickname
----+---------------
1 | hello teledb
(1 row)
INSERT 0 1
-- 指定返回的字段。
teledb=# insert into teledb_serial(nickname) values('hello teledb') returning id;
id
----
2
(1 row)
INSERT 0 1
insert..update更新
使用on conflict
teledb=# create table t_update(id int unique, name varchar);
CREATE TABLE
teledb=# \d+ t_update
Table "public.t_update"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | character varying | | | | extended | |
Indexes:
"t_update_id_key" UNIQUE CONSTRAINT, btree (id)
Distribute By: SHARD(id)
Location Nodes: ALL DATANODES
teledb=# insert into t_update values(1,'teledb');
INSERT 0 1
teledb=# insert into t_update values(1,'teledb') on conflict(id) do update set name = 'hello';
INSERT 0 1
teledb=# select * from t_update ;
id | name
----+-------
1 | hello
(1 row)
