database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Tutorials 4301 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 4301
Migrating from MySQL to PostgreSQL
작성자
정재익(advance)
작성일
2002-08-15 02:32
조회수
5,691
첨부파일: mysql2pgsql.zip (4,314bytes)

Migrating from MySQL to PostgreSQL

 

by Zach Beane

 

원본출처 : http://www.xach.com/aolserver/mysql-to-postgresql.html

 

--------------------------------------------------------------------------------

 

The Most Important Thing

 

Know SQL. If your only experience with SQL is through MySQL, many things in PostgreSQL (and indeed, any other database system) will seem alien and strange. The idioms you may have developed to work with MySQL may not be the ideal approach when using PostgreSQL. In particular, you have a set of new tools (views, triggers, stored procedures, consistency checks, and more) that you should try to take advantage of.

 

Get a good book on SQL. The Practical SQL Handbook is pretty handy. The free online book SQL for Web Nerds, though Oracle-specific, will give you ideas for good idioms.

 

Converting Dumps

 

So far I've written a Perl script that converts the data model pretty well. It handles enums (it converts them to varchars with check constraints on the possible values) and auto_increment fields. It also converts MySQL's integer and time types to compatible PostgreSQL types.

 

The script outputs SQL statements that seem to work with CVS PostgreSQL. The CVS version will eventually become PostgreSQL 7.1.

MySQL

create table foo (
   id     int not null auto_increment primary key,
   state  enum('enabled', 'disabled')
);

foo's data

 

PostgreSQL

 create table foo (
   id     int not null primary key default nextval('foo_id_seq'),
   state  varchar(8) check (state in ('enabled', 'disabled'))
);

foo's data

 

create sequence foo_id_seq;

select setval('foo_id_seq', (select max(id) from foo));

 

To be handled:

 

. timestamp fields, instead of not null default now(), could be handled by a trigger

. Secondary key definitions in MySQL to create indexes in PostgreSQL

 

Dobrica Pavlinusic(http://www.rot13.org/~dpavlin/projects.html) has also written a mysql2pgsql Perl script(http://www.rot13.org/~dpavlin/projects/sql/mysql2pgsql), but it seems to target an older version of PostgreSQL.

 

'Legacy' Statements

 

MySQL has some interesting functions that are either unavailable in PostgreSQL, or supported by functions of slightly different syntax. For example, ifnull() in MySQL is similar to coalesce() in PostgreSQL.

 

To ease the transition from the MySQL that's embedded in all my pages, I've created a set of SQL functions(http://www.xach.com/aolserver/mysql-functions.sql) that recreate the MySQL functions.

 

Converted:

 

ifnull

from_unixtime

unix_timestamp

date_format

if

to_days

from_days

 

Other Stuff

 

Some stuff looks like it just can't be handled by implementing MySQL functions in PostgreSQL. Fundamental differences exist, mainly where PostgreSQL adheres to the SQL standard and MySQL diverges.

This list isn't an endorsement of MySQL; these examples mainly come up because MySQL is much less conformant with SQL standards in general.

PostgreSQL won't let you use expression aliases in HAVING in queries. In MySQL it's legal to have something like this:

   select name, count(*) as score
   from mytable
   group by name
   having score > 10

For PostgreSQL, you have to write out the expression again: 

   select name, count(*) as score
   from mytable
   group by name
   having count(*) > 10

 

It doesn't mind having aliases in ORDER BY, but you can't use it in HAVING. Tom Lane explains (http://www.xach.com/aolserver/mail/names-in-having.txt).

 

PostgreSQL has a compile-time limit of 31 characters for names (for tables, columns, sequences, functions, etc). If you get a bit verbose when naming your tables, you have to either truncate them or build PostgreSQL with a longer name limit.

NAMEDATALEN is defined in src/include/postgres_ext.h. The name length is NAMEDATALEN - 1.

 

PostgreSQL has something similar to MySQL's auto_increment; this is in the FAQ under SERIAL(http://www.postgresql.org/docs/faq-english.html#4.16.1).

[Top]
No.
제목
작성자
작성일
조회
4336JDBC를 익히자 [1]
정재익
2002-09-09
7525
4324PostgreSQL을 Windows 2000에 설치하기
정재익
2002-09-03
6826
4309Global Transactions - X/Open XA - Resource Managers
정재익
2002-08-23
5883
4301Migrating from MySQL to PostgreSQL
정재익
2002-08-15
5691
4300The PostgreSQL JDBC Primer
정재익
2002-08-15
5678
4285mimic oracle's replace function. versions in pltcl and plpgsql
정재익
2002-08-05
4378
4275LAMPS Tutorial v2.0
정재익
2002-08-01
6567
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2020 DSN, All rights reserved.
작업시간: 0.041초, 이곳 서비스는
	PostgreSQL v13.1으로 자료를 관리합니다