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\")...