PostgreSQL for MySQL Users: Unterschied zwischen den Versionen
Aus Geoinformation HSR
Das-g (Diskussion | Beiträge) (Einheitliche Sprache für diese Wiki-Seite: Englisch) |
Das-g (Diskussion | Beiträge) (→MySQL's AUTO INCREMENT: link to docs of current PostgreSQL version) |
||
Zeile 55: | Zeile 55: | ||
INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...); | INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...); | ||
− | * See also [ | + | * See also [https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL 1] and [http://www.frankhilliard.com/serialstory.cfm 2] |
== MySQL's <code>INT UNSIGNED</code> (Cardinal) == | == MySQL's <code>INT UNSIGNED</code> (Cardinal) == |
Version vom 30. August 2017, 14:32 Uhr
See also:
Licensing:
- MySQL: Dual Licensed
- PostgreSQL: BSD-Style
Inhaltsverzeichnis
Internet Resources
- Things to find out about when moving from MySQL to PostgreSQL (PostgreSQL wiki)
- PostgreSQL for MySQL Administrators (opslife blog)
- PostgreSQL for MySQL users (coderholic blog)
- Switching from MySQL to PostgreSQL - tips, tricks and gotchas? on StackOverflow
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
- Oracle:
SEQUENCE
- MySQL:
id INT UNSIGNED NOT NULL AUTO_INCREMENT
- PostgreSQL:
id SERIAL
oderSEQUENCE
Syntactic sugar: SERIAL
CREATE TABLE table_name ( Identifier SERIAL, -- ... );
The Explicit Way: SEQUENCE
SEQUENCE
-Definition
CREATE SEQUENCE myseq;
SEQUENCE
-Usage
either as the column's default value:
CREATE TABLE table_name ( Identifier NUMBER DEFAULT nextval('myseq'), -- ... );
or explicitly each time you insert a new row:
INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);
MySQL's INT UNSIGNED
(Cardinal)
- MySQL:
column INT UNSIGNED
- PostgreSQL:
column INT CHECK (column > 0)
MySQL's Index Type
- 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
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 |