Thread: copy

Hi all,

I have a strange behavior while copying from a text file, I don't know
if this will be considered a bug or a feature.

I have this text file:
------------------------
XXX|QWERTYUIOPASDFGHJKLA
A01|BAIO
A02|BAIO CHIARO
A03|BAIO OSCURO
------------------------

and this table:

Table    = mantelli
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| m_codice                         | char()                           |     3 |
| m_descr                          | char()                           |    20 |
+----------------------------------+----------------------------------+-------+
Index:    i5_mantelli

prova=> copy mantelli from '/tmp/mantelli.load' using delimiters '|';
COPY
prova=> select * from mantelli;
m_codice|m_descr
--------+--------------------
XXX     |QWERTYUIOPASDFGHJKLA
       |BAIO CHIARO       |BAIO OSCURO
(4 rows)

prova=> select m_descr,m_codice from mantelli ;
m_descr             |m_codice
--------------------+--------
QWERTYUIOPASDFGHJKLA|XXX                   |A01            |A02            |A03     
(4 rows)

prova=> select m_codice from mantelli ;
m_codice
--------
XXX     
A01     
A02     
A03     
(4 rows)

prova=> select m_descr from mantelli ;
m_descr             
--------------------
QWERTYUIOPASDFGHJKLA                     ARO       URO
(4 rows)

Seems that COPY expects that last field is 20 char long and if it is less than 20
it becomes crazy.

-Jose'-

Sferacarta Software <sferac@bo.nettuno.it> writes:
> I have a strange behavior while copying from a text file, I don't know
> if this will be considered a bug or a feature.
> Seems that COPY expects that last field is 20 char long and if it is
> less than 20 it becomes crazy.

I cannot replicate this bug with 6.4.  What version are you using?
        regards, tom lane

>
> Sferacarta Software <sferac@bo.nettuno.it> writes:
> > I have a strange behavior while copying from a text file, I don't know
> > if this will be considered a bug or a feature.
> > Seems that COPY expects that last field is 20 char long and if it is
> > less than 20 it becomes crazy.
>
> I cannot replicate this bug with 6.4.  What version are you using?

    I can, but it isn't a bug! At least not one in the backend.

    The  file must contain CRLF at the end of a line instead of a
    single LF.

    The data is absolutely intact in the database  including  the
    CR  for those lines, where the second fields value is shorter
    than 20 chars. It's just psql(1) who's output  get's  mangled
    up.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

jwieck@debis.com (Jan Wieck) writes:
>     I can, but it isn't a bug! At least not one in the backend.
>     The  file must contain CRLF at the end of a line instead of a
>     single LF.

Good eye!  I had noticed the odd formatting of Jose's output, but
didn't draw the right conclusion --- I thought it just got messed
up in the preparation of his mail message.

I guess the next question is whether we like this behavior or not.

I could see an argument for stripping CR if it's not quoted somehow...
but this ought to be considered in the context of the whole is-COPY-
8-bit-clean issue that keeps coming up.
        regards, tom lane