PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to generate all the delete commands for all the tables of a database?

I am trying to generate all the commands to empty all the tables in my database. Right now I’m using DBeaver and my database is PostgreSQL:

DO $$
DECLARE
    table_name TEXT;
    delete_commands TEXT := '';
BEGIN
    FOR table_rec IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'xxx'
          AND table_type = 'BASE TABLE'
    LOOP
        delete_commands := delete_commands || 'DELETE FROM xxx.' || quote_ident(table_rec.table_name) || ';' || E'n';
    END LOOP;
    
    RAISE NOTICE 'Lista de comandos DELETE generada: %', delete_commands;
END $$;

I’m having problems with the request. Does someone know how to fix it?

My actual error is:

SQL Error [42601]: Unterminated dollar quote started at position 4 in SQL END $$;. Expected terminating $$

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found the solution:

    do $$DECLARE
    table_rec RECORD;
    delete_commands TEXT := '';
BEGIN
    FOR table_rec IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'xxx'
          AND table_type = 'BASE TABLE'
    LOOP
        delete_commands := delete_commands || 'DELETE FROM xxx.' || quote_ident(table_rec.table_name) || ';' || E'n';
    END LOOP;
    
    RAISE NOTICE 'Lista de comandos DELETE generada: %', delete_commands;
END $$;

  2. 0

    You don’t need a loop for this:

    SELECT STRING_AGG('', FORMAT('DELETE FROM %I.%I;', table_schema, table_name))
FROM information_schema.tables
WHERE table_schema = 'xxx'
  AND table_type = 'BASE TABLE';

    The function format() takes care of the double quotes, if needed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search