postgresql: for a given table name what is schema name?

Question

I have a table name student and like 35 schemas in my DB. how can i get in which schema the table student exists? (there might be more than one in different schemas).

I've tried through pg_class but I don't know how to get schema name from there.

postgresql.org/docs/9.4/static/catalog-pg-class.html The relnamespace field points to pg_namespace.oid, which contains the schemas — wildplasser
– wildplasser, Commented Jun 3, 2015 at 13:46

Mureinik · Accepted Answer · 2015-06-03 13:46:23Z

10

You could query it from information_schema.tables:

SELECT table_catalog, table_schema 
FROM   information_schema.tables 
WHERE  table_name = 'student'

answered Jun 3, 2015 at 13:46

Mureinik

316k54 gold badges398 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Over a year ago

what table_catalog means? will it work on views as well?

Mureinik Over a year ago

@John table_catalog is the name of the database containing the table. If you already know it, you can omit it from the query. And yes, this will work for views as well.

Syscall · Accepted Answer · 2022-01-30 10:41:48Z

2

select schemaname,relname from pg_stat_user_tables;

Example:

dvdrental=# select schemaname,relname from pg_stat_user_tables;

schemaname	relname
public	actor
public	category
public	payment
public	film
public	staff

edited Jan 30, 2022 at 10:41

Syscall

19.8k10 gold badges44 silver badges60 bronze badges

answered Jan 30, 2022 at 7:09

Kondala vinod raja

513 bronze badges

Collectives™ on Stack Overflow

postgresql: for a given table name what is schema name?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related