Formát čísla v PostgreSQL

[Pavel...]

Programator tu pre mna vyrobil podivnost a neviem sa dopatrat k odpovedi, co to vidim .
Nema niekto tip?

- v PostgreSQL databaze je stlpec typu NUMERIC, bez dalsieho spresnenia poctu cifier i desatinnych miest
- aplikacia to plni v roznych kontextoch celymi cislami.

ked dam select na ten stlpec, tak mi pgAdmin (presnejsie pgAdmin 4, ver. 6.1) vrati niektore cisla v desatinnom tvare a niektore v celociselnom.
Vyzera to zhruba takto:

123.0
   123
124.0
   125
     ...

pre vsetky prakticke ucely ktore som skusil sa to vsetko tvari ako cele cislo.
Pracovne predpokladam, ze niekde na pozadi si PostgreSQL uklada nieco o presnosti a ze to nie je v poriadku.
Ked som pokusne stlpec zmenil na NUMERIC(10), javi sa mi, ze problem sa vyparil.
Zlahka som to konzultoval s ujo Googlom, ale to k nicomu neviedlo.

Tusi niekto o co ide?

[Reklama]

[Filip Jirsák]

https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

[Pavel...]

https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

a citali ste si to? lebo ja ano, este predtym, ako som napisal uvodny post

[Filip Jirsák]

Ano, já jsem to četl a je tam napsáno, co znamená typ NUMERIC (bez přesnosti a rozsahu) a jak s ním PostgreSQL zachází. Pokud jste to četl, proč tedy spekulujete o tom, jak s tím PostgreSQL zachází? Nebo máte pocit, že je ta dokumentace v rozporu s reálným chováním PostgreSQL? Z čeho jste ten pocit získal?

Co vidíte vám nikdo neřekne – vy to vidíte, my ne. Já bych začal tím, že se podíváte, co v té databázi doopravdy je uložené – třeba pomocí psql nebo pg_dumpem do SQL příkazů.

[Pavel...]

Ano, já jsem to četl a je tam napsáno, co znamená typ NUMERIC (bez přesnosti a rozsahu) a jak s ním PostgreSQL zachází. Pokud jste to četl, proč tedy spekulujete o tom, jak s tím PostgreSQL zachází? Nebo máte pocit, že je ta dokumentace v rozporu s reálným chováním PostgreSQL? Z čeho jste ten pocit získal?

ach jaj... ked nikde nepisem o tom, ze dokumentacia je v rozpore s chovanim, tak asi si to nemyslim.

Co vidíte vám nikdo neřekne – vy to vidíte, my ne. Já bych začal tím, že se podíváte, co v té databázi doopravdy je uložené – třeba pomocí psql nebo pg_dumpem do SQL příkazů.

plsq/pg_dump/pgAdmin sa chovaju konzistentne rovnako.

Zjavne Postgresql si ulozi k cislu typu NUMERIC infromaciu, kolko je tam desatinnych miest z cias vlozenia zaznamu.
Mozem tam vlozit 1.00 a budem mat pekny vystup pre uctovnicky . 

ok, aspon mi je jasne, preco to je zahodno v aplikacii opravit. A zjavne ak sa dostanem k zdrojovemu kodu, tak tam v nejakom kontexte najdem vkladane cele cislo v desatinnom tvare.

V Oracle som to nikdy nepozoroval, ale mozno len nikoho v mojom okoli taku blbost nenapadlo zrealizovat .

[Reklama]

[Filip Jirsák]

ach jaj... ked nikde nepisem o tom, ze dokumentacia je v rozpore s chovanim, tak asi si to nemyslim.

Spekuloval jste o tom, co PostgreSQL s typem NUMERIC dělá, a vaše spekulace byla v rozporu s dokumentací. Tak buď jste tu dokumentaci nečetl, nebo si myslíte, že je dokumentace chybná.

plsq/pg_dump/pgAdmin sa chovaju konzistentne rovnako.

Takže už víte, že máte data v databázi tak, jak je vidíte v pgAdmin. Tedy jednou s jedním desetinným místem, jednou bez desetinného místa. Takže je tam aplikace takhle ukládá (pokud není chyba v PostgreSQL, ale pravděpodobnější je, že je v té aplikaci).

Zjavne Postgresql si ulozi k cislu typu NUMERIC infromaciu, kolko je tam desatinnych miest z cias vlozenia zaznamu.

Proč o tom pořád spekulujete a nepřečtete si tu dokumentaci? V dokumentaci je jasně napsáno, že pokud je uveden typ NUMERIC (bez specifikace), ukládá se číslo přesně tak, jak ho tam klient pošle. Změnit se to může jedině pokud by bylo číslo mimo implementační limity, což ta vámi uváděná čísla určitě nejsou.

V Oracle som to nikdy nepozoroval, ale mozno len nikoho v mojom okoli taku blbost nenapadlo zrealizovat .

Oracle se pravděpodobně chová podle SQL standardu.

V té dokumentaci PostgreSQL je to opět napsáno – PostgreSQL se chová v tomto případě v rozporu se standardem, protože standard vyžaduje, že když není scale uvedeno, používá se hodnota 0, tedy se zaokrouhluje na celá čísla.

[Pavel...]

Proč o tom pořád spekulujete a nepřečtete si tu dokumentaci? V dokumentaci je jasně napsáno, že pokud je uveden typ NUMERIC (bez specifikace), ukládá se číslo přesně tak, jak ho tam klient pošle. Změnit se to může jedině pokud by bylo číslo mimo implementační limity, což ta vámi uváděná čísla určitě nejsou.

V té dokumentaci PostgreSQL je to opět napsáno – PostgreSQL se chová v tomto případě v rozporu se standardem, protože standard vyžaduje, že když není scale uvedeno, používá se hodnota 0, tedy se zaokrouhluje na celá čísla.

ehm... je tam napisane, ze nebude cislo upravovane na nejaky pocet cifier, nie, ze to cislo sa v nejakej forme buchne 1:1 ako string do databazy (cim dosiahnete, ze  1, 1.0 a 1.00 su z pohladu GUI rozne udaje v databaze),
Napokon explicitne v tej dokumentacii pisu "Numeric values are physically stored without any extra leading or trailing zeroes." Co keby presne platilo tak bez doplnujucich informacii by to znacilo, ze si DB nema ako vycucat z prsta tie zaverecne nuly.

Veta o SQL-standarde hovori detto nieco uplne ine ako to, ze sa bude databaza k cislu spravat ako k stringu. Len to hovori, ze policko bude definovane dynamickejsie.

Pekne to je vidno na tom, ze NUMERIC(4,2) vam v Oracle zobrazi 1.1 a Postgresql 1.10.

[Filip Jirsák]

ehm... je tam napisane, ze nebude cislo upravovane na nejaky pocet cifier, nie, ze to cislo sa v nejakej forme buchne 1:1 ako string do databazy (cim dosiahnete, ze  1, 1.0 a 1.00 su z pohladu GUI rozne udaje v databaze),

Já jsem ale nic o stringu nepsal. Ale z toho, že databáze nijak neupravuje počet cifer, plyne, že číslo musí uložit tak, jak ho od klienta dostane. Když dostane 1, uloží 1. Když dostane 1.0, uloží 1.0. Kdyby místo toho uložila 1, znamená to, že došlo k úpravě počtu cifer – a v dokumentaci je napsáno, že k tomu nedochází.

Napokon explicitne v tej dokumentacii pisu "Numeric values are physically stored without any extra leading or trailing zeroes." Co keby presne platilo tak bez doplnujucich informacii by to znacilo, ze si DB nema ako vycucat z prsta tie zaverecne nuly.

Ale tam nikde není napsáno, že si DB ty doplňující informace neukládá. A nějak si to uložit musí – ať už jako nuly nebo jako informaci o přesnosti, když zaručuje, že uloží číslo přesně tak, jak ho klient zadal, bez nějakých úprav počtu cifer nebo přesnosti.

Veta o SQL-standarde hovori detto nieco uplne ine ako to, ze sa bude databaza k cislu spravat ako k stringu. Len to hovori, ze policko bude definovane dynamickejsie.

Ještě jednou, já ani dokumentace netvrdí nic o stringu. To jste si vymyslel vy. Zkuste si méně vymýšlet a více věnovat pozornost tomu, co je v dokumentaci skutečně napsané.

Já jsem se na větu o SQL standardu odkazoval při vysvětlení, proč se PostgreSQL chová jinak, než Oracle.

Pekne to je vidno na tom, ze NUMERIC(4,2) vam v Oracle zobrazi 1.1 a Postgresql 1.10.

Na tom je vidět akorát to, že ta čísla jsou zobrazena s různou přesností. O tom, jak s těmi čísly databázový stroj zachází, to neříká vůbec nic. Stejně se ta čísla mohou zobrazit třeba i tehdy, kdyby je databázový stroj ukládal v plovoucí řádové čárce.

[Pavel Stěhule]

Pekne to je vidno na tom, ze NUMERIC(4,2) vam v Oracle zobrazi 1.1 a Postgresql 1.10.

Postgresový numeric je uložené číslo (hodně zjednodušeně posloupnost číslic) + počet desetiných míst. Ten počet desetiných míst může být vynucený definicí nebo pokud není definován, tak vychází ze vstupu a zachovává se. Na rozdíl od Oracle Postgres pokud není formát vynucený, tak Postgres uloží číslo tak, aby jeho zobrazení bylo stejné jako jeho vstup. 1.1 a 1.10 jsou tatáž čísla. Jen se liší počtem desetinných míst. Je potřeba si uvědomit, že v případě typu numeric - numeric bez specifikace neznamená numeric(max, 0). Je to prostě numeric bez specifikace počtu desetinných míst. Neznamená to celé číslo.

Pokud se potřebujete zbavit koncových null, tj redukovat počet desetinných míst bez změny hodnoty, použijte funkci trim_scale

(2023-02-02 19:05:42) postgres=# select trim_scale(10.20000::numeric), 10.20000::numeric;
┌────────────┬──────────┐
│ trim_scale │ numeric  │
╞════════════╪══════════╡
│       10.2 │ 10.20000 │
└────────────┴──────────┘
(1 row)

[Pavel...]

Postgresový numeric je uložené číslo (hodně zjednodušeně posloupnost číslic) + počet desetiných míst.
Pokud se potřebujete zbavit koncových null, tj redukovat počet desetinných míst bez změny hodnoty, použijte funkci trim_scale

Vdaka. Jednoducho ma to prekvapilo a citil som potrebu sa snazit pochopit, co to pozorujem.
Slovami dodavatela, ktoreho som dokopal k zmene typu stlpca som "riesil uplnu blbost" .

[Pavel Stěhule]

Postgresový numeric je uložené číslo (hodně zjednodušeně posloupnost číslic) + počet desetiných míst.
Pokud se potřebujete zbavit koncových null, tj redukovat počet desetinných míst bez změny hodnoty, použijte funkci trim_scale

Vdaka. Jednoducho ma to prekvapilo a citil som potrebu sa snazit pochopit, co to pozorujem.
Slovami dodavatela, ktoreho som dokopal k zmene typu stlpca som "riesil uplnu blbost" .

V tom nejste sám :-). Čím déle člověk dělá s jedním konkrétním softwarem, a pak přejde na něco jiného, tak skoro zákonitě naráží, na to, že ten druhý software se nechová tak jak by čekal. Postgres není 100% kompatibilní s Oraclem. Není to záměr, ale výsledek vývoje, v některých případech dost odlišné filozofii, v některých případech i odlišné architektuře a i jiným chybám v designu, které už nejde z důvodu zpětné kompatibility opravit.

Vlastnosti SQL, vlastnosti datových typů se v Postgresu definovaly hlavně koncem 90 let, tedy 20 let po Oracle, kdy už byly i jiné možnosti dané výrazně větším výkonem a větší RAMkou. Nikoho také v té době nenapadlo, že by někdo migroval z Oracle do Postgresu.