Postgresql 9.3 - Exporting output of a .sql file to a pipe delimited text file

Question

I have a query in a(.sql) file "Provider.sql" . The query is ,

select 
P.ID AS P_ID,
P.LASTNAME AS L_N,
P.FIRST_NAME AS F_N,
FROM 
PROVIDER P

How do i export the output of this "Provider.sql" file to a '|' delimited text file?

P_ID|L_N|F_N 1|FRASER|SCOTT

I can export the output to a file manually from "PGADMIN III".

However I would like to automate this using PSQL.

What will be best approach to send the output of this .sql file to a text file with pipe delimiters?

Can you help me with an example?

Why won't it work with larger queries? I use \copy with hugely complex queries sometimes. You can always use a view, too. As for the output being "mangled" ... well, what output did you expect, and how was it different to what you got? Show some examples please. (Edit the question to add them, then comment here). — Craig Ringer
– Craig Ringer, Commented Aug 12, 2014 at 14:31

Craig Ringer · Accepted Answer · 2014-08-12 14:33:28Z

2

psql can do what you want natively - just run it in quiet, non-aligned, tuples-only mode:

$ psql -qAt -F '|' -c 'SELECT x, x FROM generate_series(1,10) x;'
1|1
2|2
3|3
4|4
5|5
6|6
7|7
8|8
9|9
10|10

If you want to change the record separator from newline to something else, use -R.

To write to a file, redirect stdout, e.g.

psql -qAt -F '|' -c 'SELECT x, x FROM generate_series(1,10) x;' > output.csv

or use -o:

psql -qAt -F '|' -o output.csv -c 'SELECT x, x FROM generate_series(1,10) x;'

However, in general \copy is more appropriate.

answered Aug 12, 2014 at 14:33

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1