MS SQL到postgre的SQL迁移 [英] MS SQL to postgre SQL migration

问题描述

嗨朋友

我刚刚使用迁移工具（Convertdb）将MS SQL 2012数据库转换为Postgre SQL 9.5（在我的Windows 8机器上托管）



一切正常，除了一个小问题



我不能在没有提到架构的情况下查询数据库

 从 tblmastitems中选择 * 抛出错误，但
 
 选择 * 从  public 。  tblmastitems工作正常

< br $> b $ b

如何摆脱这个笨重的公众。tableName



提前致谢

- Faiz



我尝试过：



我是Postgre SQL的新手

我用google搜索解决方案，

Searc针对类似问题的代码项目



将备份恢复到新创建的数据库

没有什么对我有用

解决方案

Hello Faiz

您可以设置默认的search_path在数据库级别：

  ALTER  数据库< database_name>  SET  search_path  TO  schema1，schema2; 

或者在用户或角色级别：

  ALTER   ROLE < role_name>  SET  search_path  TO  schema1，schema2; 

本文档可能会对您有所帮助

PostgreSQL：文档：9.3：客户端连接默认值

我认为公共模式的解决方案问题，但如果这不起作用，您可以尝试这种具有架构支持的免费转换器：

将SQL Server数据库转换为PostgreSQL [ ^ ]



您可以查看默认架构在pgAdmin中，单击数据库并查看右侧的属性，它应该是public。

Hi Friends
I just converted an MS SQL 2012 Database to Postgre SQL 9.5 (Hosted on my Windows 8 Machine) using a migration tool (Convertdb)

everything is working fine, except for a small problem

I can't just query the database without the schema mentioned

Select * from tblmastitems throws an error but

Select * from public."tblmastitems" works fine

How can I get rid of this cumbersome Public."tableName"

Thanks in advance
- Faiz

What I have tried:

I'm newbie to Postgre SQL
I googled for a solution,
Searched Code Project for a similar problem

Restored the backup into a freshly created database
Nothing worked for me alright

解决方案

Hello Faiz

You can set the default search_path at the database level:

ALTER DATABASE <database_name> SET search_path TO schema1,schema2;

Or at the user or role level:

ALTER ROLE <role_name> SET search_path TO schema1,schema2;

This documentation might be helpful
PostgreSQL: Documentation: 9.3: Client Connection Defaults

I think the solution for the public schema problem is already given in Solution1, but if this does not work you might try this free converter which has schema support:
Convert SQL Server Database to PostgreSQL[^]

You can check the "Default schema" in pgAdmin, click on the database and look at the properties on the right, it should be "public".

这篇关于MS SQL到postgre的SQL迁移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！