PostgreSQL for MySQL Users: Unterschied zwischen den Versionen

Aus Geometa Lab OST
Zur Navigation springen Zur Suche springen
(→‎MySQL's AUTO INCREMENT: fix SQL syntax error: CREATE TABLE & INSERT INTO erfordern einen Tabellen-Name)
(nach https://wiki.hsr.ch/Datenbanken/wiki.cgi?PostgreSQLForMySQLUsers verschoben)
 
(10 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:
Siehe auch:
Siehe [https://wiki.hsr.ch/Datenbanken/wiki.cgi?PostgreSQLForMySQLUsers HSR-Datenbanken-Wiki].
* [[PostgreSQL]]
 
Licensing:
* MySQL: Dual Licensed
* PostgreSQL: BSD-Style
 
== Internet Resources ==
 
* [https://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL Things to find out about when moving from MySQL to PostgreSQL] (PostgreSQL wiki)
* [https://opslife.org/postgresql-for-mysql-administrators/ PostgreSQL for MySQL Administrators] (opslife blog)
* [http://www.coderholic.com/postgresql-for-mysql-users/ PostgreSQL for MySQL users] (coderholic blog)
* [http://stackoverflow.com/questions/772111/switching-from-mysql-to-postgresql-tips-tricks-and-gotchas Switching from MySQL to PostgreSQL - tips, tricks and gotchas?] on StackOverflow
 
== MySQL's <code>INSERT UPDATE</code> ==
Option 1) Write a function like:
  CREATE FUNCTION doinsert(_id integer, _value text) RETURNS void AS $$
  BEGIN
  UPDATE thetable SET value = _value WHERE id = _id;
  IF NOT FOUND THEN
      INSERT INTO thetable(id, value) VALUES (_id, _value);
    END IF
  END;
  $$ LANGUAGE plpgsql;
 
Option 2) Use two SQL statements:
  -- update the existing
  UPDATE realTable SET VALUE = (SELECT VALUE FROM tmp WHERE tmp.id = realTable.id)
  WHERE EXISTS (SELECT VALUE FROM tmp WHERE tmp.id = realTable.id);
  -- insert the missing
  INSERT INTO realTable(id, value)
  SELECT id, value FROM tmp WHERE NOT EXISTS(SELECT 1 FROM realTable WHERE tmp.id = realTable.id);
 
== MySQL's <code>AUTO INCREMENT</code> ==
* Oracle: <code>SEQUENCE</code>
* MySQL: <code>id INT UNSIGNED NOT NULL AUTO_INCREMENT</code>
* PostgreSQL: <code>id SERIAL</code> oder <code>SEQUENCE</code>
 
=== Syntactic sugar: <code>SERIAL</code> ===
CREATE TABLE table_name (
  Identifier SERIAL,
  -- ...
);
 
===  Explizite Variante: <code>SEQUENCE</code> ===
==== <code>SEQUENCE</code>-Definition ====
CREATE SEQUENCE myseq;
==== <code>SEQUENCE</code>-Verwendung ====
'''entweder''' als Standard-Wert der Spalte:
CREATE TABLE table_name (
  Identifier NUMBER DEFAULT nextval('myseq'),
  -- ...
);
'''oder''' explizit beim Einfügen neuer Zeilen:
INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);
 
* Siehe auch [http://www.postgresql.org/docs/8.0/static/datatype.html#DATATYPE-SERIAL 1] und [http://www.frankhilliard.com/serialstory.cfm 2]
 
== MySQL's <code>DROP TABLE IF EXIST</code> ==
* Existiert auch in PostgreSQL ab Version 8.2
* Bei früheren Versionen gibt es einen Workaround:
** % select count(*) from pg_class where relname = 'mytable'
** return 1 if mytable exists, and 0 if it doesn't
** so you can run a DROP TABLE command (or not) based on the returned value.
 
== MySQL's <code>INT UNSIGNED</code> (Cardinal) ==
* MySQL: column INT UNSIGNED
* PostgreSQL: column INT CHECK (column > 0)
 
== MySQL's Index-Typ ==
* MySQL: INDEX idx_id(id)
* PostgreSQL: CREATE INDEX idx_id ON TABLENAME(id);
 
== MySQL's Query Resultset Limit ==
* MySQL: SELECT * FROM tbl LIMIT 10, 5
* PostgreSQL: SELECT * FROM tbl LIMIT 5 OFFSET 10 
 
== Data types of MySQL versus PostgreSQL ==
 
{| class="mw-datatable"
|-
! colspan=2 | Mysql                !! colspan=2 | PostgreSQL!!                     
|-
! Typ        !! Beschreibung        !! Typ                  !! Beschreibung         
|-
| tinyint    || -128..127          || -                    || -                   
|-
| smallint  || -32768..+32767      || int2                  || -32767 ... +32768   
|-
| int        || -2147483648.. +2147483647 || int4            || -2147483648 ... +2147483647 
|-
|            ||                    || int8                  || +/- 18 Dezimalstellen
|-
| float(n)  || n E {4;8}, Einf./doppelte Genauig.|| float4  || 6 Dez.Stellen
|-
|            ||                    || float8                || 15 Dezimalstellen
|-
| date      || YYYY-MM-DD Datumsformat || date              || Datum, Datumformate mit SET DATESTYLE=Value einstellbar
|-
| time      || HH-MM-SS Zeitformat || time                  || Uhrzeit, Auflösung 1 Mikrosekunde
|-
| char(m)    || Zeichenkette mit fester Länge || char        || ein Zeichen
|-
| varchar(m) || variable Länge, max. m Zeichen || varchar(n) || 4+n Bytes
|-
| blob      || Binary Large Object, wird für Texte gebraucht, "TINY", "", "LONG" || bytea
|-
|            ||                    || money                || 4 Byte, -21474836,48 ... +21474836,47 
|-
|            ||                    || text                  || Variable Länge
|-
|            ||                    || bool                  || Kann den Wert 't' oder 'f' annehmen
|}

Aktuelle Version vom 4. September 2017, 09:57 Uhr

Siehe HSR-Datenbanken-Wiki.

Von «https://giswiki.ch/index.php?title=PostgreSQL_for_MySQL_Users&oldid=60661»