Notification texts go here Contact Us Buy Now!

PostgreSQL query to list all table names?

What bout this query (based on the description from manual)?

SELECT table_name
  FROM information_schema.tables
 WHERE table_schema='public'
   AND table_type='BASE TABLE';

Yes one may obtain a list of user defined tables present in a database using a query like this:

SELECT
  table_name
FROM
  information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_type='BASE TABLE'
As reads in the official docs:

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types...

So it's a good target for filtering out the user-defined stuff from the builtin ones. Stay aware that since its possible to define tables in schemas other than the public schema, relying on filtering of the results on table_schema as suggested by the other people and also in other places like the google cloud's docs, is not a good option, because it may neglect the user-defined tables on the other builtin or user-defined schema.

Post a Comment

Cookie Consent
We serve cookies on this site to analyze traffic, remember your preferences, and optimize your experience.
Oops!
It seems there is something wrong with your internet connection. Please connect to the internet and start browsing again.
AdBlock Detected!
We have detected that you are using adblocking plugin in your browser.
The revenue we earn by the advertisements is used to manage this website, we request you to whitelist our website in your adblocking plugin.
Site is Blocked
Sorry! This site is not available in your country.