Click here to Skip to main content
15,877,915 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

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
Updated 5-Apr-16 12:35pm
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:

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
Share this answer
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".
Share this answer

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