PostgreSQL for MySQL Users: Unterschied zwischen den Versionen

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
(new section MySQL's SHOW)
(nach https://wiki.hsr.ch/Datenbanken/wiki.cgi?PostgreSQLForMySQLUsers verschoben)
 
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
 
|}
 

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

Siehe HSR-Datenbanken-Wiki.