Re: AW: Truncate data from whole cluster

Thank you all. I found function already that has to executed connecting to
each db one by one. I thought to have one command that takes care of
everything..Anyways...thank you

On Wed, 20 Dec 2023, 17:20 hubert depesz lubaczewski, <depesz(at)depesz(dot)com>
wrote:

> On Wed, Dec 20, 2023 at 11:39:40AM +0000, Dischner, Anton wrote:
> > Hi all,
> >
> > if you ask Bard LLM do do this you get:
> >
> > #!/bin/bash
> >
> > # List all databases in the PostgreSQL instance databases=$(psql -t -c
> "SELECT datname FROM pg_database;")
> >
> > # Truncate table data for each database
> > for database in $databases; do
> > echo "Processing database '$database'..."
> > psql -d $database -c "SET FOREIGN_KEY_CHECKS=0;"
> > tables=$(psql -d $database -t -c "SELECT tablename FROM
> pg_catalog.pg_tables WHERE schemaname = 'public';")
> > for table in $tables; do
> > echo "Truncating table '$table' in database '$database'..."
> > psql -d $database -c "TRUNCATE TABLE $table;"
> > done
> > psql -d $database -c "SET FOREIGN_KEY_CHECKS=1;"
> > done
> >
> > This may be a good script to start.
> > You will definetly test this first on a test installation!
>
> No, it's not.
> it's full of errors, and hallucinations.
>
> couple of things that took me < 1 minute to notice:
>
> 1. will fail for database names that contain spaces in them
> 2. setting variable like psql -d ... -c "set .." - is pointless, the
> value will be active only for this session, but this session has just
> eneded
> 3. there is no "FOREIGN_KEY_CHECKS" setting in pg
> 4. will fail for table names with spaces
> 5. will ignore tables in non-public schemas
> 6. will fail for table names with UpperCaseLetters
> 7. for db with N tables (in public), it will call 101 psql connections
> to this database. if your db has many small tables it will be
> unreasonably slow
>
> depesz, cursing whatever "magic ai tool" people try to use as base for
> their scripts
>