Truncate text output of COPY command for postgres

Question

I've got a script that copies every table in a schema to a csv using the following:

psql -Atc "select tablename from pg_tables where schemaname='$schema'" --host=$host -w --user=$user $database |\
 while read TBL; do
 psql -c "COPY \"$schema\".\"$TBL\" TO STDOUT WITH DELIMITER AS '|' CSV HEADER" --host=$host -w --user=$user $database > "${TBL}_${timestamp}.csv"
 done

Which by and large works great. Now I've been asked if I can:

• Remove the milliseconds from the timestamp output
• Stop CLOBS from being moe than 6000 characters

Is there any way I can continue doing what I'm doing?

Answer 1

Instead of COPY <table>, you can use COPY (SELECT <columns> FROM <table>), and work the truncation logic into the column list.

Of course, you'll need to build this list based on the type information in the catalog. This should do it:

 SELECT
 string_agg(
 quote_ident(column_name) ||
 CASE udt_name
 WHEN 'timestamp' THEN '::timestamp(0)'
 WHEN 'timestamptz' THEN '::timestamptz(0)'
 WHEN 'text' THEN '::varchar(6000)'
 ELSE ''
 END,
 ','
 )
 FROM information_schema.columns
 WHERE
 table_schema = '$schema' AND
 table_name = '$TBL';

This query just generates the <columns> string to put into the COPY statement. Run it inside your while loop, put the output in a variable (e.g. $columns), and then run psql -c "COPY (SELECT $columns FROM \"$schema\".\"$TBL\")...