带条件删除

teledb=# select * from t_update ;
 id |     name     | age 
----+--------------+-----
  1 | multi_column |   0
  2 | test         |   3
  3 | 123          |   4
  4 |              |   4
(4 rows)

teledb=# delete from t_update where id = 3;
DELETE 1

null 条件的表达方式。

teledb=# delete from t_update where name is null;
DELETE 1
teledb=# select * from t_update ;
 id |     name     | age 
----+--------------+-----
  1 | multi_column |   0
  2 | test         |   3
(2 rows)

多表关联删除数据

teledb=# select * from t_update ;
 id |     name     | age 
----+--------------+-----
  1 | multi_column |   0
  2 | test         |   3
(2 rows)
teledb=# select * from teledb_serial;
 id | nickname  
----+-----------
  1 | returning
(1 row)

teledb=# delete from t_update using teledb_serial where t_update.id = teledb_serial.id;
DELETE 1
teledb=# select * from t_update ;
 id | name | age 
----+------+-----
  2 | test |   3
(1 row)

返回删除数据

teledb=# delete from t_update returning *;
 id | name | age 
----+------+-----
  2 | test |   3
(1 row)

DELETE 1

returning 特性可以返回 DML（insert、update、delete）修改的数据，降低应用复杂度。

删除所有数据

teledb=# insert into t_update select t,random()::text from generate_series(1,100000) as t;
INSERT 0 100000
Time: 235.715 ms
teledb=# delete from t_update;
DELETE 100000
Time: 44.429 ms

使用truncate 方法是全表删除更高效的方法。

teledb=# insert into t_update select t,random()::text from generate_series(1,100000) as t;
INSERT 0 100000
Time: 244.021 ms
teledb=# truncate table t_update;
TRUNCATE TABLE
Time: 21.953 ms

数据表使用复合类型

teledb=# create table t_my_type (f1 int,f_my_type my_type);
CREATE TABLE
teledb=# insert into t_my_type values(1,row(1,'teledb'));
INSERT 0 1
teledb=# select * from t_my_type;
 f1 |  f_my_type  
----+-------------
  1 | (1,teledb)
(1 row)