Strange Result with char concatenation in query.
Hello,
A short example, is IMHO more explicit :
% cat file.csv
1,100/100
2,100/100
3,200/200
Creation :
CREATE TABLE test(
numtst INT,
chartst VARCHAR(8)
);
\c - postgres
COPY test FROM '/home/ou/file.csv' USING DELIMITERS ',' WITH NULL AS '';
\c - ou
SELECT '+'||numtst||'+' AS "numtst", '*'||chartst||'*' AS "chartst" FROM test;
Result :
test_db=> SELECT '+'||numtst||'+' AS "numtst", '*'||chartst||'-' AS "chartst" FROM test;
numtst | chartst
--------+------------
-+2+ | *100/100
-+4+ | *100/100
-+5+ | *200/200
(3 rows)
As you see the last '-' in the second column is appended to the first column.
Any help would be kindy.
Mathieu,
PGP Public Key Fingerprint: D775 C0FC 039D 203E 3221 76F1 9BB4 43D2
"mathieu.chappuis@msg-software.com" <mathieu.chappuis@msg-software.com> writes:
test_db=> SELECT '+'||numtst||'+' AS "numtst", '*'||chartst||'-' AS "chartst" FROM test;
numtst | chartst
--------+------------
-+2+ | *100/100
-+4+ | *100/100
-+5+ | *200/200
(3 rows)
As you see the last '-' in the second column is appended to the first column.
I think you have carriage returns in the values of chartst, which would
not be surprising if the original data file had Windows-style newlines
in it. COPY expects Unix-style newlines.
regards, tom lane
On Fri, 24 May 2002 16:09:54 +0200,
"mathieu.chappuis@msg-software.com"
<mathieu.chappuis@msg-software.com> wrote:
Hello,
A short example, is IMHO more explicit :
% cat file.csv
1,100/100
2,100/100
3,200/200
I bet, your input lines end with <CR>/<LF> (carriage return/linefeed).
On COPY FROM the <CR> is stored as the last character of chartst.
test_db=> SELECT '+'||numtst||'+' AS "numtst", '*'||chartst||'-' AS "chartst" FROM test;
numtst | chartst
--------+------------
-+2+ | *100/100
Your terminal gets
+2+ | *100/100<CR>-
<CR> sends the cursor to the start of the line, so you see the - in
front of the rest. Just to illustrate this, try
SELECT '+'||numtst||'+' AS "numtst", '*'||chartst||'-abc-' AS
"chartst" FROM test;
and you will get
numtst | chartst
--------+------------
-abc- | *100/100
Servus
Manfred