PostgreSQL/From MySQL - Revision history

Johnno at 02:25, 24 October 2014

2014-10-24T02:25:52Z

Johnno at 01:36, 11 February 2008

2008-02-11T01:36:27Z

Johnno: /* Notes */ ALTER SEQUENCE

2008-02-10T21:52:31Z

Notes: ALTER SEQUENCE

Johnno: Table notes

2008-02-10T21:45:02Z

Table notes

Johnno: REPLACE INTO (lame!!!)

2008-02-08T03:15:05Z

REPLACE INTO (lame!!!)

Johnno at 01:51, 8 February 2008

2008-02-08T01:51:02Z

Johnno at 22:02, 7 February 2008

2008-02-07T22:02:18Z

Johnno at 15:20, 7 February 2008

2008-02-07T15:20:33Z

Johnno: LIMIT syntax

2008-02-07T15:11:21Z

LIMIT syntax

Johnno: /* Notes */

2008-02-06T23:32:27Z

Notes

← Older revision		Revision as of 02:25, 24 October 2014
Line 1:		Line 1:
−	The following table shows how to get around some MySQL-specific non SQL~~-92~~ syntax.	+	The following table shows how to get around some of the weird MySQL-specific, non-standard SQL syntax.

	;Note		;Note

@@ Line 11: / Line 11: @@
 |-
 | MATCH (''column(s)'') AGAINST ''query''    ||  ''ftsindex'' @@ ''query''::tsquery ||  No<sup>(1)</sup>
 |-
 | REPLACE INTO ''table'' (''pkcolumn'', ''column2'') <br /> VALUES ('key', 'value2') || INSERT INTO ''table'' (''pkcolumn'', ''column2'') <br /> SELECT 'key', 'value2' FROM ''table'' <br /> WHERE 'key' NOT IN (SELECT ''pkcolumn'' FROM ''table'') LIMIT 1

@@ Line 56: / Line 56: @@
 Importing rows from a MySQL dump that uses INTEGER AUTO_INCREMENT for primary keys into a new PostgreSQL database using SERIAL primary keys is problematic. Options include:
-# reset the PostgreSQL sequences to start from MAX(''idcolumn'') + 1. <br /> SELECT MAX(''idcolumn'') + 1 AS ''startvalue'' <br /> CREATE SEQUENCE ''newsequence'' INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH ''startvalue'' CACHE 1 NO CYCLE <br /> ALTER TABLE ''table'' ALTER COLUMN ''idcolumn'' SET DEFAULT NEXTVAL('newsequence')
+# reset the PostgreSQL sequences to start from MAX(''idcolumn'') + 1. <br /> SELECT MAX(''idcolumn'') + 1 AS ''startvalue'' <br /> ALTER SEQUENCE ''sequence'' RESTART WITH ''startvalue''
 # edit the dump to remove ''idcolumn'' values from the INSERT statements (highly tedious and probably not easily scriptable)

@@ Line 10: / Line 10: @@
 | LIMIT ''offset'', ''count''    ||  LIMIT ''count'' OFFSET ''offset''  ||  Yes
 |-
-| MATCH (''column(s)'') AGAINST ''query''    ||  ''ftsindex'' @@ ''query''::tsquery ||  No
+| MATCH (''column(s)'') AGAINST ''query''    ||  ''ftsindex'' @@ ''query''::tsquery ||  No<sup>(1)</sup>
 |-
 | REPLACE INTO ''table'' (''pkcolumn'', ''column2'') <br /> VALUES ('key', 'value2') || INSERT INTO ''table'' (''pkcolumn'', ''column2'') <br /> SELECT 'key', 'value2' FROM ''table'' <br /> WHERE 'key' NOT IN (SELECT ''pkcolumn'' FROM ''table'') LIMIT 1
 || No
 |-
-| BOOLEAN <br /> INT <br /> INT(''n'') <br /> INTEGER(''n'') <br /> TINYINT <br /> TINYINT(''n'') <br /> TINYINTEGER <br /> TINYINTEGER(''n'') <br /> MEDIUMINT <br /> MEDIUMINT(''n'') <br /> MEDIUMINTEGER <br /> MEDIUMINTEGER(''n'') <br /> SMALLINT <br /> SMALLINT(''n'') <br /> SMALLINTEGER <br /> SMALLINTEGER(''n'')   ||  INTEGER  ||  Yes<sup>(1)</sup>
+| BOOLEAN <br /> INT <br /> INT(''n'') <br /> INTEGER(''n'') <br /> TINYINT <br /> TINYINT(''n'') <br /> TINYINTEGER <br /> TINYINTEGER(''n'') <br /> MEDIUMINT <br /> MEDIUMINT(''n'') <br /> MEDIUMINTEGER <br /> MEDIUMINTEGER(''n'') <br /> SMALLINT <br /> SMALLINT(''n'') <br /> SMALLINTEGER <br /> SMALLINTEGER(''n'')   ||  INTEGER  ||  Yes<sup>(2)</sup>
 |-
 | BOOLEAN       ||  INTEGER CHECK (''column'' IN ('0', '1')) || No
@@ Line 23: / Line 23: @@
 | INTEGER SIGNED    ||  INTEGER                             || Yes
 |-
-| INTEGER AUTO_INCREMENT    ||  SERIAL PRIMARY KEY    || No<sup>(2)</sup>
+| INTEGER AUTO_INCREMENT    ||  SERIAL PRIMARY KEY    || No<sup>(3)</sup>
 |-
-| VARCHAR(''n'') <br /> CHAR(''n'') <br /> LONGTEXT <br /> BLOB <br /> LONGBLOB    ||  TEXT    || Yes<sup>(3)</sup>
+| VARCHAR(''n'') <br /> CHAR(''n'') <br /> LONGTEXT <br /> BLOB <br /> LONGBLOB    ||  TEXT    || Yes<sup>(4)</sup>
 |-
 | DATETIME <br /> DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'   || TIMESTAMP <br> TIMESTAMP NOT NULL DEFAULT NOW() || No
@@ Line 31: / Line 31: @@
 | '0000-00-00 00:00:00' || NOW() || No
 |-
-| CHARACTER SET UTF8 COLLATE UTF8_BIN ||     || No<sup>(4)</sup>
+| CHARACTER SET UTF8 COLLATE UTF8_BIN ||     || No<sup>(5)</sup>
 |-
-| ''column'' SET('value1', 'value2') || ''column'' TEXT CHECK (''column'' IN ('value1', 'value2')) || No<sup>(5)</sup>
+| ''column'' SET('value1', 'value2') || ''column'' TEXT CHECK (''column'' IN ('value1', 'value2')) || No<sup>(6)</sup>
 |-
 | ''<columndef>'' COMMENT ''comment'' || COMMENT ON COLUMN ''column'' IS 'comment'  || No
@@ Line 43: / Line 43: @@
 === Notes ===
 # PostgreSQL has INT2 (SMALLINT), INT4 (INTEGER) and INT8 (BIGINT), but use INTEGER unless you want ones > 2<sup>31</sup> in which case use BIGINT.
 # The SERIAL type is an auto-incrementing INTEGER sequence (implicitly created at CREATE TABLE time), maintained externally from the table (akin to an index). Autoincrementing integers suck in distributed scenarios, consider a UUID or composite key instead.
 # PostgreSQL couldn't care less, and stores them all as varying text anyway. Insisting on a length won't make it faster or use less disk space.

@@ Line 11: / Line 11: @@
 |-
 | MATCH (''column(s)'') AGAINST ''query''    ||  ''ftsindex'' @@ ''query''::tsquery ||  No
 |-
 | BOOLEAN <br /> INT <br /> INT(''n'') <br /> INTEGER(''n'') <br /> TINYINT <br /> TINYINT(''n'') <br /> TINYINTEGER <br /> TINYINTEGER(''n'') <br /> MEDIUMINT <br /> MEDIUMINT(''n'') <br /> MEDIUMINTEGER <br /> MEDIUMINTEGER(''n'') <br /> SMALLINT <br /> SMALLINT(''n'') <br /> SMALLINTEGER <br /> SMALLINTEGER(''n'')   ||  INTEGER  ||  Yes<sup>(1)</sup>

← Older revision		Revision as of 01:51, 8 February 2008
Line 1:		Line 1:
	The following table shows how to get around some MySQL-specific non SQL-92 syntax.		The following table shows how to get around some MySQL-specific non SQL-92 syntax.
		+
		+	;Note
		+	:After a bunch of editing here, I found this [http://en.wikibooks.org/wiki/Programming:Converting_MySQL_to_PostgreSQL "Converting MySQL to PostgreSQL" wikibook].

	{\| border=1		{\| border=1

← Older revision		Revision as of 15:20, 7 February 2008
Line 49:		Line 49:
	# reset the PostgreSQL sequences to start from MAX(''idcolumn'') + 1. <br /> SELECT MAX(''idcolumn'') + 1 AS ''startvalue'' <br /> CREATE SEQUENCE ''newsequence'' INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH ''startvalue'' CACHE 1 NO CYCLE <br /> ALTER TABLE ''table'' ALTER COLUMN ''idcolumn'' SET DEFAULT NEXTVAL('newsequence')		# reset the PostgreSQL sequences to start from MAX(''idcolumn'') + 1. <br /> SELECT MAX(''idcolumn'') + 1 AS ''startvalue'' <br /> CREATE SEQUENCE ''newsequence'' INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH ''startvalue'' CACHE 1 NO CYCLE <br /> ALTER TABLE ''table'' ALTER COLUMN ''idcolumn'' SET DEFAULT NEXTVAL('newsequence')
	# edit the dump to remove ''idcolumn'' values from the INSERT statements (highly tedious and probably not easily scriptable)		# edit the dump to remove ''idcolumn'' values from the INSERT statements (highly tedious and probably not easily scriptable)
		+
		+	== Script To Update Sequences ==
		+
		+	A quick dirty script to update sequences to start from above the maximum value of the id field. Adjust to requirements.
		+
		+	<pre>
		+	#!/usr/bin/python
		+	import sys
		+	import string
		+	from pyPgSQL import PgSQL
		+
		+	def main(filename):
		+	try:
		+	conn = PgSQL.connect(database='dbname', user='username', host='localhost', password='password')
		+	cur = conn.cursor()
		+	except PgSQL.Error, msg:
		+	print "*** PostgreSQL Connection Error: %s" % msg
		+	return 1
		+
		+	try:
		+	f = open(filename, "r")
		+	except:
		+	print "*** File not found: %s" % filename
		+	return 1
		+
		+	for line in f:
		+	if (line.find("CREATE TABLE ") != -1):
		+	tablename = line.split()[2]
		+	seqname = ""
		+	elif (line.find(" SERIAL ") != -1):
		+	columnname = line.split()[0]
		+	seqname = "\"%s_%s_seq\"" % (tablename.strip('"'), columnname.strip('"'))
		+
		+	if seqname != "":
		+	maxsql = "SELECT MAX(%s)+1 AS seqrestart FROM %s;" % (columnname, tablename)
		+	cur.execute(maxsql)
		+	res = cur.fetchall()
		+	seqrestart = res[0].seqrestart
		+	if seqrestart > 0:
		+	seqsql = "ALTER SEQUENCE %s RESTART WITH %s;" % (seqname, seqrestart)
		+	print seqsql
		+	cur.execute(seqsql)
		+	seqname = ""
		+
		+	cur.close()
		+	conn.commit()
		+	del cur, conn
		+	return 0
		+
		+	if __name__ == "__main__":
		+	sys.exit(main("/path/to/your_schema.sql"))
		+	</pre>

@@ Line 5: / Line 5: @@
 |  '''MySQL'''  ||  '''PostgreSQL'''  ||  '''Supported by both?'''
 |-
-| LIMIT x, y    ||  LIMIT x OFFSET y  ||  Yes
+| LIMIT ''offset'', ''count''    ||  LIMIT ''count'' OFFSET ''offset''  ||  Yes
 |-
 | BOOLEAN <br /> INT <br /> INT(''n'') <br /> INTEGER(''n'') <br /> TINYINT <br /> TINYINT(''n'') <br /> TINYINTEGER <br /> TINYINTEGER(''n'') <br /> MEDIUMINT <br /> MEDIUMINT(''n'') <br /> MEDIUMINTEGER <br /> MEDIUMINTEGER(''n'') <br /> SMALLINT <br /> SMALLINT(''n'') <br /> SMALLINTEGER <br /> SMALLINTEGER(''n'')   ||  INTEGER  ||  Yes<sup>(1)</sup>