PostgreSQL for MySQL Users - Versionsgeschichte

Das-g: nach https://wiki.hsr.ch/Datenbanken/wiki.cgi?PostgreSQLForMySQLUsers verschoben

2017-09-04T09:57:47Z

nach https://wiki.hsr.ch/Datenbanken/wiki.cgi?PostgreSQLForMySQLUsers verschoben

← Nächstältere Version		Version vom 4. September 2017, 09:57 Uhr
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~~
	\|}

Das-g: new section /* MySQL's SHOW */

2017-08-31T10:06:05Z

new section MySQL's SHOW

← Nächstältere Version		Version vom 31. August 2017, 10:06 Uhr
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> ==

Das-g: link to official FAQ about license

2017-08-30T12:41:45Z

link to official FAQ about license

← Nächstältere Version		Version vom 30. August 2017, 12:41 Uhr
Zeile 4:		Zeile 4:
	Licensing:		Licensing:
	* MySQL: Dual Licensed		* MySQL: Dual Licensed
	* PostgreSQL: BSD-Style		* PostgreSQL: [https://wiki.postgresql.org/wiki/FAQ#What_is_the_license_of_PostgreSQL.3F BSD-Style]

	== Internet Resources ==		== Internet Resources ==

Das-g: /* MySQL's AUTO INCREMENT */ also link to official FAQ about this

2017-08-30T12:37:06Z

MySQL's AUTO INCREMENT: also link to official FAQ about this

← Nächstältere Version		Version vom 30. August 2017, 12:37 Uhr
Zeile 43:		Zeile 43:
	);		);

	* See also [https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL Serial Types] in the PostgreSQL documentation		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> ===		=== The Explicit Way: <code>SEQUENCE</code> ===

Das-g: /* MySQL's AUTO INCREMENT */ move link to appropriate subsection

2017-08-30T12:35:18Z

MySQL's AUTO INCREMENT: move link to appropriate subsection

← Nächstältere Version		Version vom 30. August 2017, 12:35 Uhr
Zeile 42:		Zeile 42:
	-- ...		-- ...
	);		);

			* See also [https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL Serial Types] in the PostgreSQL documentation

	=== The Explicit Way: <code>SEQUENCE</code> ===		=== The Explicit Way: <code>SEQUENCE</code> ===
Zeile 54:		Zeile 56:
	'''or''' explicitly each time you insert a new row:		'''or''' explicitly each time you insert a new row:
	INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);		INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);

	* See also [https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL Serial Types] in the PostgreSQL documentation

	== MySQL's <code>INT UNSIGNED</code> (Cardinal) ==		== MySQL's <code>INT UNSIGNED</code> (Cardinal) ==

Das-g: /* MySQL's AUTO INCREMENT */ wikify link

2017-08-30T12:34:40Z

MySQL's AUTO INCREMENT: wikify link

← Nächstältere Version		Version vom 30. August 2017, 12:34 Uhr
Zeile 55:		Zeile 55:
	INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);		INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);

	* See also [https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL 1]		* See also [https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL Serial Types] in the PostgreSQL documentation

	== MySQL's <code>INT UNSIGNED</code> (Cardinal) ==		== MySQL's <code>INT UNSIGNED</code> (Cardinal) ==

Das-g: /* MySQL's AUTO INCREMENT */ remove dead link

2017-08-30T12:32:52Z

MySQL's AUTO INCREMENT: remove dead link

← Nächstältere Version		Version vom 30. August 2017, 12:32 Uhr
Zeile 55:		Zeile 55:
	INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);		INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);

	* See also [https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL 1~~] and [http://www.frankhilliard.com/serialstory.cfm 2~~]		* See also [https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL 1]

	== MySQL's <code>INT UNSIGNED</code> (Cardinal) ==		== MySQL's <code>INT UNSIGNED</code> (Cardinal) ==

Das-g: /* MySQL's AUTO INCREMENT */ link to docs of current PostgreSQL version

2017-08-30T12:32:31Z

MySQL's AUTO INCREMENT: link to docs of current PostgreSQL version

← Nächstältere Version		Version vom 30. August 2017, 12:32 Uhr
Zeile 55:		Zeile 55:
	INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);		INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);

	* See also [~~http~~://www.postgresql.org/docs/~~8.0~~/static/datatype.html#DATATYPE-SERIAL 1] and [http://www.frankhilliard.com/serialstory.cfm 2]		* 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) ==

Das-g: Einheitliche Sprache für diese Wiki-Seite: Englisch

2017-08-30T12:09:14Z

Einheitliche Sprache für diese Wiki-Seite: Englisch

← Nächstältere Version		Version vom 30. August 2017, 12:09 Uhr
Zeile 1:		Zeile 1:
	~~Siehe auch~~:		See also:
	* [[PostgreSQL]]		* [[PostgreSQL]]

Zeile 43:		Zeile 43:
	);		);

	=== ~~Explizite Variante~~: <code>SEQUENCE</code> ===		=== The Explicit Way: <code>SEQUENCE</code> ===
	==== <code>SEQUENCE</code>-Definition ====		==== <code>SEQUENCE</code>-Definition ====
	CREATE SEQUENCE myseq;		CREATE SEQUENCE myseq;
	==== <code>SEQUENCE</code>-~~Verwendung~~ ====		==== <code>SEQUENCE</code>-Usage ====
	'''~~entweder~~''' ~~als Standard-Wert der Spalte~~:		'''either''' as the column's default value:
	CREATE TABLE table_name (		CREATE TABLE table_name (
	Identifier NUMBER DEFAULT nextval('myseq'),		Identifier NUMBER DEFAULT nextval('myseq'),
	-- ...		-- ...
	);		);
	'''~~oder~~''' ~~explizit beim Einfügen neuer Zeilen~~:		'''or''' explicitly each time you insert a new row:
	INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);		INSERT ... INTO table_name VALUES(nextval('myseq'), 'Name', ...);

	* ~~Siehe auch~~ [http://www.postgresql.org/docs/8.0/static/datatype.html#DATATYPE-SERIAL 1] ~~und~~ [http://www.frankhilliard.com/serialstory.cfm 2]		* See also [http://www.postgresql.org/docs/8.0/static/datatype.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) ==
Zeile 61:		Zeile 61:
	* PostgreSQL: <code>column INT CHECK (column > 0)</code>		* PostgreSQL: <code>column INT CHECK (column > 0)</code>

	== MySQL's Index~~-Typ~~ ==		== MySQL's Index Type ==
	* MySQL: <code>INDEX idx_id(id)</code>		* MySQL: <code>INDEX idx_id(id)</code>
	* PostgreSQL: <code>CREATE INDEX idx_id ON TABLENAME(id);</code>		* PostgreSQL: <code>CREATE INDEX idx_id ON TABLENAME(id);</code>

Das-g: mark up SQL keywords & SQL code in prose

2017-08-29T13:19:22Z

mark up SQL keywords & SQL code in prose

← Nächstältere Version		Version vom 29. August 2017, 13:19 Uhr
Zeile 58:		Zeile 58:

	== MySQL's <code>INT UNSIGNED</code> (Cardinal) ==		== MySQL's <code>INT UNSIGNED</code> (Cardinal) ==
	* MySQL: column INT UNSIGNED		* MySQL: <code>column INT UNSIGNED</code>
	* PostgreSQL: column INT CHECK (column > 0)		* PostgreSQL: <code>column INT CHECK (column > 0)</code>

	== MySQL's Index-Typ ==		== MySQL's Index-Typ ==
	* MySQL: INDEX idx_id(id)		* MySQL: <code>INDEX idx_id(id)</code>
	* PostgreSQL: CREATE INDEX idx_id ON TABLENAME(id);		* PostgreSQL: <code>CREATE INDEX idx_id ON TABLENAME(id);</code>

	== MySQL's Query Resultset Limit ==		== MySQL's Query Resultset Limit ==
	* MySQL: SELECT * FROM tbl LIMIT 10, 5		* MySQL: <code>SELECT * FROM tbl LIMIT 10, 5</code>
	* PostgreSQL: SELECT * FROM tbl LIMIT 5 OFFSET 10		* PostgreSQL: <code>SELECT * FROM tbl LIMIT 5 OFFSET 10</code>

	== Data types of MySQL versus PostgreSQL ==		== Data types of MySQL versus PostgreSQL ==