INSERT语法
更新时间 2025-02-14 10:22:02
最近更新时间: 2025-02-14 10:22:02
本页介绍天翼云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 1insert..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)
