Leerzeichen werden ersetzt mit \"\" bei Einfuegen von Daten in Composite Type

Anwendungen & Erweiterungen SQL
1

Guten Abend,

ich habe eine kleine Übungsaufgabe mit PostgreSQL vor, indem ich in einer Tabelle einen Composite Type (also einen eigenen Typ) benutzen möchte.
Jedoch werden beim Befüllen der Daten innerhalb der Spalten vom Typ text/character varying jedesmal die Leerzeichen durch EscapeSequenzen ersetzt.
Ich hatte wirklich sehr vieles ausprobiert und komme nicht zur Lösung.

Der Aufbau:
CREATE TABLE public.person
(
id integer NOT NULL,
nachname character varying(255) COLLATE pg_catalog.“default” NOT NULL,
vorname character varying(255) COLLATE pg_catalog.“default” NOT NULL,
“Adresse” “Adresse”[],
CONSTRAINT person_pkey PRIMARY KEY (id)
)

CREATE TYPE public.“Adresse” AS
(
id_adresse integer,
plz integer,
wohnort text,
strasse text,
hausnr integer,
land text
);

–

Das Insert-Statement (dieser besagt, dass die folgende Person 2 gleichzeitige Adressen besitzt):
insert into person values (1,‘Grabowsky’,‘Karl Heinz’, ‘01.06.1960’, ‘M’, ARRAY[ROW(1,55801, ‘Bad Salzuflen’,‘Soester’ || ’ ’ || ‘Weg’,51,‘Deutschland’)::“Adresse”, row(2,15098,‘Emsbueren’,‘Ackerstraße’,54,‘Deutschland’)::“Adresse”] );

Resultat:
Jedoch werden alle Leerzeichen (bzw. Escape-Sequenzen) enthaltenen Strings ersetzt nach folgendem Schema:

\"Bad Salzuflen\"

Meine Ansätze:
Ich versuchte es mit Funktionen wie chr(32), concat, quote_literal, array_to_string etc.
Ich hatte es auch versucht mit encode(E’W '::bytea, ‘hex’) das Leerzeichen explizit über die Funktion encode herauszufinden, womit ich auf den Hexwert 20 kam bzw. 32 Dezimal. Es löste nicht das Problem.

Falls einer helfen kann oder möchte wäre dies schön.

Mein konkreter Sinn dieses Ansatzes ist einfach - ich möchte innerhalb einer Middleware NodeJS nicht ständig einen gesamten Datensatz abrufen nach dem Schema:
{
name: Grabowsky,
vorname: Karl,
Adresse 1 …
},
{
name: Grabowsky,
vorname: Karl,
Adresse 2 …
}

Es sollte alle Daten zu einer Person in einem Datensatz enthalten sein, also:

{
name: Grabowsky,
vorname: Karl,
{Adresse 1}
{Adresse 2}
}

Dies soll schon auf Datenbankebene geschehen, anstatt erst in der Middleware,
wo es auch ORM Ansätze gibt, die ein nested JSON Datensatz ermöglichen - die ich jedoch lieber auf Datenbankebene schon lösen möchte. Was evtl. auch performanter ist.

Vielen Dank um jede Hilfe!

2

Warum normalisierst Du das nicht in zwei Tabellen? Damit wirst Du viel weniger Probleme haben.
(Und warum hat der Typ Adresse nochmal eine Spalte “id_adresse”? Das scheint mir komplett überflüssig zu sein)

Jedoch werden alle Leerzeichen enthaltenen Strings ersetzt nach folgendem Schema

Nein, die Leerzeichen werden nicht “ersetzt”, das ist nur die Art und Weise wie so ein Typ angezeigt wird. Wenn Du auf die einzelne Felder des Typs zugreifst, dann siehst Du das:

select id, nachname, vorname, adresse[1].wohnort, adresse[1].strasse
from person;

Online Beispiel

3

Bitte verwende für Code passende Code-Tag, damit das Copy&Paste - fähig wird. Warum verwendest Du INTEGER für PLZ und Hausnummer?

4

Wenn Du die Generierung des JSON im Frontend vermeiden willst, würde ich eher zu einem vernünftig normalisierten Modell greifen, und mittels einer View die JSON Generierung in der Datenbank machen.

akretschmer hat Recht: PLZ und Hausnummer sollten Strings sein. Bei der Postleitzahl ist die führende Null wichtig, und Hausnummern können auch Buchstaben oder andere Zeichen enthalten, z.B “Bahnhofstr 1a” oder “Am Weg 1-7”

So was in der Art wie das:

CREATE TABLE person
(
  id integer primary key,
  nachname varchar(100),
  vorname varchar(100)
);

create table adresse 
(
  id integer primary key,
  person_id integer not null references person,
  plz varchar(5),
  wohnort text,
  strasse text,
  hausnr varchar(25),
  land text
);

insert into person (id, nachname, vorname)values 
(1, 'Grabowsky','Karl Heinz'),
(2, 'Lustig', 'Peter');

insert into adresse  
values
(1,1,'55801', 'Bad Salzuflen','Soester Weg','51','Deutschland'),
(2,1,'15098','Emsbueren','Ackerstraße','54b','Deutschland'),
(3,2,'12345','Im Wald','Bauwagen','1a','Deutschland');


create or replace view person_und_adressen
as
select p.id, 
          p.vorname, 
          p.nachname, 
          to_jsonb(p)||jsonb_build_object('adressen', (jsonb_agg(a) - 'person_id')) as person_json
from person p 
  join adresse a on a.person_id = p.id
group by p.id;

Online Beispiel

1 „Gefällt mir“
5

Erst einmal vielen Dank für die schnellen Antworten!

Die Funktion jsonb_build_object sowie jsonb_agg sehe ich zum Ersten Mal und bedanke mich für diesen Ansatz, direkt in ein json-Objekt umwandeln zu können.

Ich bedanke mich auch für den Hinweis, dass die durch \" ersetzten Daten im select anders dargestellt werden - dies wusste ich nicht, dass es beim Zugriff ohne diesen Zeichen dargestellt wird.

Und ja die id_adresse ist überflüssig - Sorry dafür. Außerdem stimmt es - eine Hausnummer kann auch StrasseXYZ 1a lauten - dies hatte ich nicht bedacht.

Mir ging es primär darum den alten Ansatz mit zwei Tabellen zu vermeiden und dafür eine “nested composite type column” zu verwenden, worin die Adresse(n) direkt an die Person sozusagen geheftet wird/werden, anstatt erst über einen Fremdschlüssel zu schauen zu welcher Person welche Adresse gehört.

Danke nochmals für die schnelle Hilfe!

6

Wenn Du erst anfängst mit relationalen Datenbanken zu arbeiten, würde ich ganz stark empfehlen, erstmal den “normalen” Weg beim Design zu gehen. Sprich: ein sauberes, normalisiertes Modell erstellen. Relationale Datenbank sind darauf optimiert Tabellen via JOIN zu verbinden. Das bildet den Kern des relationalen Modells. Wenn sie das nicht gut könnten, wären sie nicht so erfolgreich.

Eine de-normalisierung über Arrays ist fast immer eine Schlechte Wahl. Denk nur mal an Abfragen wie: finde alle Adresse im Postleitzahlen Gebiet 8x - jede Suche die über Attribute von Adressen geht wird wesentlich komplizierter durch die Arrays. Zudem kannst Du auch keine vernünftigen Foreign Keys verwenden, z.B. für die Stadt. Auch updates werden durch die Arrays wesentlich umständlicher und langsamer.

Wenn dann auch noch “doppelt” de-normalisiert wird, indem das mit einem composite type gekoppelt wird, geht die Wahrscheinlichkeit, dass es tatsächlich effizienter ist, gegen Null.

1 „Gefällt mir“