Int als IPv4 Adresse anzeigen

Ich habe eine Tabelle mit IP-Adressen in einer int Spalte. Nun möchte ich diese in der Form n.n.n.n darstellen. Für positive Werte geht folgendes:

‘0.0.0.0’::inet + ipaddress

wenn ich aber diese Funktion auf negative Werte anwende, dann bekomme ich einen “result is out of range” Fehler.

Was muss ich ändern?

Christian

Das scheitert wie Du schon gemerkt hast am Vorzeichen, also am Datentyp.
Die Operation ergibt bei großen Werten einen Überlauf.
Gäbe es ein unsigned int in Postgres bzw. die zugehörigen Cast Funktionen dafür oder für inet und int(negativ), dann wäre das kein Problem.
So musst Du wahrscheinlich bitshift operationen nehmen und oder mit bytea arbeiten und “zu Fuß” in String umwandeln, byte für byte (also auf den Typ inet verzichten)

Es müsste doch klappen wenn Du den negativen integer Wert in den korrespondierenden positiven bigint Wert umwandelst (Zweierkompliment).

Sowas in der Art:

create or replace function int_to_inet(p_input integer)
  returns inet
as
$$
select case 
          when p_input < 0 then p_input::bigint + 2147483647 + 1 
          else p_input 
        end + '0.0.0.0'::inet
$$
language sql
immutable;

Ist schon eine Weile her das ich auf “bit-Ebene” was machen musste, und mit dem IP Kram bin ich auch nicht so fit - kann also durchaus falsch sein (aber ich denke die Idee an sich könnte klappen)

Das ist eine gute Idee, aber ich glaube sie scheitert an den vorhandenen Operatoren für den Typ INET. Für bigint ist da nichts. Unsigned Typen gibt es nicht.
Es ist wirklich schade, dass hier mit dem Fehlen von unsigned Typen so eine häßliche Lücke bei Postgres existiert.
M.E. kommt man nicht darum herum, die Werte mit Bitshifting oder ByteA Operationen einzeln zu extrahieren und zu einem IP String zu konvertieren.
Der bestehende INET Typ funktioniert offenbar nur so schön, wenn man ihn von Anfang an verwendet. Die Konvertierung ist häßlich und die (uralten) Beispiele mit dem Trick ```
‘0.0.0.0’::inet + xy funktionieren nur für xy Werte > -1.

Wenn die IP Daten also in dieser Form in der DB vorliegen, stammt der Wert vermutlich aus einer Berechnung im Client oder einem Import und würde also dort am besten auch weiter behandelt / konvertiert oder wie gesagt Bitshifting oder Bytea Casting.

Naja, Oracle, SQL Server und DB2 haben auch keine unsigned Typen und man kommt gut klar.

Deswegen ja die Idee den negativen integer Wert (der ja letztendlich das Zweierkompliment des unsigned Wertes darstellt) in das äquivalente (signed) bigint zu konvertieren.

Der Trick mit '0.0.0.0'::inet + <zahl> funktioniert eh nur mit bigint

Wenn ich das richtig sehe, funktioniert die Funktion von oben “eigentlich” ganz gut. Nur kommt die IP Adresse “rückwärts” raus. -1277278613 liefert 51.222.70.107, obwohl es eigentlich 107.70.222.51 sein müsste. Aber das kriegt man sicherlich auch noch in den Griff

Ah, so scheint’s zu gehen:

create or replace function int_to_inet(p_input integer)
  returns inet
as
$$
    select '0.0.0.0'::inet + 
            case 
              when p_input < 0 then (p_input::bigint & 4294967295::bigint)
              else p_input::bigint 
            end;
$$
language sql
immutable;
2 „Gefällt mir“

Danke, bei mir hat diese Funktion so geklappt.

Christian

Ja, man kommt “gut klar”, wenn man gar nicht erst die Vorteile nutzen kann, die es gäbe, wenn man ein starkes Typsystem mit starken typsensitiven Operatoren hätte.
Postgres hat das, leider fehlt der unsigned Basistyp.
Und IF THEN ELSE Gehampel für etwas, das bei der Existenz eines simpleren Typs gar nicht notwendig wäre, ist ein workaround, aber nicht optimal.

Man kann die Werte doch als bigint speichern, dann hat man auch kein Problem.

Ja, das ist auch keine Klage. Postgres ist so mächtig, dass es einfach Spaß macht.
Ich habe nur gesagt, dass es schade ist. Meinetwegen eine kosmetische Frage. (Was aber schon unter dem Aspekt der Ressourcenfrage eine Schmeichelei ist). Und dieser Punkt wird genau am Beispiel IPV4 sehr schön deutlich. Das Teil trägt seine “Fähigkeiten” ebenso wie IPV6 sogar im Namen. 4 Byte oder 6 Byte. Hat man einem Informatikschüler eine Handvoll Grundlagen beigebracht und was eine IP Adresse ist und wie sie funktioniert, was würde er versuchen, wenn eine solche IP Adresse in einer Tabelle zu speichern ist?

v4 und v6 beziehen sich auf die Version der entsprechenden Spezifikation. IPv6 verwendet 128bit - also 16 bytes.

Die Reduzierung des Speicherbedarfs ist ja nur ein Aspekt bei so einer Überlegung

Ein weiterer wichtiger Aspekt ist, einen Datentyp zu wählen, der den gewünschten Wertebereich ohne Verrenkungen abdeckt - und das wäre halt ein bigint. Bzw.: Postgres würde man wohl gleich inet oder cidr verwenden.

int statt bigint zu verwenden garantiert aber auch nicht, dass die Tabelle dadurch wirklich kleiner wird. Die Spaltenwerte eines Datensatzes werden auf 64 bit Grenzen ausgerichtet werden (“padding”). Genauer gesagt auf den Wert von MAXALIGN - was in 64bit Systemen typischerweise 64bit ist.

Das gilt nicht nur für Postgres. Soweit ich weiß passiert das bei allen Datenbanksystemen (auch wenn die Implementierungsdetails vermutlich stark abweichen)

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.