CODEWITHSUNDEEP
postgresqlpostgresql-11
William
William

Reputation: 124

PostgreSQL: Alter all tables returned by a select query

I have this query running on a postgres 11.2 db:

SELECT relname
FROM pg_stat_user_tables
WHERE n_mod_since_analyze=0 AND relname LIKE '%' 
    AND relname NOT IN (SELECT relname FROM pg_class WHERE  array_length(reloptions,1)>0);

That returns a list of tables I need to disable autovacuum for. I'm trying to come up with a way to run ALTER TABLE pg_stat_user_tables.relname SET (autovacuum_enabled = false); on all tables returned by my query statement. Googling keeps bringing me back to altering table columns. Is it possible to just take the table names returned from this query and pass it to ALTER TABLE?

Upvotes: 0

Views: 884

Answers (1)

pifor
pifor

Reputation: 7882

Try with psql CLI and \gexec internal command:

SELECT format('ALTER TABLE %s SET (autovacuum_enabled = false);',lt.relname)
FROM
(
 SELECT relname
 FROM pg_stat_user_tables
 WHERE n_mod_since_analyze=0 
 AND relname LIKE '%' 
 AND relname NOT IN (SELECT relname FROM pg_class WHERE  array_length(reloptions,1)>0)
) lt;
\gexec

Execution output:

ALTER TABLE p SET (autovacuum_enabled = false);
ALTER TABLE
ALTER TABLE t1 SET (autovacuum_enabled = false);
ALTER TABLE
ALTER TABLE t2 SET (autovacuum_enabled = false);
ALTER TABLE

Upvotes: 1

Related Questions