PostgreSQL--Oracle数据库迁移至PG数据库，在Navicat中

安装navicat， 

一、迁移表结构和表数据（源库Oracle、目标库PG、表：O_TO_PG）

1、表结构等迁移

1）、复制表结构

在Oracle数据库中，把O_TO_PG表复制表结构，会生成一个O_TO_PG.copy1的仅有表结构的新表。

2）、创建表结构

Navicat 工具栏，选择工具-->数据传输-->源选择Oracle、目标选PG-->勾选仅有表结构的表O_TO_PG.copy1-->点击完成-->执行结果界面出现successful即成功。

3）、优化表名、数据类型、精度等

在PG数据库表中出现O_TO_PG.copy1，修改表名为O_TO_PG，修改数据类型和精度。

在Oracle数据库中数据类型为NUMBER（包含整型和浮点）的，传输到PG数据库为numeric(1000,53)（包含整型和浮点）。

选中表-->表设计 中修改表结构：

把所有numeric(1000,53)改为numeric（即长度为0，小数点为0），修改好点击保存。

注：为了更好的跟Python中的float类型和int类型匹配，避免在数据传输过程中类型转换的精度损失。最好把浮点数设置为float8，整型设置为int8。

其他字段的数据类型和精度根据表具体情况和项目要求进行修改，主外键建议在表数据迁移后设置，提高表数据传输效率。

2、表数据迁移

1）、 Navicat 工具栏，选择工具-->数据传输-->源选择Oracle、目标选PG-->勾选表O_TO_PG-->底部选项设置：创建前删除表(不要勾选)-->点击完成-->执行结果界面出现successful即成功。

注：已建好表结构，传输表数据时记得 创建前删除表不要勾选。

锁住源表：在源数据库锁住表，一旦触发数据传输，任何在表的都不允许更新。

二、PG数据库大小写敏感

1、Pgsql引用大写的对象名字（任意对象名：比如列名，索引名、字段名等）

PostgreSQL对于数据库中对象的名字允许使用支持大小写区分的定义和引用方法。方式就是在DDL中用双引号把希望支持大小的对象名括起来。

# 在python中使用Pgsql，访问PG数据库中的表
import datetime
pg_cols_dict  = {'STUDENT': ['ID', 'NAME', 'START_TIME']}
ONE_DAYS_BEFORE = datetime.date.today() - datetime.timedelta(days=2)

# 查询数据
def get_pg_data(table_name):
    
    sql = "SELECT {} FROM \"{}\" WHERE \"START_TIME\" >= TO_DATA('{}', 'YYYYMMDD');".format(
        ','.join(['"' + col + '"' for col in pg_cols_dict.get(table_name)]),
        table_name,
        ONE_DAYS_BEFORE.strftime('%Y%m%d'))
    print(sql)
    
get_pg_data(table_name = 'STUDENT')
# 结果sql = SELECT "ID","NAME","START_TIME" FROM "STUDENT" WHERE "START_TIME" >= TO_DATA('20230629', 'YYYYMMDD');
# 注：Pgsql默认将双引号内的内容视为标识符而不是字符串，
# 所以引用字段名时'"' + col + '"'需要用单引号把双引号括起来, 
# 引用表名和字段名时\"{}\" 、\"START_TIME\" 双引号前加\转义。

# 插入数据
def write_pg_data(table_name):
    
    sql = "INSERT INTO \"{}\" ({}) VALUES ({});".format(
        table_name, 
        ','.join(['"' + col + '"' for col in pg_cols_dict.get(table_name)]),
        ','.join(['%s' for i in range(len(pg_cols_dict.get(table_name)))]))
    print(sql)
    # cursor.execute(sql, data)  # data为数据的元组或列表，可避免SQL注入攻击
    
write_pg_data(table_name = 'STUDENT')
# 结果sql = INSERT INTO "STUDENT" ("ID","NAME","START_TIME") VALUES (%s,%s,%s);

2、Pgsql大小写规则

字段名为小写：不区分大小写，不管大小写都一律转为为小写。

字段名为大写：区分大小写，并且需要加上双引号才能识别。

3、修改表名、字段名为小写

https://www.cnblogs.com/bcodepod/p/13675068.html

-- 创建exec(sqlstring)函数方便执行
CREATE OR REPLACE FUNCTION "public"."exec"("sqlstring" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
    declare
        res varchar(50);
    BEGIN
        EXECUTE sqlstring;
        RETURN 'ok';
    END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
-- 其中public是你的用户名

-- 通过条件拼接SQL，
-- 根据条件查询所有大写的column
select * from information_schema.columns where table_schema='public' and table_name<>'pg_stat_statements' and column_name <> lower(column_name);

-- 修改条件后，通过下列语句转换column_name中的大写字母
SELECT
    exec('alter table "' || table_name || '" rename column  "' || column_name || '" to ' || lower( column_name ) || ';')
FROM
    information_schema.COLUMNS 
WHERE
    table_schema = 'public' 
    AND column_name <> lower(column_name)
    AND table_name <> 'pg_stat_statements' 
    AND table_name = 'prescribe_state_copy1';
    
-- 查询大写的table_name
SELECT * FROM information_schema.TABLES WHERE   table_schema = 'public' AND table_catalog = 'Guns' AND table_name <> lower( table_name );

-- 修改表名中的大写为小写
SELECT
    exec ( 'alter table "' || table_name || '" rename to ' || lower( table_name ) || ';' ) 
FROM
    information_schema.tables 
WHERE
    table_schema='public' 
    and table_catalog = 'Guns' 
    and table_name <> lower(table_name);

 三、数据类型（PG数据库与Python交互时）

sql = '''CREATE TABLE "user_name"."TEST" (
    "均数" float8,  
    "人数" int8,
    "个数" int4)'''
    # 在PG数据库中是float8(53,0)、int8(64,0)、int4(32,0)。
    # float8（double precision） 是存储双精度浮点数，即64位浮点数，与Python中的float64类型匹配。
    # int8（bigint） 是一个64位整数类型, 与Python中的int64类型的范围是一致的,在使用PG数据库与Python进行数据交互时，可以使用BIGINT类型来保证数据的精度不会受到影响。
    # int4（integer）是固定长度的整数类型，占用4个字节，与Python中的int类型匹配。