|
|
Zeile 1: |
Zeile 1: |
− | See also:
| + | Siehe [https://wiki.hsr.ch/Datenbanken/wiki.cgi?PostgreSQLForMySQLUsers HSR-Datenbanken-Wiki]. |
− | * [[PostgreSQL]]
| |
− | | |
− | Licensing:
| |
− | * MySQL: Dual Licensed
| |
− | * PostgreSQL: [https://wiki.postgresql.org/wiki/FAQ#What_is_the_license_of_PostgreSQL.3F 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>SHOW</code> ==
| |
− | | |
− | PostgreSQL uses the <code>SHOW</code> keyword only for displaying run-time parameters. To inspect schemas, tables, views, columns in the command line interface (<code>psql</code>) use [https://wiki.postgresql.org/wiki/FAQ#How_do_I_find_out_what_tables.2C_indexes.2C_databases.2C_and_users_are_defined.3F_How_do_I_see_the_queries_used_by_psql_to_display_them.3F the backslash commands] ([https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS complete list]).
| |
− | | |
− | | |
− | == 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,
| |
− | -- ...
| |
− | );
| |
− | | |
− | See also
| |
− | * [https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL Serial Types] in the PostgreSQL documentation
| |
− | * [https://wiki.postgresql.org/wiki/FAQ#How_do_I_create_a_serial.2Fauto-incrementing_field.3F How do I create a serial/auto-incrementing field?] in the PostgreSQL FAQ
| |
− | | |
− | === The Explicit Way: <code>SEQUENCE</code> ===
| |
− | ==== <code>SEQUENCE</code>-Definition ====
| |
− | CREATE SEQUENCE myseq;
| |
− | ==== <code>SEQUENCE</code>-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 <code>INT UNSIGNED</code> (Cardinal) ==
| |
− | * MySQL: <code>column INT UNSIGNED</code>
| |
− | * PostgreSQL: <code>column INT CHECK (column > 0)</code>
| |
− | | |
− | == MySQL's Index Type ==
| |
− | * MySQL: <code>INDEX idx_id(id)</code>
| |
− | * PostgreSQL: <code>CREATE INDEX idx_id ON TABLENAME(id);</code>
| |
− | | |
− | == MySQL's Query Resultset Limit ==
| |
− | * MySQL: <code>SELECT * FROM tbl LIMIT 10, 5</code>
| |
− | * PostgreSQL: <code>SELECT * FROM tbl LIMIT 5 OFFSET 10</code>
| |
− | | |
− | == 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
| |
− | |}
| |