PostgreSQL for MySQL Users: Unterschied zwischen den Versionen

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
(link to official FAQ about license)
(new section MySQL's SHOW)
Zeile 12: Zeile 12:
 
* [http://www.coderholic.com/postgresql-for-mysql-users/ PostgreSQL for MySQL users] (coderholic 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
 
* [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> ==
 
== MySQL's <code>INSERT UPDATE</code> ==

Version vom 31. August 2017, 11:06 Uhr

See also:

Licensing:

Internet Resources

MySQL's SHOW

PostgreSQL uses the SHOW keyword only for displaying run-time parameters. To inspect schemas, tables, views, columns in the command line interface (psql) use the backslash commands (complete list).


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 oder SEQUENCE

Syntactic sugar: SERIAL

CREATE TABLE table_name (
  Identifier SERIAL,
  -- ...
);

See also

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