csv文件'-‘的psql变为'-0’

Question

我想将psql查询的结果输出到csv文件。我使用了以下方法

\o test.csv
SELECT myo_date, myo_maps_study, cbp_lvef, cbp_rvef, myx_ecg_posneg, myx_st, std_drugs, std_reason_comment FROM myo INNER JOIN studies ON (myo_std_uid = std_uid) LEFT OUTER JOIN cbp on (std_uid = cbp_std_uid) LEFT OUTER JOIN myx on (std_uid = myx_std_uid) WHERE myo_maps_study ~ 'MYO[0-9]*\$' AND std_reason_comment ~ 'AF' AND cbp_lvef is not null AND myx_st IS NOT NULL AND std_drugs IS NOT NULL ORDER by myo_date DESC LIMIT 500;
\q

查询本身的结果如下

06/11/2013 | MYO134537      | 36.75000 | 29.00000 | -              |    0.0 | ASPIRIN;BISOPROLOL;LISINOPRIL;METFORMIN;PPI;STATIN;FLUOXETINE;AMLODIPINE;GTN     | CPOE;AF;T2DM;POSET
 31/10/2013 | MYO130555      | 45.00000 | 36.25000 | -              |    0.0 | DILTIAZEM;STATIN;LISINOPRIL;ASPIRIN;FRUSEMIDE;SALBUTAMOL;PARACETAMOL;AMOXICILLIN | TROP-VE; CP; AF; CTPA-VE; ANT T; INV; RF
 23/10/2013 | MYO130538      | 18.75000 | 18.50000 | +              |   -1.0 | ASPIRIN;BISOPROLOL;RAMIPRIL                                                      | AF;MR;QLVFN;FAILED CARDIOVERSION
 18/10/2013 | MYO134510      | 39.50000 | 32.25000 | -              |    0.0 | ASPIRIN;STATIN;CO-CODAMOL;BISOPROLOL;GTN;PPI                                     | PVD;AF
 18/10/2013 | MYO130537      | 19.00000 | 18.00000 | -              |    0.0 | STATIN;RAMIPRIL;AMLODIPINE;WARFARIN;(METOPROLOL-STOPPED FOR TEST)                | TIA;AF;RF+++;ETINAP

但是，csv文件(在open office中打开)如下所示

06/11/2013  MYO134537   36.75   29  -0  0   ASPIRIN;BISOPROLOL;LISINOPRIL;METFORMIN;PPI;STATIN;FLUOXETINE;AMLODIPINE;GTN    CPOE;AF;T2DM;POSET
31/10/2013  MYO130555   45  36.25   -0  0   DILTIAZEM;STATIN;LISINOPRIL;ASPIRIN;FRUSEMIDE;SALBUTAMOL;PARACETAMOL;AMOXICILLIN    TROP-VE; CP; AF; CTPA-VE; ANT T; INV; RF
23/10/2013  MYO130538   18.75   18.5    0   -1  ASPIRIN;BISOPROLOL;RAMIPRIL AF;MR;QLVFN;FAILED CARDIOVERSION
18/10/2013  MYO134510   39.5    32.25   -0  0   ASPIRIN;STATIN;CO-CODAMOL;BISOPROLOL;GTN;PPI    PVD;AF
18/10/2013  MYO130537   19  18  -0  0   STATIN;RAMIPRIL;AMLODIPINE;WARFARIN;(METOPROLOL-STOPPED FOR TEST)   TIA;AF;RF+++;ETINAP

'-‘符号变成了-0，'+’变成了0。为了清楚起见，我想将它们分别更改为N和P。

做一个more test.csv可以给你

06/11/2013,MYO134537,36.75,29,-0,0,ASPIRIN;BISOPROLOL;LISINOPRIL;METFORMIN;PPI;STATIN;FLUOXETINE;AMLODIPINE;GTN,CPOE;AF;T2DM;POSET,,
31/10/2013,MYO130555,45,36.25,-0,0,DILTIAZEM;STATIN;LISINOPRIL;ASPIRIN;FRUSEMIDE;SALBUTAMOL;PARACETAMOL;AMOXICILLIN,TROP-VE; CP; AF; CTPA-VE; ANT T; INV; RF,,
23/10/2013,MYO130538,18.75,18.5,0,-1,ASPIRIN;BISOPROLOL;RAMIPRIL,AF;MR;QLVFN;FAILED CARDIOVERSION,,
18/10/2013,MYO134510,39.5,32.25,-0,0,ASPIRIN;STATIN;CO-CODAMOL;BISOPROLOL;GTN;PPI,PVD;AF,,
18/10/2013,MYO130537,19,18,-0,0,STATIN;RAMIPRIL;AMLODIPINE;WARFARIN;(METOPROLOL-STOPPED FOR TEST),TIA;AF;RF+++;ETINAP,,

但是，当我在open office中选择单元格时，-0或0单元格的内容始终为0。这不允许我进行搜索和替换。我不想手动更改这些设置。

我是否可以使用psql命令强制使用+和-，或者是否可以使用其他linux工具将-0更改为N，将0更改为P。我使用的是RHEL6。

Answer 1

尝试使用decode function代替字段名。

decode(myx_ecg_posneg,'-','N','+','P')

更新:对不起，这是pl/sql。尝试case表达式：

CASE myx_ecg_posneg
WHEN '-' THEN 'N'
WHEN '+' THEN 'P'
END