我经常需要在重建之前删除PostgreSQL数据库中的所有数据。如何在SQL中直接做到这一点?
目前,我已经设法想出了一个SQL语句,返回我需要执行的所有命令:
SELECT 'TRUNCATE TABLE ' || tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';
但是,一旦我有了它们,我就看不到以编程方式执行它们的方法。
我经常需要在重建之前删除PostgreSQL数据库中的所有数据。如何在SQL中直接做到这一点?
目前,我已经设法想出了一个SQL语句,返回我需要执行的所有命令:
SELECT 'TRUNCATE TABLE ' || tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';
但是,一旦我有了它们,我就看不到以编程方式执行它们的方法。
当前回答
您可以使用类似的方法来获取所有截断查询。
SELECT 'TRUNCATE TABLE ' || table_name || ';'
FROM information_schema.tables
WHERE table_schema='schema_name'
AND table_type='BASE TABLE';
其他回答
在这种情况下,最好使用一个空数据库作为模板,当需要刷新时,删除现有数据库并从模板创建一个新数据库。
清洗AUTO_INCREMENT版本:
CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
statements CURSOR FOR
SELECT tablename FROM pg_tables
WHERE tableowner = username AND schemaname = 'public';
BEGIN
FOR stmt IN statements LOOP
EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
IF EXISTS (
SELECT column_name
FROM information_schema.columns
WHERE table_name=quote_ident(stmt.tablename) and column_name='id'
) THEN
EXECUTE 'ALTER SEQUENCE ' || quote_ident(stmt.tablename) || '_id_seq RESTART WITH 1';
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
如果你会使用psql,你可以使用\gexec元命令来执行查询输出;
SELECT
format('TRUNCATE TABLE %I.%I', ns.nspname, c.relname)
FROM pg_namespace ns
JOIN pg_class c ON ns.oid = c.relnamespace
JOIN pg_roles r ON r.oid = c.relowner
WHERE
ns.nspname = 'table schema' AND -- add table schema criteria
r.rolname = 'table owner' AND -- add table owner criteria
ns.nspname NOT IN ('pg_catalog', 'information_schema') AND -- exclude system schemas
c.relkind = 'r' AND -- tables only
has_table_privilege(c.oid, 'TRUNCATE') -- check current user has truncate privilege
\gexec
注意,\gexec是在9.6版本中引入的
您可以使用类似的方法来获取所有截断查询。
SELECT 'TRUNCATE TABLE ' || table_name || ';'
FROM information_schema.tables
WHERE table_schema='schema_name'
AND table_type='BASE TABLE';
您可以使用动态SQL依次执行每条语句吗?您可能必须编写一个PL/pgSQL脚本来完成此操作。
http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html(第38.5.4节)。执行动态命令)