|
|
| (29 dazwischenliegende Versionen von 2 Benutzern 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
| |
| | |
| | |
| MySQL's INSERT UPDATE:
| |
| * 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 AUTO INCREMENT:
| |
| * MySQL: id INT UNSIGNED NOT NULL AUTO_INCREMENT
| |
| * PostgreSQL: id SERIAL
| |
| * 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 drop table if exist:
| |
| * 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.
| |
| | |
| Unsigned integer (Cardinal):
| |
| * MySQL: column INT UNSIGNED
| |
| * PostgreSQL: column INT CHECK (column > 0)
| |
| | |
| Index-Typ:
| |
| * MySQL: INDEX idx_id(id)
| |
| * PostgreSQL: CREATE INDEX idx_id ON TABLENAME(id);
| |
| | |
| Query Resultset Limit:
| |
| * MySQL: SELECT * FROM tbl LIMIT 10, 5
| |
| * PostgreSQL: SELECT * FROM tbl LIMIT 5 OFFSET 10
| |
| | |
| Data types:
| |
| | |
| | Mysql | | 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 |
| |