2

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.