Dotaz: Postgresql - text - utf8 - \r \n \t ...

[Martin]

Dobrý den, může mi prosím někdo vysvětlit, jak vložit text (obsah souboru) v kódování utf8 do tabulky tak, aby postgres neprovedl konverzi tohoto textu ? Příklad:
html:

<!-- soubor v utf8, without bom -->
<input type="text"<-- tady je CRLF
    data-sometext="ěáíýžč">

sql:

create table test (html text);
insert into test values (pg_read_file('...'));
select * from test;

<input type="text"\r
    data-sometext="ěáíýžč">

Jde o tu značku \r. Postgres prostě převede chr(13) na string \r. Týká se to i ostatních escape sekvencí. Google nepomohl. Píše se tam o convert_from(), encode(), E'\r', ale to prostě není ono. Chtěl bych, aby tam byl vložen ten soubor tak, jak je na disku. Server i client jsou encoding utf8, database má encoding=utf8, collate='C', ctype='C'. Nějak to nechápu. \t \r \n jsou přece platné znaky v utf8. Díky.

Odpovědi

[🇨🇽]

vložen ten soubor tak, jak je na disku

hhhhmmmmmmmmmmmmmmmmmmm co ho jakoby přečíst binárně funkcí pg_read_binary_file hele a v db ho jako schovat v proměný typu bytea hele?????? :O ;D

[Martin]

Ano, to jsem také zkoušel. Bohužel, v okamžiku, kdy chci již naimportované bytea parsovat (regexp_replace), musím ho převést na text - a je to tam znovu. Vlastně každý převod z bytea na text končí s vloženými \r \t ...

[okbobcz]

Implicitní cast z bytea do textu escapuje bílé znaky. Pokud člověk se chce vyhnout této nechtěné konverzi, tak je potřeba použít vlastní funkci:

CREATE OR REPLACE FUNCTION public.bytea_to_text(bytea)
 RETURNS text
 LANGUAGE sql
AS $function$
SELECT convert_from($1, current_setting('server_encoding'))
$function$

[okbobcz]

K tomuhle by se pg_read_file vůbec nemělo používat. Tahle funkce je navržená pro adminy (server side) pro načítání konfiguráků. Už to, že tuto funkci může používat pouze super user signalizuje, že není navržená pro běžným uživatelem. Postgres je client-server databáze, nikoliv souborová databáze.

Pokud potřebujete naimportovat soubor, tak pokud je v řádech kilobajtů, tak nejjednodušší je jej načíst na straně klienta, oescapovat a vygenerovat insert. nebo použít prepared statement nebo parametrized statement a pak se nemusí nic escapovat, jelikož obsah jde separátním kanálem.

Inspiraci můžete čerpat z pgimportdoc

Další možnost - už přes soubory je načíst soubor do Postgresu jako large Object skrze lo_import. Pak už na serveru je zkonvertovat na text nebo bytea, funkcí lo_get - ukázka. Příklad je v článku na rootu "PostgreSQL 9.4: transakční SQL JSON databáze".

[Martin]

K tomu pg_read_file - je to na serverové straně, a jsou to malé soubory, které se načtou během vytváření databáze.

... Pak už na serveru je zkonvertovat na text nebo bytea,

A o tohle mi jde. Jako bytea je to ok, ale bytea nemohu převést na text -> zase tam vzniknou řetězce \r\n.
Př.: vezmu string "Hello" + chr(13)+chr(10) + "world"

select convert_from('\x48656c6c6f0d0a576f726c64','utf8');

console: Hello\r
world

copy (select convert_from('\x48656c6c6f0d0a576f726c64','utf8')) to 'testfile.txt';

Soubor obsahuje: Hello\r\nworld
Díky.

[okbobcz]

\r bude dost mozna jen o zobrazeni. Postgres \r interne chr(13) zobrazuje jako \r. Interne to ale nejsou dva znaky tj \ a r

postgres=# select 'Ahoj' || chr(13) || 'Svete';
┌─────────────┐
│  ?column?   │
╞═════════════╡
│ Ahoj\rSvete │
└─────────────┘
(1 row)

Kdezto

postgres=# select 'Ahoj' || chr(10) || 'Svete';
┌──────────┐
│ ?column? │
╞══════════╡
│ Ahoj    ↵│
│ Svete    │
└──────────┘
(1 row)

Jde o to, ze Postgres nezna vyznam \r (chr(13)) a tak jej zobrazi jako \r. Postgres je Unixova databaze, tam se \r pro zalomeni nepouziva.

Pokud se ho chcete zbavit, tak jej musite smazat.

[okbobcz]

V Unixu je konec řádku \n, v Dosu \r\n. Postgres escape sekvence, které neumí interpretovat, zobrazuje.

[okbobcz]

Cokoliv, co vyžaduje superusera, by se pro běžnou práci nemělo používat - pg_read_file. Samozřejmě, že může jít jen o Vaši soukromou aplikaci, ale jinak je to prasárna.