Postgres output to a tab delimmitted .txt file

postgresql

Solution

You can use COPY command like below:

COPY (SELECT * FROM TABLE_NAME) TO 'D:\Demo1.tsv'

more info COPY IN POSTGRESQL

EDIT FOR .txt File:

 COPY (SELECT * FROM t_offer_master) TO 'D:\Demo1.txt' WITH DELIMITER E'\t';

Edit for \n , \r replace

COPY (SELECT id,regexp_replace(name,E'\n|\r',' ','g') FROM table_name) TO 'D:\
    Demo1.txt' WITH DELIMITER E'\t';

Problem

I've a requirement where in i need to provide the client with data from a couple of postgres tables in a tab-delimmited file.I've used the following code to generate it in a .tsv file. But he's come back asking me to provide it in a tab delimmited csv file. Can you please suggest me as how this can be done apart from re-naming or moving it to .txt file. Regards, Vijay ``` psql -d vijay -U postgres -t -A -c "select a.key,a.name,cf.categories,cf.features,regexp_replace(a.iphone_description, E'[\\n\\r]+', ' ', 'g' ),a.sms_name,replace(a.sms_address,',',':'),a.sms_phone,a.sms_subscribe_string,a.iphone_name,replace(a.iphone_address,',',':'),a.iphone_city,a.iphone_state_key,a.iphone_zip,a.iphone_phone,a.iphone_logo_suffix,a.iphone_banner_suffix,a.iphone_url,a.start_date,a.end_date,a.disabled,a.is_deletable,a.cms_type,a.deleted,a.banner_enabled,a.latitude,a.longitude from .advertiser a LEFT OUTER JOIN (select cat.ad_key as key,fe.name,cat.categories,fe.features from .temp_cat cat FULL OUTER JOIN .temp_features fe on (cat.ad_key=fe.key))cf ON (a.key=cf.key) and a.cms_type='poi'" > poi_10072014.tsv regexp_replace(a.iphone_description, E'[\\n\\r]+', ' ', 'g' ) COPY (select a.key,a.name,cf.categories,cf.features,regexp_replace(a.iphone_description, E'\n|\r',' ', 'g' ),a.sms_name,replace(a.sms_address,',',':'),a.sms_phone,a.sms_subscribe_string,a.iphone_name,replace(a.iphone_address,',',':'),a.iphone_city,a.iphone_state_key,a.iphone_zip,a.iphone_phone,a.iphone_logo_suffix,a.iphone_banner_suffix,a.iphone_url,a.start_date,a.end_date,a.disabled,a.is_deletable,a.cms_type,a.deleted,a.banner_enabled,a.latitude,a.longitude from advertiser a LEFT OUTER JOIN (select cat.ad_key as key,fe.name,cat.categories,fe.features from temp_cat cat FULL OUTER JOIN temp_features fe on (cat.ad_key=fe.key))cf ON (a.key=cf.key) and a.cms_type='poi') TO 'poi_11072014.txt' WITH DELIMITER E'\t'; ERROR: syntax error at or near "(" at character 6 LINE 1: COPY (select a.key,a.name,cf.categories,cf.features,regexp_r... ```

Original source