How to remove the schema name from tables for use with set search_path in PostgreSQL?

Asked Dec 14 '16 at 04:56

Active Dec 14 '16 at 05:53

Viewed 237 times

PostgreSQL 9.5.4

In changing around the names on my schemas in the database, PostgreSQL added the schema name to each of the tables in the schema.

That is, companies became public.companies. Now in setting the default path, with

ALTER DATABASE chaos SET search_path TO public;

select * from companies fails. However, select * from public.companies works.

What can be done to either remove the unwanted schema name from its tables, and/or establish a search_path that respects the simple

select * from companies

TIA

Edit: The opening database definition does a "set search_path to public". However, when doing a "show search_path" in a PgAdmin III query session, "nova" is returned with "show search_path" -- is the problem with PgAdmin ?

edited Dec 14 '16 at 05:53

asked Dec 14 '16 at 04:56

Alan Wayne

5,122
10
52
95

1

`SET search_path TO public;` – Sergey Gornostaev Dec 14 '16 at 05:25
2

What does `show search_path;` return? If it's set for the user you're connected as it will override the database default. – teppic Dec 14 '16 at 05:34
@teppic "show search_path" shows "nova" --the name prior to renaming the schema's. "Alter database chaos set search_path to public" seems not to be respected?? – Alan Wayne Dec 14 '16 at 05:38
@SergeyGornostaev "Set search_path to public:" worked -- but is it permanent? – Alan Wayne Dec 14 '16 at 05:44
1

Use `alter role set search_path to public;` to make it permanent. – teppic Dec 14 '16 at 05:50
@teppic Great! "alter role postgres set search_path to public;" -- worked! That will save me alot of time. :) – Alan Wayne Dec 14 '16 at 05:59
After `ALTER DATABASE chaos SET ...` you should to reconnect to get the effect. – Abelisto Dec 14 '16 at 06:24

How to remove the schema name from tables for use with set search_path in PostgreSQL?

0 Answers0