Export from Postgres as fixed width file

ziceman used Ask the Experts™
In order for my client's ERP system (xTuple) to work with their new banking system, the check registry needs to be exported as a fixed with file.  The fields sizes are preset and will have leading zeros (e.g., 000101, 00000243).

What is the best way to get the results of a query to look like this.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Select to_char(101,'000000'),to_char(243,'00000000');


Excellent.  So would the SQL look something like this?

SELECT to_char(check_num, '000000') as checknumber,
              to_char(acct_num,'000000000') as accoutnumber,
              to_char(rout_num,'00000000000') as routing number
FROM check_reg
Yes though take care how you alias the column names spelling and blanks ....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial