sql-info.de
DeutschEnglish

2. SQL

2.1. Was sind die Vorteile des bool'schen Datentyps BOOLEAN gegenüber z.B. CHAR(1)?

  • Platz: BOOLEAN benötigt 1 Byte, CHAR(1) dagegen mindestens 5;
  • Geschwindigkeit: BOOLEAN-Daten werden als Wert und nicht als Zeiger übermittelt;
  • Flexibilität: BOOLEAN akzeptiert verschiedene Ausdrücke als gültige Eingabewerte, z.B. '1','t', 'yes' bzw. TRUE (siehe Dokumentation: http://www.postgresql.org/docs/current/static/datatype-boolean.html);
  • Portabilität: Der BOOLEAN-Datentyp ist im SQL99-Standard definiert und sollte in anderen modernen Datenbanksystemen vorhanden sein (was leider aber selten der Fall ist).

2.2. Warum funktionieren Typumwandlungen (CAST) wie SELECT bar::INT4 FROM foo nicht, wenn bar als VARCHAR definiert ist?

Die Anzahl möglicher Kombinationen von Typumwandlungen in PostgreSQL wurde auf ein Mindestmaß reduziert, um mögliche Unklarheiten bei einer Umwandlung auszuschließen. Dies führt jedoch dazu, dass manche CASTs sehr explizit definiert werden müssen. Im obigen Fall wäre das SELECT bar::TEXT::INT4 FROM foo (bzw. SELECT CAST(bar AS TEXT)::INT4 FROM foo, bzw. SELECT CAST(CAST(bar AS TEXT) AS INT4) FROM foo).

2.3. Wie kann ich eine Teilmenge identischer Datensätze löschen?

Angenommen eine Tabelle mit mindestens zwei ganz identischen Reihen, von denen einige aber nicht alle gelöscht werden sollen, z.B.:

test=# SELECT * FROM mytable;
 id |      foo       | bar
----+----------------+-----
  1 | Hello World    | 22
  1 | Hello World    | 22
  1 | Hello World    | 22
  1 | Hello World    | 33
  1 | Other text     | 44
(5 rows) 

Eine nahliegende Lösung wäre, LIMIT in Verbindung mit DELETE zu verwenden, z.B. DELETE FROM mytable WHERE id = 1 LIMIT 2, diese Syntax wird jedoch nicht unterstützt.

Eine Lösung wäre, die (versteckte) oid-Spalte einzusetzen: diese Spalte bietet einen prinzipiell unverwechselbaren Identifikator, der in verbindung mit einer Unterabfrage (subquery) dazu verwendet werden kann, eine spezifische Anzahl von Reihen für die Löschung auszuwählen:

DELETE FROM mytable WHERE oid IN (SELECT oid
                                    FROM mytable
                                   WHERE id=1
                                   LIMIT 2)

Zu beachten ist allerdings, dass Tabellen ohne oids erstellt werden können. Zudem ist der Datentyp oid eine 4-byte vorzeichnunbehaftete Ganzzahl (4 byte unsigned integer), bei Tabellen mit mehr als 4294967295 Reihen könnte der oid-Wert wiederholt werden und ist also nicht garantiert unverwechselbar.

Weitere Information über oids befindet sich hier:
http://www.postgresql.org/docs/current/static/datatype-oid.html

2.4. Wie kann ich Textdaten aus einer BYTEA-Spalte in eine TEXT- bzw. VARCHAR- Spalte einfügen?

Bei der Transferierung von Daten aus einer BYTEA-Spalte scheint es keine gültige Kombination von Typumwandlungen zu geben, z.B.:

test=#  UPDATE mytable SET text_col = byte_col;
ERROR:  column "text_col" is of type text but expression is of type bytea
        You will need to rewrite or cast the expression
test=#  UPDATE mytable SET text_col = byte_col::text;
ERROR:  Cannot cast type bytea to text

usw.

Die Funktion DECODE() bietet Abhilfe, z.B.

     UPDATE mytable SET text_col = DECODE(byte_col, 'escape')
   

2.5. Warum liefern nacheinanderfolgende Aufrufe von NOW() immer den gleichen Zeitwert?

test=# BEGIN;
BEGIN
test=# SELECT now();
              now              
-------------------------------
 2003-05-14 22:29:04.074993+02
(1 row)

test=# -- (mehrere Sekunden warten)
test=# SELECT now();
              now              
-------------------------------
 2003-05-14 22:29:04.074993+02
(1 row)

Der Rückgabewert von NOW() (und von der äquivalenten Funktion CURRENT_TIMESTAMP) wird am Anfang einer Transaktion festgelegt und ändert sich im Verlauf der Transaktion nicht.

Die aktuelle (System-) Zeit kann mit TIMEOFDAY() abgefragt werden.

Ein Grund für dieses Verhalten besteht darin, dass es die Kennzeichnung von benutzerdefinierten Funktionen als STABLE ermöglicht.

2.6. In MySQL bewirkt NULL die Einfügung des nächsten Wertes einer AUTO_INCREMENT-Pseudosequenz. Wie kann man das in PostgreSQL emulieren?

In MySQL ist es möglich, Tabellen so zu erstellen:

CREATE TABLE serial_exmpl (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  abc TEXT
)

und einen einmaligen Wert in die AUTO_INCREMENT einzufügen, indem man NULL explizit oder implizit in die Spalte einfügt, z.B.:

INSERT INTO serial_exmpl VALUES(NULL, 'When it is in a MySQL database!')

bzw.

INSERT INTO serial_exmpl 
           (abc)
     VALUES('When is a NULL not a NULL?')
    

Dieses absonderliche Verhalten ergibt folgendes Resultat:

myprogramlisting> SELECT * FROM serial_exmpl;
+----+---------------------------------+
| id | abc                             |
+----+---------------------------------+
|  1 | When is a NULL not a NULL?      |
|  2 | When it is in a MySQL database! |
+----+---------------------------------+
2 rows in set (0.02 sec)

In PostgreSQL würde man so verfahren:

CREATE TABLE serial_exmpl (
  id SERIAL PRIMARY KEY,
  abc TEXT
)    

Anstatt NULL fügt man DEFAULT ein, und PostgreSQL ersetzt dies mit dem nächsten Sequenzwert:

INSERT INTO serial_exmpl VALUES(DEFAULT, 'Remember: NULL is the absence of a value')    
    

Falls es nicht möglich sein sollte, die MySQL-Anweisungen mit DEFAULT umzuschreiben, könnte man für die betreffenden Tabellen einen Trigger so definieren:

CREATE OR REPLACE FUNCTION null_to_default() RETURNS TRIGGER AS '
  BEGIN
    IF NEW.id IS NULL THEN
      SELECT INTO NEW.id nextval(''public.serial_exmpl_id_seq''::text);
    END IF;
    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER serial_exmpl_trg BEFORE INSERT ON serial_exmpl
  FOR EACH ROW EXECUTE PROCEDURE null_to_default();

Dies stellt sicher, dass beim Enfügen eines NULL-Wertes die Spalte automatisch den nächsten Sequenzwert gefüllt wird.

Ab PostgreSQL 8.0 kann man die Funktion etwas eleganter gestalten:

CREATE OR REPLACE FUNCTION null_to_default()
  RETURNS TRIGGER 
  LANGUAGE 'plpgsql'
AS $$
  BEGIN
    IF NEW.id IS NULL THEN
      SELECT INTO NEW.id nextval(pg_get_serial_sequence('serial_exmpl','id'));
    END IF;
    RETURN NEW;
  END;
$$;

2.7. Wo finde ich die SQL-Standards?

Die relevanten Standards wie SQL92 und SQL99 sind nicht gerade einfach zu finden. Insbesondere SQL99 scheint nur kostenpflichtig verfügbar zu sein.

Die folgenden Links enthalten weitere Hinweise (engl.):