Migrate the system database from MySQL 5.5 to PostgreSQL 9.1

Migrate the system database from MySQL 5.5 to PostgreSQL 9.1 in Windows Server 2003x64 Simplified Chinese, MySQL 5.5 (UTF8 encoding), PostgreSQL 9.1.4-1 (UTF8 encoding) Spring 3.0.7, struts 2.3.4, Hibernate 3.5.5 migration from MySQL to PostgreSQL * differences in writing in DDL definitions ** the former can be a symbol "'" (excluding quotation marks, on the keyboard is the character corresponding to the key under the Esc key) to enclose the table name, column name, etc., the latter is not allowed. * The Escape Character MySQL www.2cto.com \ PostgreSQL does not support the common \ by default, but uses the single quotation mark '* you can use the DB name in DBMySQL; PostgreSQL does not support the use keyword! * There is no difference between the two primary key constraints. * The auto-increment primary key MySQL writes the create table users (id INT (11) not null AUTO_INCREMENT, name VARCHAR (50) not null, primary key (id); in PostgreSQL, create table users (id serial not null, name VARCHAR (50) not null, primary key (id); note: postgreSQL implicitly generates a SEQUENCE named table name_pk name_seq for columns of the serial data type. In this example, the SEQUENCE name is users_id_seq. * Change the auto-increment sequence of an existing table 01 www.2cto.com -- for more secure and effective solutions, see <a href =" http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync "Target =" _ blank "rel =" nofollow "> http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync </A>/* assume that table a is migrated from MySQL with 100 data records. If no sequence is specified when the table is created, PG will default a 02sequence to the primary key column of this table -- it is increasing by 1. If Hibernate is used to add data to Table a, an error is returned, indicating that primary key 1 already exists! 03. After the table is migrated, you can make a slight modification to the sequence of the table, so that it can start from the maximum value of the primary key of the current table plus 1! You can solve the problem that error occurs when 04Hibernate is added. 05 06 alter sequence "public ". "Table name_primary key name_seq" restart with (maximum value of PK + 1 ); 07 or 08 alter sequence table name_primary key name_seq restart with (maximum value of PK + 1); 09 10e. g.11ALTER SEQUENCE file_types_id_seq "restart with 10; 12 www.2cto.com 13 a simpler and more effective SQL statement obtained from the above stackoverflow.com Website: 14 SELECT pg_catalog.setval (values ('table _ name ', 'id'), (select max (id) FROM table_name) + 1); 1516 */* unique key constraint MySQL UN Ique key name (name) PostgreSQL UNIQUE (name) * built-in SQL function difference MySQL formatting date DATE_FORMAT (CURRENT_TIMESTAMP, '% Y-% m-% d % H: % I: % s') PostgreSQL format date to_char (CURRENT_TIMESTAMP, 'yyyy-mm-dd hh24: mi: ss') * date type MySQL date time datetimePostgreSQL timestamp * Boolean Type MySQL wood has, it can be simulated by int (1), enumeration, or string. PostgreSQL boolean www.2cto.com * foreign key constraints create a TABLE with foreign keys in MySQL: 01 drop table if exists recipient_recipientgroup; 02 03 create table if not exists recipient_recipientgroup (04 id serial not null, 05 recipient_id integer default null, 06 recipient_group_id integer default null, 07 primary key (id ), 08 KEY FK_recipient_recipientgroup_recipient (recipient_id), 09 KEY FK_recipient_recipientgroup_recipient_group (re Cipient_group_id), 10 CONSTRAINT limit foreign key (recipient_id) REFERENCES recipient (id), 11 CONSTRAINT limit foreign key (recipient_group_id) REFERENCES recipient_group (id) 12 ); the statement for creating a table with a foreign KEY in PostgreSQL is as follows: (remove the KEY... this sentence !) 01 drop table if exists partition; 02 03 create table if not exists recipient_recipientgroup (04 www.2cto.com id serial not null, 05 recipient_id integer default null, 06 recipient_group_id integer default null, 07 primary key (id), 08 -- KEY FK_recipient_recipientgroup_recipient (recipient_id), 09 -- KEY pair (recipient_group_id), 10 CONSTRAINT FK_recipie Nt_recipientgroup_recipient foreign key (recipient_id) REFERENCES recipient (id), 11 CONSTRAINT rule foreign key (recipient_group_id) REFERENCES recipient_group (id) 12 ); code modification ---------- * modify the IdEntity class provided in SpringSide 3.3.4 as follows 01/** 02 * refer to SpringSide3 to define the entity base class of the id. 03*04 * The base class uniformly defines the property name, data type, column name ing, and generation policy of the id. 05 * The subclass can reload the getId () function to redefine the id column name ing and generation policy. 06 */www.2cto.com 07 // JPA Id of the base class 08 @ MappedSuperclass09public abstract class IdEntity {10 11 protected Long id; 12 13 @ Id14 // @ GeneratedValue (strategy = GenerationType. AUTO) 15 @ GeneratedValue (strategy = GenerationType. IDENTITY) 16 @ Column (unique = true, nullable = false) 17 public Long getId () {18 return this. id; 19} 2021 public void setId (Long id) {22 this. id = id; 23 www.2cto.com} 24 25} * Change 01 public List in SQL/HQL Code <LeakageDet Ail> findExceptLeakageDetailList (String ids) {02 String queryString = "SELECT * FROM leakage_detail" 03 + "WHERE" // -- DATE_FORMAT (find_date, '% Y % m ') <(DATE_FORMAT (NOW (), '% Y % m')-1) AND04 + "CONCAT (find_date, find_process) IN (" 05 + "select concat (find_date, find_process) AS xx "06 +" FROM leakage_detail WHERE id IN ("+ ids +") "07 +" group by find_date, find_process "08 // +" having count (xx)> 5) "; // This method is supported by MySQL, not by PostgreSQL! 09 + "having count (CONCAT (find_date, find_process)> 5) AND id IN (" + ids + ") order by find_date, find_process "; 10 logger.info ("Query SQL->" + queryString) for emails with abnormal liquid Leakage when data is imported by the Leakage module; 11 query Query = getSession (). createSQLQuery (queryString ). addEntity (LeakageDetail. class); 12 return query. list (); 13 www.2cto.com} 01 public List <StatisticalAnalysisVo> getStatisticalAnalysisList () {02 // String hql = "select works Hop as name, count (id) as num from DataModel where date_format (create_at, '% Y-% m') = date_format (now (),' % Y-% m ') group by workshop "; 03 String SQL =" select workshop as name, count (id) as num "04 +" from data_models "05 // +" where date_format (create_at, '% Y-% m') = date_format (now (),' % Y-% m') "// the date_format function is MySQL-specific 06 +" where to_char (create_at, 'yyyy-mm') = to_char (now (), 'yyyy-mm') "// in PostgreSQL The date Formatting Function is to_char07 + "group by workshop"; 08 // Query query = getSession (). createSQLQuery (hql); 09 Query query = getSession (). createSQLQuery (SQL ). addScalar ("name", Hibernate. STRING ). addScalar ("num", Hibernate. LONG); 10 query. setResultTransformer (Transformers. ALIAS_TO_ENTITY_MAP) 11. setResultTransformer (Transformers. aliasToBean (StatisticalAnalysisVo. class); 12 return query. list (); 13 www.2cto.com} * Stored Procedure Change MySQL01DROP procedure if exists 'calculclp '; 02 DELIMITER // 03 create definer = 'root' @ 'localhost' PROCEDURE 'calculclp '() 04 COMMENT the Stored Procedure '05inin06 07 SELECT (@ rownum: = @ rownum + 1) AS 'id', DATE_FORMAT (lot_no_to_date, '% Y % m') AS year_and_month, 08 'model _ no', group_no, 09 SUM (liquid_injected_input_num) AS total_input, 10 SUM (short_circuit_num) AS total_short, 11 COUNT (DISTINCT (lot_no) S round, ROUND (SUM (ROUND)/COUNT (DISTINCT (lot_no) AS sample_size_n13, ROUND (SUM (short_circuit_num)/SUM (ROUND), 4) as round from hipot, (SELECT @ rownum: = 0) AS r WHERE liquid_injected_input_num! = 015 www.2cto.com group by 'model _ no', group_no, year_and_month16; 1718END // 19 DELIMITER; PostgreSQL view sourceprint? 01 drop function if exists calcUlclp (); 02 03 create or replace function calcUlclp () 04 returns setof record AS05 $ BODY $06 declare07 -- SQL varchar; 08 rownum int; 09 v_rc record; 10BEGIN11 for v_rc in12 www.2cto.com SELECT (rownum = rownum + 1) AS id, to_char (bytes, 'yyyymm') AS year_and_month, 13 model_no, group_no, 14 SUM (bytes) AS total_input, 15 SUM (short_circuit_num) AS total _ Short, 16 COUNT (DISTINCT (lot_no) as round, ROUND (SUM (DISTINCT)/COUNT (DISTINCT (lot_no) AS sample_size_n18, ROUND (SUM (short_circuit_num) /SUM (liquid_injected_input_num), 4) AS nonconforming_rate_mean_p19 FROM hipot, (SELECT rownum = 0) AS r WHERE liquid_injected_input_num! = 020 group by model_no, group_no, year_and_month21 loop22 return next v_rc; 23 end loop; 24 25END; 26 $ BODY $27 LANGUAGE 'plpgsql' VOLATILE; 28 www.2cto.com 29 -- call Stored Procedure 30/* 31 SELECT * from calcUlclp () as32 t (id _ boolean, year_and_month text, model_no varchar, group_no varchar, 33 total_input bigint, total_short numeric, month_num_of_product_days bigint, 34 sample_size_n double precision, nonconforming_rate_mean_p numeric); 35 */author leeoo