MS SQL to postgre SQL migration

Question

0.00/5 (No votes)

See more:

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

SQL

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

Posted 4-Apr-16 22:59pm

FaizRahmathulla

Updated 5-Apr-16 12:35pm

Patrice T

v2

Add a Solution

Comments

CHill60 5-Apr-16 5:06am

If you type SHOW search_path; does "public" appear in the output?

RedDk 5-Apr-16 12:58pm

You just converted it. So how is there a problem with Postgre?

FaizRahmathulla 7-Apr-16 4:25am

yes it says ""$user", public"

FaizRahmathulla 8-Apr-16 8:00am

Sorry RedDk, Your response was not useful at all...

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DhruveshAkhani · Accepted Answer · 2016-04-05T03:10:00

Hello Faiz

You can set the default search_path at the database level:

SQL

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

Or at the user or role level:

SQL

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

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

RickZeeland · Accepted Answer · 2016-04-05T04:20:00

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 to postgre SQL migration

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0