Truncate text output of COPY command for postgres 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 &gt; "${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?
Instead of COPY &lt;table&gt;, you can use COPY (SELECT &lt;columns&gt; FROM &lt;table&gt;), 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 &lt;columns&gt; 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\")...