Postgres copy to TSV file with header

Question

I have a function like so -

CREATE 
OR REPLACE FUNCTION ind (bucket text) RETURNS table (
    middle character varying (100),
    last character varying (100)
) AS $body$ BEGIN return query 
select 
  fname as first, 
  lname as last 
from all_records
; END;
$body$ LANGUAGE PLPGSQL;

How do I output the results of select ind ('Mob') into a tsv file?

I want the output to look like this -

first   last
MARY    KATHERINE

Wickedness

Answer 1

You can use the COPY command example:

COPY (select * from ind('Mob')) TO '/tmp/ind.tsv'  CSV  HEADER DELIMITER E'\t';

the file '/tmp/ind.tsv' will contain you data

Sweetening

Answer 2

Postgres doesn't allow copy with header for tsv for some reason.

If you're using a linux based system you can do it with a script like this:

#create file with tab delimited column list (use \t between each column name)
echo -e "user_id\temail" > user_output.tsv

#now you can append the results of your query to that file by copying to STDOUT
psql -h your_host_name -d your_database_name -c "\copy (SELECT user_id, email FROM my_user_table) to STDOUT;" >> user_output.tsv

Alternatively, if your script is long and you don't want to pass it in with -c command you can use the same approach from a .sql file, use "--quiet" to avoid notices being passed into your file

psql --quiet -h your_host_name -d your_database_name -f your_sql_file.sql >> user_output.tsv

Catabolism

Answer 3

The following works as well, for psql;

psql -U "$POSTGRES_USER" -d "$POSTGRES_TARGET"  -c "\copy (SELECT * FROM table) 
TO STDOUT WITH (FORMAT CSV, HEADER, DELIMITER E'\t');" > output.tsv

Affliction