Обсуждение: Strange Result with char concatenation in query.

Поиск
Список
Период
Сортировка

Strange Result with char concatenation in query.

От
"mathieu.chappuis@msg-software.com"
Дата:
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

Re: Strange Result with char concatenation in query.

От
Tom Lane
Дата:
"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

Re: Strange Result with char concatenation in query.

От
Manfred Koizar
Дата:
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