将SQL服务器数据库迁移到 Aurora Postgre SQL-Compatible 涉及架构转换、数据库对象转换、数据迁移和数据加载。由于 Postgre SQL 和 SQL Server 之间的差异(与数据类型、连接对象、语法和逻辑有关),因此最困难的迁移任务是对代码库进行必要的更改,使其在 Post SQL gre 中正常运行。
$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id \
FROM active_student_record b WITH (NOLOCK) \
INNER JOIN student_contributor c WITH (NOLOCK) on c.contributor_id = b.c_st)
对于 PostgreSQL,请转换为:
$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id \
FROM active_student_record b INNER JOIN student_contributor c \
on c.contributor_id = b.c_student_contr_id WHERE b_current_1 is true \
LIMIT $numofRecords)"
my $tableexists = db_read( arg 1, $sql_qry, undef, 'writer');
my $table_already_exists = $tableexists->[0]{table_exists};
if ($table_already_exists){# do some thing
}
else{# do something else
}
SQL服务器:
my $sql_qry = “SELECT OBJECT_ID('$backendTable', 'U') table_exists", undef, 'writer')";
Postgre: SQL
my $sql_qry = “SELECT TO_REGCLASS('$backendTable', 'U') table_exists", undef, 'writer')";
以下示例使用了 inline 中的 Perl 变量SQL,该变量使用带有 a 的SELECT语句JOIN来获取表的主键和键列的位置。
SQL服务器:
my $sql_qry = "SELECT column_name', character_maxi mum_length \
FROM INFORMATION_SCHEMA.COLUMNS \
WHERE TABLE_SCHEMA='$example_schemaInfo' \
AND TABLE_NAME='$example_table' \
AND DATA_TYPE IN ('varchar','nvarchar');";
Postgre: SQL
my $sql_qry = "SELECT c1.column_name, c1.ordinal_position \
FROM information_schema.key_column_usage AS c LEFT \
JOIN information_schema.table_constraints AS t1 \
ON t1.constraint_name = c1.constraint_name \
WHERE t1.table_name = $example_schemaInfo'.'$example_table’ \
AND t1.constraint_type = 'PRIMARY KEY' ;";
$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id \
FROM active_student_record b WITH (NOLOCK) \
INNER JOIN student_contributor c WITH (NOLOCK) on c.contributor_id = b.c_st)
对于 PostgreSQL,请转换为:
$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id \
FROM active_student_record b INNER JOIN student_contributor c \
on c.contributor_id = b.c_student_contr_id WHERE b_current_1 is true \
LIMIT $numofRecords)"
my $tableexists = db_read( arg 1, $sql_qry, undef, 'writer');
my $table_already_exists = $tableexists->[0]{table_exists};
if ($table_already_exists){# do some thing
}
else{# do something else
}
SQL服务器:
my $sql_qry = “SELECT OBJECT_ID('$backendTable', 'U') table_exists", undef, 'writer')";
Postgre: SQL
my $sql_qry = “SELECT TO_REGCLASS('$backendTable', 'U') table_exists", undef, 'writer')";
以下示例使用了 inline 中的 Perl 变量SQL,该变量使用带有 a 的SELECT语句JOIN来获取表的主键和键列的位置。
SQL服务器:
my $sql_qry = "SELECT column_name', character_maxi mum_length \
FROM INFORMATION_SCHEMA.COLUMNS \
WHERE TABLE_SCHEMA='$example_schemaInfo' \
AND TABLE_NAME='$example_table' \
AND DATA_TYPE IN ('varchar','nvarchar');";
Postgre: SQL
my $sql_qry = "SELECT c1.column_name, c1.ordinal_position \
FROM information_schema.key_column_usage AS c LEFT \
JOIN information_schema.table_constraints AS t1 \
ON t1.constraint_name = c1.constraint_name \
WHERE t1.table_name = $example_schemaInfo'.'$example_table’ \
AND t1.constraint_type = 'PRIMARY KEY' ;";