Hilfe bei PostgreSQL Query Abfrage

[HansGraefe]

Hallo,

ich brauche verschiedene Daten aus einer PostgreSQL Datenbank (Version 13+225+deb11u1) damit ich sie per bash weiterverarbeiten kann. Ich bin soweit gekommen, dass mir:

psql -d lcportal -c 'SELECT "NAME","DEVICE_IP" FROM "dc_vpn_clients"';

Die Spalten NAME und DEVICE_IP korrekt anzeigt. Jetzt brauche ich aber noch Daten aus einer anderen Tabelle, Abfrage wäre:

psql -d lcportal -c 'SELECT "ADDR" FROM "dc_vpn_addr"'

Aber da die Datensätze/Zeilen zusammengehören, vermutlich über die Spalte "id" "verbunden", wie bekomme ich beide Abfragen "zusammen", damit ich eine Liste beider Tabellen mit den korrekt zugeordneten Zeilen bekomme?

zB. so:

id | NAME | DEVICE_IP | ADDR |

Ich habe probiert:

psql -d lcportal -c 'SELECT "id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr"';

aber da bekomme ich Kauderwelsch zurück.

Habe UNION gefunden, aber:

psql -d lcportal -c 'SELECT "NAME","DEVICE_IP" FROM "dc_vpn_clients"' UNION 'SELECT "ADDR" FROM "dc_vpn_addr"';

Bringt einen Fehler:

Code: Alles auswählen

root@server ~ # psql -d lcportal -c 'SELECT "NAME","DEVICE_IP" FROM "dc_vpn_clients"' UNION 'SELECT "ADDR" FROM "dc_vpn_addr"';
psql: warning: extra command-line argument "SELECT "ADDR" FROM "dc_vpn_addr"" ignored
psql: error: FATAL:  Peer authentication failed for user "UNION"
root@yados-vpn ~ # psql -d lcportal -c 'SELECT "NAME","DEVICE_IP" FROM "dc_vpn_clients" UNION SELECT "ADDR" FROM "dc_vpn_addr"';
ERROR:  each UNION query must have the same number of columns
LINE 1: ...E","DEVICE_IP" FROM "dc_vpn_clients" UNION SELECT "ADDR" FRO...
                                                             ^
root@server ~ #

[bluestar]

psql -d lcportal -c 'SELECT "id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr"';

Entweder via JOIN oder via WHERE die Verküpfung beider Tabellen herstellen.... Du kannst mal mit

Code: Alles auswählen

\d dc_vpn_clients

und

Code: Alles auswählen

\d dc_vpn_addr

die in psql die Tabellen- und Fremdschlüssel anzeigen lassen, damit wird's möglich dir zu erklären, wie die Tabellen korrekt miteinander abfragen kannst.

[HansGraefe]

Die beiden Befehle ergeben:

Code: Alles auswählen

lcportal=# \d dc_vpn_clients
[...]
Indexes:
    "dc_vpn_clients_pkey" PRIMARY KEY, btree (id)

lcportal=# \d dc_vpn_addr
[...]
Indexes:
    "dc_vpn_addr_pkey" PRIMARY KEY, btree (id)

lcportal=#

Also doch über "id"

[bluestar]

Also doch über "id"

Code: Alles auswählen

psql -d lcportal -c 'SELECT "id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr"' WHERE "dc_vpn_clients"."id" = "dc_vpn_addr"."id";

[HansGraefe]

Code: Alles auswählen

psql -d lcportal -c 'SELECT "id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr"' WHERE "dc_vpn_clients"."id" = "dc_vpn_addr"."id";

Da kommt:

Code: Alles auswählen

root@server ~ # psql -d lcportal -c 'SELECT "id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr"' WHERE "dc_vpn_clients"."id" = "dc_vpn_addr"."id";
psql: warning: extra command-line argument "dc_vpn_clients.id" ignored
psql: warning: extra command-line argument "=" ignored
psql: warning: extra command-line argument "dc_vpn_addr.id" ignored
psql: error: FATAL:  Peer authentication failed for user "WHERE"

root@server ~ # psql -d lcportal -c 'SELECT "id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr" WHERE "dc_vpn_clients"."id" = "dc_vpn_addr"."id"';
ERROR:  column reference "id" is ambiguous
LINE 1: SELECT "id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients",...
               ^
root@server ~ #

[bluestar]

root@server ~ # psql -d lcportal -c 'SELECT "id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr" WHERE "dc_vpn_clients"."id" = "dc_vpn_addr"."id"';
ERROR: column reference "id" is ambiguous
LINE 1: SELECT "id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients",...
^
root@server ~ #
[/code]

Du musst bei Id schon dazuschreiben, aus welcher Tabelle die id ausgegeben werden soll:

Code: Alles auswählen

psql -d lcportal -c 'SELECT "dc_vpn_clients"."id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr" WHERE "dc_vpn_clients"."id" = "dc_vpn_addr"."id"';

[HansGraefe]

psql -d lcportal -c 'SELECT "dc_vpn_clients"."id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr" WHERE "dc_vpn_clients"."id" = "dc_vpn_addr"."id"';

Das funktioniert! Darf ich noch fragen, weshalb du die . angiebst? Das habe ich so noch nie gesehen, bzw. was ist der Unterschied zum ,?

[Meillo]

psql -d lcportal -c 'SELECT "dc_vpn_clients"."id","NAME","DEVICE_IP","ADDR" FROM "dc_vpn_clients","dc_vpn_addr" WHERE "dc_vpn_clients"."id" = "dc_vpn_addr"."id"';

Das funktioniert! Darf ich noch fragen, weshalb du die . angiebst? Das habe ich so noch nie gesehen, bzw. was ist der Unterschied zum ,?

Komma nutzt man als Trennzeichen, um mehrere Spalten anzufuehren.

Punkt nutzt man, um klarzustellen, zu welche Tabelle eine Spalte gehoert, d.h. in dieser Form: Tabellenname.Spaltenname (Statt dem Tabellenname kann man auch einen Alias definieren, den man dann stattdessen nutzt.)


Btw: Wenn Spalten- und Tabellennamen keine Sonderzeichen enthalten oder gleich wie Schluesselworte lauten, muss man sie nicht quoten. Ohne die ganzen Anfuehrungszeichen wird der Befehl etwas lesbarer, finde ich.

Nachtrag:
Ich haette das etwa so geschrieben:

Code: Alles auswählen

psql -d lcportal -c 'SELECT c.id, NAME, DEVICE_IP, ADDR FROM dc_vpn_clients c, dc_vpn_addr a WHERE c.id = a.id;' 

(Im Uebrigens sollte der Strichpunkt am Ende eher *in* den SQL-Befehl und nicht in die Shell. In beiden Faellen ist er unnoetig, aber am Ende des SQL-Befehls wird er eigentlich erwartet.)

[HansGraefe]

Ich haette das etwa so geschrieben:

Code: Alles auswählen

psql -d lcportal -c 'SELECT c.id, NAME, DEVICE_IP, ADDR FROM dc_vpn_clients c, dc_vpn_addr a WHERE c.id = a.id;' 

(Im Uebrigens sollte der Strichpunkt am Ende eher *in* den SQL-Befehl und nicht in die Shell. In beiden Faellen ist er unnoetig, aber am Ende des SQL-Befehls wird er eigentlich erwartet.)

Vielen Dank für die Erklärung! Bei deiner Zeile kommt aber:

Code: Alles auswählen

root@server ~ # psql -d lcportal -c 'SELECT c.id, NAME, DEVICE_IP, ADDR FROM dc_vpn_clients c, dc_vpn_addr a WHERE c.id = a.id;'
ERROR:  column "name" does not exist
LINE 1: SELECT c.id, NAME, DEVICE_IP, ADDR FROM dc_vpn_clients c, dc...
                     ^
root@server ~ #

Also muss es doch in "" gestellt werden?

[4A4B]

Innerhalb von " werden die Namen in PostgreSQL als case sensitive behandelt, ohne werden sie dagegen in Kleinbuchstaben konvertiert. Wenn die Spaltennamen Großbuchstaben enthalten, müssen sie in PostgreSQL wohl gequotet werden.

[Meillo]

Innerhalb von " werden die Namen in PostgreSQL als case sensitive behandelt, ohne werden sie dagegen in Kleinbuchstaben konvertiert. Wenn die Spaltennamen Großbuchstaben enthalten, müssen sie in PostgreSQL wohl gequotet werden.

Oh, ja, jetzt wo du es sagst: da war doch was. Dann musst du doch Anfuehrungszeichen verwenden.



OT:

Ich vergesse das nur immer wieder, weil in meinem Umfeld niemand Grossbuchstaben fuer Bezeichner verwendet, wenn es nicht sein muss. Aber bei SQL scheint sich weiterhin die Kultur aus den Zeiten, als Terminals nur Grossbuchstaben konnten, zu halten. Wenn ich den Befehl mit grossbuchstabigen Bezeichnern und Quotes tippe, dann brauche ich dafuer fast doppelt so lange wie wenn ich ihn ohne Quotes und nur mit Kleinbuchstaben tippe, darum wuerde ich mir grossbuchstabige Spaltennamen nicht freiwillig antun.

Die automatische Konvertierung in Kleinbuchstaben von Postgres muss man aus Kompatiblitaetsgruenden leider aufrechterhalten. An sich ist die genau so doof wie bei FAT-Dateisystemen die automatische Konvertierungen in Grossbuchstaben wenn Dateinamen maximal acht Zeichen lang sind und nur Kleinbuchstaben enthalten. Das sind uralte Hacks um mit Terminals, die nur Grossbuchstaben unterstuetzen, kompatibel zu sein.

[HansGraefe]

Genial, vielen vielen Dank an alle!