What's the best way to do a psql export that does a regex replace across all columns?

user_4151218 0
Viewed 72

I'm running PSQL exports to CSV files for a few tables. They look like the below:

COPY table_name TO 'file_name' CSV

The issue is that some of these tables have text fields, in which the values for these fields contain both the delimiter (commas) and newlines. What would be the best way to do the export while removing the newlines across all columns?

Example table:

field1,field2,field3,field4
field1,field2,"field3, with, the delimiter",field4
field1,field2,"field3, with, the
delimiter and newline",field4
field1,"field2 with a
newline",field3,field4

How I'd want my export to look:

field1,field2,field3,field4
field1,field2,"field3, with, the delimiter",field4
field1,field2,"field3, with, the delimiter and newline",field4
field1,"field2 with a newline",field3,field4

Some solutions I've been considering:

  1. Write a custom regex replace function and update the tables before I do the export.
  2. See if there is a way to do the replace during the export transaction (is this possible?).
  3. Perform the export as is and use another library/language to post-process the exported CSV.

Thanks for the help!

postgresql sql regex
1 Answers

You can automatically compose an appropriate COPY statement with this SQL statement:

SELECT format(
          'COPY (SELECT %s FROM %I.%I) TO ''filename'' (FORMAT ''csv'');',
          string_agg(
             format(
                CASE WHEN data_type IN ('text', 'character varying', 'character')
                     THEN 'translate(%I, E''\n,'', '''')'
                     ELSE '%I'
                END,
                column_name
             ),
             ', '
             ORDER BY ordinal_position
          ),
          table_schema,
          table_name
       )
FROM information_schema.columns
WHERE table_schema = 'schema_name'
  AND table_name = 'table_name'
GROUP BY table_schema, table_name;

If you are using psql, you can replace the final semicolon with \gexec to have psql run the resulting SQL statement for you in one go.

user_64643080
Related