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.