Subject: Re: Invalid byte sequence
To: pgsql-admin@lists.postgresql.org
References: 
 <CALhHtcC1zP2ADshm_Qo=RHHsTyqhR0jErAydPBZf2-ZbUrFLWg@mail.gmail.com>
From: Holger Jakobs <holger@jakobs.com>
Message-ID: <c30c40b2-721f-7fb9-7973-4a65f89d8e4b@jakobs.com>
Date: Thu, 1 Jul 2021 09:40:19 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: 
 <CALhHtcC1zP2ADshm_Qo=RHHsTyqhR0jErAydPBZf2-ZbUrFLWg@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha256;
 protocol="application/pgp-signature";
 boundary="2bxOQBlcemz7W754T8EMEVGXlW0kx2XK9"
Archived-At: 
 <https://www.postgresql.org/message-id/c30c40b2-721f-7fb9-7973-4a65f89d8e4b%40jakobs.com>
Precedence: bulk

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--2bxOQBlcemz7W754T8EMEVGXlW0kx2XK9
Content-Type: multipart/mixed; boundary="01pUahy1GHeViqukaHnLcaUFBpqnoKNGy";
 protected-headers="v1"
From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Message-ID: <c30c40b2-721f-7fb9-7973-4a65f89d8e4b@jakobs.com>
Subject: Re: Invalid byte sequence
References: <CALhHtcC1zP2ADshm_Qo=RHHsTyqhR0jErAydPBZf2-ZbUrFLWg@mail.gmail.com>
In-Reply-To: <CALhHtcC1zP2ADshm_Qo=RHHsTyqhR0jErAydPBZf2-ZbUrFLWg@mail.gmail.com>

--01pUahy1GHeViqukaHnLcaUFBpqnoKNGy
Content-Type: multipart/alternative;
 boundary="------------2342E20D4BC2167B75F659B7"
Content-Language: de-DE

This is a multi-part message in MIME format.
--------------2342E20D4BC2167B75F659B7
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable

How many lines has the file? Maybe line 377617 is the last one.

If so, you can just remove the last line containing this 0x00. The=20
following command can do this for you:

|sed -i '$ d' |/outputfile.csv

May I remark that having a file in the / directory is a bit awkward?=20
This directory should not be writable by any user (except root, and root =

doesn't fiddle with files for a database).


Am 01.07.21 um 09:29 schrieb Yambu:
> Hello
>
> How do i get past the error below.=C2=A0I have created a foreign table =

> which points to a csv file and when i try to create another table it=20
> gives error below
>
> The csv file has funny characters, how do i bypass those characters.
>
> create foreign table table1
> (
> id int,
> name text
> )
> SERVER local_file
> OPTIONS (filename '/outputfile.csv' ,
> format 'csv',
> header 'TRUE',
> delimiter ';'
> );
>
>
> create table2 as select * from=C2=A0table1;
>
> ERROR: =C2=A0invalid byte sequence for encoding "UTF8": 0x00
> CONTEXT: =C2=A0COPY table1, line 377617

--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012


--------------2342E20D4BC2167B75F659B7
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <p>How many lines has the file? Maybe line 377617 is the last one.</p=
>
    <p>If so, you can just remove the last line containing this 0x00.
      The following command can do this for you:<br>
    </p>
    <pre class=3D"lang-sh s-code-block"><code class=3D"hljs language-bash=
">sed -i <span class=3D"hljs-string">'$ d'</span> </code>/outputfile.csv<=
/pre>
    <p>May I remark that having a file in the / directory is a bit
      awkward? This directory should not be writable by any user (except
      root, and root doesn't fiddle with files for a database).</p>
    <p><br>
    </p>
    <p><br>
    </p>
    <div class=3D"moz-cite-prefix">Am 01.07.21 um 09:29 schrieb Yambu:<br=
>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:CALhHtcC1zP2ADshm_Qo=3DRHHsTyqhR0jErAydPBZf2-ZbUrFLWg@mail.gm=
ail.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DU=
TF-8">
      <div dir=3D"ltr">Hello<br>
        <div><br>
        </div>
        <div>How do i get past the error below.=C2=A0I have created a for=
eign
          table which points to a csv file and when i try to create
          another table it gives error below</div>
        <div><br>
        </div>
        <div>The csv file has funny characters, how do i bypass those
          characters.</div>
        <div><br>
        </div>
        <div>create foreign table table1</div>
        <div>(</div>
        <div>id int,</div>
        <div>name text</div>
        <div>)<br>
          SERVER local_file<br>
          OPTIONS (filename '/outputfile.csv' ,<br>
          format 'csv',<br>
          header 'TRUE',<br>
          delimiter ';' <br>
          );<br>
        </div>
        <div><br>
        </div>
        <div><br>
        </div>
        <div>create table2 as select * from=C2=A0table1;</div>
        <div><br>
        </div>
        <div>ERROR: =C2=A0invalid byte sequence for encoding "UTF8": 0x00=
<br>
          CONTEXT: =C2=A0COPY table1, line 377617<br>
        </div>
      </div>
    </blockquote>
    <pre class=3D"moz-signature" cols=3D"72">--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012</pre>
  </body>
</html>

--------------2342E20D4BC2167B75F659B7--

--01pUahy1GHeViqukaHnLcaUFBpqnoKNGy--

--2bxOQBlcemz7W754T8EMEVGXlW0kx2XK9
Content-Type: application/pgp-signature; name="OpenPGP_signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="OpenPGP_signature"

-----BEGIN PGP SIGNATURE-----

wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYN1xYwUDAAAAAAAKCRCKV5yTsxlhusiX
AKCyoEnK1iGptVQJOsO6IoqKx7bjTQCfd8vyedhfbcoaUEN4v19iLBz2xHk=
=S93a
-----END PGP SIGNATURE-----

--2bxOQBlcemz7W754T8EMEVGXlW0kx2XK9--