PostgreSQL for MySQL Users: Unterschied zwischen den Versionen

Version vom 31. August 2017, 12:06 Uhr

See also:

• PostgreSQL

Licensing:

• MySQL: Dual Licensed
• PostgreSQL: BSD-Style

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

• Serial Types in the PostgreSQL documentation
• How do I create a serial/auto-incrementing field? in the PostgreSQL FAQ

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