插入单条记录

指定所有字段。

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)