语法示例

-- PURGE TABLE table_name; -- --查看回收站gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------(0 rows)gaussdb=# DROP TABLE IF EXISTS flashtest;NOTICE:  table "flashtest" does not exist, skippingDROP TABLEgaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------(0 rows)--创建表flashtestgaussdb=# CREATE TABLE IF NOT EXISTS flashtest(id int, name text) with (storage_type = ustore);CREATE TABLE--插入数据gaussdb=# INSERT INTO flashtest VALUES(1, 'A');INSERT 0 1gaussdb=# SELECT * FROM flashtest; id | name ----+------  1 | A(1 row)--DROP表flashtestgaussdb=# DROP TABLE IF EXISTS flashtest;DROP TABLE--查看回收站，删除的表被放入回收站gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------     18591 |   12737 |    18585 | BIN$31C14EB4899$9737$0==$0   | flashtest            | d            |       0 |      79352606 | 2023-09-13 20:01:28.640664+08 |     79352595 |     79352595 |         2200 |       10 |             0 |          18585 | t             | t           | 225492       |         225492 |     18591 |   12737 |    18588 | BIN$31C14EB489C$12D1BF60==$0 | pg_toast_18585       | d            |       2 |      79352606 | 2023-09-13 20:01:28.641018+08 |            0 |            0 |           99 |       10 |             0 |          18588 | f             | f           | 225492       |         225492 |(2 rows)--查看表flashtest，表不存在gaussdb=# SELECT * FROM flashtest;ERROR:  relation "flashtest" does not existLINE 1: select * from flashtest;                      ^--PURGE表，将回收站中的表删除gaussdb=# PURGE TABLE flashtest;PURGE TABLE--查看回收站，回收站中的表被删除gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------(0 rows)-- PURGE INDEX index_name; --gaussdb=# DROP TABLE IF EXISTS flashtest;NOTICE:  table "flashtest" does not exist, skippingDROP TABLE--创建表flashtestgaussdb=# CREATE TABLE IF NOT EXISTS flashtest(id int, name text) with (storage_type = ustore);CREATE TABLE--为表flashtest创建索引flashtest_indexgaussdb=# CREATE INDEX flashtest_index ON flashtest(id);CREATE INDEX--DROP表gaussdb=# DROP TABLE IF EXISTS flashtest;DROP TABLE--查看回收站gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------     18648 |   12737 |    18641 | BIN$31C14EB48D1$9A85$0==$0   | flashtest            | d            |       0 |      79354509 | 2023-09-13 20:40:11.360638+08 |     79354506 |     79354508 |         2200 |       10 |             0 |          18641 | t             | t           | 226642       |         226642 |     18648 |   12737 |    18644 | BIN$31C14EB48D4$12E236A0==$0 | pg_toast_18641       | d            |       2 |      79354509 | 2023-09-13 20:40:11.36112+08  |            0 |            0 |           99 |       10 |             0 |          18644 | f             | f           | 226642       |         226642 |     18648 |   12737 |    18647 | BIN$31C14EB48D7$9A85$0==$0   | flashtest_index      | d            |       1 |      79354509 | 2023-09-13 20:40:11.361246+08 |     79354508 |     79354508 |         2200 |       10 |             0 |          18647 | f             | t           | 0            |              0 |(3 rows)--PURGE索引flashtest_indexgaussdb=# PURGE INDEX flashtest_index;PURGE INDEX--查看回收站，回收站中的索引flashtest_index被删除gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------     18648 |   12737 |    18641 | BIN$31C14EB48D1$9A85$0==$0   | flashtest            | d            |       0 |      79354509 | 2023-09-13 20:40:11.360638+08 |     79354506 |     79354508 |         2200 |       10 |             0 |          18641 | t             | t           | 226642       |         226642 |     18648 |   12737 |    18644 | BIN$31C14EB48D4$12E236A0==$0 | pg_toast_18641       | d            |       2 |      79354509 | 2023-09-13 20:40:11.36112+08  |            0 |            0 |           99 |       10 |             0 |          18644 | f             | f           | 226642       |         226642 |(2 rows)-- PURGE RECYCLEBIN ----PURGE回收站gaussdb=# PURGE RECYCLEBIN;PURGE RECYCLEBIN--查看回收站，回收站被清空gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------(0 rows)-- TIMECAPSULE TABLE { table_name } TO BEFORE DROP [RENAME TO new_tablename] --gaussdb=# DROP TABLE IF EXISTS flashtest;NOTICE:  table "flashtest" does not exist, skippingDROP TABLE--创建表flashtestgaussdb=# CREATE TABLE IF NOT EXISTS flashtest(id int, name text) with (storage_type = ustore);CREATE TABLE--插入数据gaussdb=# INSERT INTO flashtest VALUES(1, 'A');INSERT 0 1gaussdb=# SELECT * FROM flashtest; id | name ----+------  1 | A(1 row)--DROP表gaussdb=# DROP TABLE IF EXISTS flashtest;DROP TABLE--查看回收站，表被放入回收站gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------     18658 |   12737 |    18652 | BIN$31C14EB48DC$9B2B$0==$0   | flashtest            | d            |       0 |      79354760 | 2023-09-13 20:47:57.075907+08 |     79354753 |     79354753 |         2200 |       10 |             0 |          18652 | t             | t           | 226824       |         226824 |     18658 |   12737 |    18655 | BIN$31C14EB48DF$12E46400==$0 | pg_toast_18652       | d            |       2 |      79354760 | 2023-09-13 20:47:57.07621+08  |            0 |            0 |           99 |       10 |             0 |          18655 | f             | f           | 226824       |         226824 |(2 rows)--查看表，表不存在gaussdb=# SELECT * FROM flashtest;ERROR:  relation "flashtest" does not existLINE 1: select * from flashtest;                      ^--闪回drop表gaussdb=# TIMECAPSULE TABLE flashtest to before drop;TimeCapsule Table--查看表，表被恢复到drop之前gaussdb=# SELECT * FROM flashtest; id | name ----+------  1 | A(1 row)--查看回收站，回收站中的表被删除gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------(0 rows)--DROP表gaussdb=# DROP TABLE IF EXISTS flashtest;DROP TABLEgaussdb=# SELECT * FROM flashtest;ERROR:  relation "flashtest" does not existLINE 1: select * from flashtest;                      ^--查看回收站，表被放入回收站gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid |           rcyname            |        rcyoriginname         | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+------------------------------+------------------------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------     18664 |   12737 |    18652 | BIN$31C14EB48DC$9B4E$0==$0   | flashtest                    | d            |       0 |      79354845 | 2023-09-13 20:49:17.762977+08 |     79354753 |     79354753 |         2200 |       10 |             0 |          18652 | t             | t           | 226824       |         226824 |     18664 |   12737 |    18657 | BIN$31C14EB48E1$12E680A8==$0 | BIN$31C14EB48E1$12E45E00==$0 | d            |       3 |      79354845 | 2023-09-13 20:49:17.763271+08 |     79354753 |     79354753 |           99 |       10 |             0 |          18657 | f             | f           | 0            |              0 |     18664 |   12737 |    18655 | BIN$31C14EB48DF$12E68698==$0 | BIN$31C14EB48DF$12E46400==$0 | d            |       2 |      79354845 | 2023-09-13 20:49:17.763343+08 |            0 |            0 |           99 |       10 |             0 |          18655 | f             | f           | 226824       |         226824 |(3 rows)--闪回drop表，表名用回收站中的rcynamegaussdb=# TIMECAPSULE TABLE "BIN$31C14EB48DC$9B4E$0==$0" to before drop;TimeCapsule Table--查看回收站，回收站中的表被删除gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------(0 rows)gaussdb=# SELECT * FROM flashtest; id | name ----+------  1 | A(1 row)--DROP表gaussdb=# DROP TABLE IF EXISTS flashtest;DROP TABLE--查看回收站，表被放入回收站gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid |           rcyname            |        rcyoriginname         | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+------------------------------+------------------------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------     18667 |   12737 |    18652 | BIN$31C14EB48DC$9B8D$0==$0   | flashtest                    | d            |       0 |      79354943 | 2023-09-13 20:52:14.525946+08 |     79354753 |     79354753 |         2200 |       10 |             0 |          18652 | t             | t           | 226824       |         226824 |     18667 |   12737 |    18657 | BIN$31C14EB48E1$1320B4F0==$0 | BIN$31C14EB48E1$12E680A8==$0 | d            |       3 |      79354943 | 2023-09-13 20:52:14.526319+08 |     79354753 |     79354753 |           99 |       10 |             0 |          18657 | f             | f           | 0            |              0 |     18667 |   12737 |    18655 | BIN$31C14EB48DF$1320BAE0==$0 | BIN$31C14EB48DF$12E68698==$0 | d            |       2 |      79354943 | 2023-09-13 20:52:14.526423+08 |            0 |            0 |           99 |       10 |             0 |          18655 | f             | f           | 226824       |         226824 |(3 rows)--查看表，表不存在gaussdb=# SELECT * FROM flashtest;ERROR:  relation "flashtest" does not existLINE 1: SELECT * FROM flashtest;                      ^--闪回drop表，并重命名表gaussdb=# TIMECAPSULE TABLE flashtest to before drop rename to flashtest_rename;TimeCapsule Table--查看原表，表不存在gaussdb=# SELECT * FROM flashtest;ERROR:  relation "flashtest" does not existLINE 1: SELECT * FROM flashtest;                      ^--查看重命名后的表，表存在gaussdb=# SELECT * FROM flashtest_rename; id | name ----+------  1 | A(1 row)--查看回收站，回收站中的表被删除gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------(0 rows)--drop表gaussdb=# DROP TABLE IF EXISTS flashtest_rename;DROP TABLE--清空回收站gaussdb=# PURGE RECYCLEBIN;PURGE RECYCLEBIN--查看回收站，回收站被清空gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------(0 rows)-- TIMECAPSULE TABLE { table_name } TO BEFORE TRUNCATE --gaussdb=# DROP TABLE IF EXISTS flashtest;NOTICE:  table "flashtest" does not exist, skippingDROP TABLE--创建表flashtestgaussdb=# CREATE TABLE IF NOT EXISTS flashtest(id int, name text) with (storage_type = ustore);CREATE TABLE--插入数据gaussdb=# INSERT INTO flashtest VALUES(1, 'A');INSERT 0 1gaussdb=# SELECT * FROM flashtest; id | name ----+------  1 | A(1 row)--truncate表gaussdb=# TRUNCATE TABLE flashtest;TRUNCATE TABLE--查看回收站，表的数据被放入回收站gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------     18703 |   12737 |    18697 | BIN$31C14EB4909$9E4C$0==$0   | flashtest            | t            |       0 |      79356608 | 2023-09-13 21:24:42.819863+08 |     79356606 |     79356606 |         2200 |       10 |             0 |          18697 | t             | t           | 227927       |         227927 |     18703 |   12737 |    18700 | BIN$31C14EB490C$132FE3F0==$0 | pg_toast_18697       | t            |       2 |      79356608 | 2023-09-13 21:24:42.820358+08 |            0 |            0 |           99 |       10 |             0 |          18700 | f             | f           | 227927       |         227927 |(2 rows)--查看表，表中的数据为空gaussdb=# SELECT * FROM flashtest; id | name ----+------(0 rows)--闪回truncate表gaussdb=# TIMECAPSULE TABLE flashtest to before truncate;TimeCapsule Table--查看表，表中的数据被恢复gaussdb=# SELECT * FROM flashtest; id | name ----+------  1 | A(1 row)--查看回收站gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid |           rcyname            |    rcyoriginname     | rcyoperation | rcytype | rcyrecyclecsn |        rcyrecycletime         | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+------------------------------+----------------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------     18703 |   12737 |    18700 | BIN$31C14EB490C$13300228==$0 | pg_toast_18697       | t            |       2 |      79356610 | 2023-09-13 21:24:42.872732+08 |            0 |            0 |           99 |       10 |             0 |          18706 | f             | f           | 0            |         227928 |     18703 |   12737 |    18697 | BIN$31C14EB4909$9E4D$0==$0   | flashtest            | t            |       0 |      79356610 | 2023-09-13 21:24:42.872792+08 |     79356606 |     79356606 |         2200 |       10 |             0 |          18704 | t             | t           | 0            |         227928 |(2 rows)--drop表gaussdb=# DROP TABLE IF EXISTS flashtest;DROP TABLE--清空回收站gaussdb=# PURGE RECYCLEBIN;PURGE RECYCLEBIN--查看回收站，回收站被清空gaussdb=# SELECT * FROM gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+---------------+-------------+--------------+----------------+-----------(0 rows)