getting tables list of other schema too

Hello Atul,

You can use set a filter to limit the tables returned, i.e:

\dt college.*

HTH,
Thomas

Le mer. 24 févr. 2021 à 08:54, Atul Kumar <akumar14871@gmail.com> a écrit :

yes I know that, but my doubt is why \dt is showing tables of other
schemas even I am setting the search_path.

Regards,
Atul

On Wed, 2021-02-24 at 15:54 +0530, Atul Kumar wrote:

I have postgres 9.6 cluster running on centos 7 machine.
when I set search_path to any user made schema with below command
[enterprisedb@stg-edb02 ~ 01:51:39]$ psql edb
edb=# \c test
set search_path to college;
and after listing the tables with command \dt, we should get list of
tables of schema college only.
but here I am getting list of tables of schema college and list of
tables of schema sys along with it.
Why is it happening, please suggest.
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------------------------+-------+--------------
college | ta_rule_error | table | college
college | team_import | table | college
college | test_24022021 | table | enterprisedb
sys | callback_queue_table | table | enterprisedb
sys | dual | table | enterprisedb
sys | edb$session_wait_history | table | enterprisedb
sys | edb$session_waits | table | enterprisedb
sys | edb$snap | table | enterprisedb
sys | edb$stat_all_indexes | table | enterprisedb
sys | edb$stat_all_tables | table | enterprisedb
sys | edb$stat_database | table | enterprisedb
sys | edb$statio_all_indexes | table | enterprisedb
sys | edb$statio_all_tables | table | enterprisedb
sys | edb$system_waits | table | enterprisedb
sys | plsql_profiler_rawdata | table | enterprisedb
sys | plsql_profiler_runs | table | enterprisedb
sys | plsql_profiler_units | table | enterprisedb
sys | product_component_version | table | enterprisedb
sys | scheduler_0100_component_name | table | college
sys | scheduler_0200_program | table | college
sys | scheduler_0250_program_argument | table | college
sys | scheduler_0300_schedule | table | college
sys | scheduler_0400_job | table | college
sys | scheduler_0450_job_argument | table | college

yes I know that, but my doubt is why \dt is showing tables of other
schemas even I am setting the search_path.

The problem is that you are not running PostgreSQL, but EnterpriseDB's
closed source fork, and they obviously hacked the "search_path" so that
it automatically includes a "sys" schema, but they were not consistent
enough to exclude that schema from "\dt".

You could complain to EnterpriseDB --- in my opinion, that schema should
only show up in "\dtS" output.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Atul:

On Wed, Feb 24, 2021 at 11:24 AM Atul Kumar <akumar14871@gmail.com> wrote:

yes I know that, but my doubt is why \dt is showing tables of other
schemas even I am setting the search_path.

AFAIK dt list "tables", not "tables in the schemas in search path".

It states " By default, only user-created objects are shown; supply a
pattern or the S modifier to include system objects.", but these sys
schema does not seem to be a system one.

Francisco Olarte.

On Wed, Feb 24, 2021 at 12:12 PM Francisco Olarte
<folarte@peoplecall.com> wrote:

AFAIK dt list "tables", not "tables in the schemas in search path".
It states " By default, only user-created objects are shown; supply a
pattern or the S modifier to include system objects.", but these sys
schema does not seem to be a system one.

Zap it, after (incorrectly after posting) searching for "schema" in
the docs I found, in a paragraph far, far, away, \d* uses search path.

Francisco Olarte.

I am sorry but I am not clear from your response, as I have created
another instance with same version 9.6 but there no system schema or
its tables are visible.

Please help.

Atul.

Due to your top posting style and not being a native english speaker
I'm unable to understand your question.

As all the quotes at the bottom seemed to belong to me, I'm assuming
you referred to some of my postings.

As the last one said I tried to point a thing after checking some
docs, then I noticed some error in my part and tried to tell everybody
to ignore my previous post.

On Wed, Feb 24, 2021 at 3:16 PM Atul Kumar <akumar14871@gmail.com> wrote:

I am sorry but I am not clear from your response, as I have created
another instance with same version 9.6 but there no system schema or
its tables are visible.

Regarding these, I do not know what a "system schema" is May be
"public". Note my post talked about "user cretaed" vs "system" ( I?ve
normally observed those are things like information schema vies,
pg_class an similar tables, which are always there after DB creation
and are normally needed for the server to work ) OBJECTS. Also,
naming an schema "sys" does not make it a system schema.

Francisco Olarte