Click here to Skip to main content
15,306,142 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 5-Apr-16 12:35pm
v2
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...

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
   
v2
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".
   
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900