Speichern von json, jsonb, hstore, xml -, enum -, IP-addr, etc schlägt mit "Spalte "x" ist vom Typ json, aber Ausdruck vom Typ character varying"

Beim Einsatz von PostgreSQL zum speichern von Daten in einem Feld ein string-wie validiert Art, wie xml, json, jsonb, xml, ltree, etc, die INSERT oder UPDATE schlägt mit einer Fehlermeldung wie:

column "the_col" is of type json but expression is of type character varying

... oder

column "the_col" is of type json but expression is of type text

Warum? Was kann ich dagegen tun?

Ich bin mit JDBC (PgJDBC).

Dies geschieht durch Hibernate, JPA, und alle möglichen anderen Abstraktionsebenen.

Dem "standard" - Beratung durch das PostgreSQL-team, ist die Verwendung einer CAST im SQL. Dies ist nicht sinnvoll für Menschen, die mit query-Generatoren oder ORMs, vor allem, wenn die Systeme nicht über explizite Unterstützung für Datenbank-Typen wie json, so sind Sie abgebildet über String in der Anwendung.

Einige ORMs ermöglichen die Implementierung benutzerdefinierter Typ-Handler, aber ich möchte nicht wirklich zum schreiben eines benutzerdefinierten Prozedur für jeden Datentyp, für jede ORM, z.B. json auf Hibernate, json auf EclipseLink, json auf OpenJPA, xml Hibernate, ... etc. Es gibt keine JPA2 SPI für das schreiben einer generischen benutzerdefinierten Typ-handler. Ich bin auf der Suche nach einer Allgemeinen Lösung.

InformationsquelleAutor Craig Ringer | 2015-08-27
  1. 31

    , Warum es passiert

    Das problem ist, dass PostgreSQL ist übermäßig strengen über-casts zwischen text und nicht-text-Daten-Typen. Es wird nicht zulassen, dass ein impliziter cast (ohne CAST oder :: im SQL) aus einer text-Typ, wie text oder varchar (character varying) zu einem text-wie nicht-text-Typ, wie json, xml usw.

    Die PgJDBC Treiber gibt den Datentyp von varchar beim Aufruf setString, um einen parameter zuweisen. Wenn die Datenbank den Typ der Spalte, Funktion, argument, etc., ist nicht wirklich varchar oder text, sondern eine andere Art, Sie bekommen einen Typ-Fehler. Dies gilt auch für sehr viele andere Fahrer und ORMs.

    PgJDBC: stringtype=unspecified

    Die beste option, wenn Sie PgJDBC ist in der Regel zu übergeben Sie den parameter stringtype=unspecified. Dies überschreibt das default-Verhalten der Weitergabe setString Werte als varchar und stattdessen überlässt es die Datenbank zu "erraten", die Ihrem Datentyp. In fast allen Fällen ist dies jedoch genau das, was Sie wollen, übergeben Sie die Zeichenfolge, die input validator für den Typ, den Sie speichern wollen.

    Alle: CREATE CAST ... WITH FUNCTION ...

    Können Sie stattdessen CREATE CAST zum definieren einer data-type specific cast um dies zu ermöglichen, auf einer Art-für-Art-basis, aber diese können Nebenwirkungen haben anderswo. Wenn Sie dies tun, tun nicht verwenden WITHOUT FUNCTION wirft, werden Sie bypass Typ-Validierung und-Fehlern führen. Sie müssen die Eingabe - /Validierungs-Funktion für den Datentyp. Mit CREATE CAST ist geeignet für Benutzer, die von anderen Datenbank-Treiber, die haben keine Möglichkeit, den Treiber zu stoppen, indem die Art für einen string/text-Parameter.

    z.B.

    CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$
SELECT json_in($1::cstring); 
$$ LANGUAGE SQL IMMUTABLE;

CREATE CAST (text AS json) 
WITH FUNCTION json_intext(text) AS IMPLICIT;

    Alle: Benutzerdefinierter Typ hf

    Wenn Ihr ORM erlaubt, können Sie implementieren eine benutzerdefinierte type-handler für den Datentyp, und dass spezifische ORM. Dies vor allem dann nützlich, wenn Sie mit nativer Java-Typ, der Karten sowie der PostgreSQL-Typ, eher als die Verwendung String, aber es kann auch funktionieren, wenn Ihr ORM können Sie angeben, Typ-Handler mithilfe von Anmerkungen etc.

    Methoden für die Implementierung von benutzerdefinierten type-Handler sind Fahrer-, Sprach - und ORM-spezifischen. Hier ein Beispiel für Java-und Hibernate für json.

    PgJDBC: type handler mit PGObject

    Wenn Sie über eine native Java-Typ in Java, können Sie erweitern PGObject, um eine PgJDBC-Typ-Zuordnung für Ihren Typ. Sie werden wahrscheinlich auch brauchen, um die Durchführung einer ORM-spezifischen Typ-handler zu verwenden, Ihre PGObject, da die meisten ORMs wird einfach anrufen toString auf Typen, die Sie nicht erkennen. Dies ist die bevorzugte Methode zum zuordnen von komplexen Datentypen zwischen Java und PostgreSQL, aber auch die komplexeste.

    PgJDBC: Type handler mit setObject(int, Object)

    Wenn Sie String zu halten, wird der Wert in Java, als eine bestimmte Art, die Sie aufrufen können Sie die JDBC-Methode setObject(integer, Object) zum speichern der Zeichenfolge mit keine bestimmten Datentyp angegeben. Der JDBC-Treiber sendet den string-Repräsentation, und die Datenbank ableiten, dass der Typ aus der destination-Spalte Typ-oder-Funktion-argument-Typ.

    Siehe auch

    Fragen:

    Externe:

    • Andere / bessere Antworten begrüßt.
    • Sehr geschätzt wird. FWIW, bei der Prüfung es stringtype=unspecified für mich gearbeitet, setObject(int, Object) auf der anderen Seite nicht (zumindest ohne stringtype=unspecified aktiv).
    • wie man das mit spring boot?
    • Poste bitte eine neue Frage
    • tolle Antwort, aber ich möchte hinzufügen: nur mit setObject(int, Object) ist nicht genug mit PgJDBC in einigen Fällen - es ist immer noch schlau genug, um herauszufinden, dass der übergebene Wert ist in der Tat ein String, so liefert oid = 1043 als parameter-Typ (der für mich zu einem Fehler führt auf Postgre Seite wie ERROR: column type is 'jsonb' and passed value is of type 'character varying'. Ich musste setObject(int, Object, java.sql.Types.OTHER) Verwirklichung der notwendigen Wirkung (parameter mit oid = 0, die im wesentlichen teilt dem server mit "erraten Sie selbst, welcher Typ es ist")
    InformationsquelleAutor Craig Ringer
Schreibe einen Kommentar