Postgresql dynamic query in COPY stament with psql command

Question

I am trying to get maintenance query from windows command line psql with COPY statement.(Windows 2012). The query produces maintenance query as output file. After that I wish to import maintenance query (reindex.sql) and execute with a scheduler. But I couldn't find it out why I couldnt execute script. There should be a comma trick which I couldn't solve.

psql -U postgres -c "COPY( SELECT 'REINDEX TABLE "' || schemaname || '"."' || relname || '";'FROM pg_stat_all_tables ORDER BY n_dead_tup DESC)TO 'E:\scripts\maintenance\reindex.sql';"

Could you describe "does not work" in some more detail?

Laurenz Albe
– Laurenz Albe

2018-10-25 10:17:36 +00:00
Commented Oct 25, 2018 at 10:17 — Laurenz Albe
– Laurenz Albe, Commented Oct 25, 2018 at 10:17

klin · Accepted Answer · 2018-10-25 10:37:28Z

1

Use format():

psql -U postgres -c ^
"COPY ( ^
SELECT format('REINDEX TABLE %s.%s', schemaname, relname) ^
FROM pg_stat_all_tables ^
ORDER BY n_dead_tup DESC) ^
TO 'E:\scripts\maintenance\reindex.sql'"

answered Oct 25, 2018 at 10:37

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql dynamic query in COPY stament with psql command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related