Fetch all tables in a particuler Postgres database using node?

Question

I need to fetch all tables in a particular Postgres database using node. But not finding any way to achieve that. Is there any way to get that?

For example, suppose I have a database named 'TestDatabase' it contains 4 tables( just assume it can have less or more) Person, Company, Clothes, Animal. I need to get the name of all of them using node.

I am also using node-postgres ('pg') to connect with the database.

score 2 · Answer 1 · answered Oct 16 '20 at 14:21

2

This is a generic solution. Use the query below:

SELECT
   relname
FROM
   pg_class
WHERE
   relkind = 'r';

pg_class is the system catalog that holds information on table like objects. Hence the need to restrict relkind to 'r'. This will include all table types. To further restrict see relpersistence at link below.

https://www.postgresql.org/docs/current/catalog-pg-class.html

answered Oct 16 '20 at 14:21

Adrian Klaver

15,886
2
17
28

2

I would modify your question to indicate that as it now says 'I need to fetch all tables in a particular Postgres database ...'. For what you want run the query in @Stefanov.sm answer below as the user you created the tables as. The 'information_schema` queries will only show you tables that the user running the query created or has rights to. – Adrian Klaver Oct 16 '20 at 14:54
I do not know much above Postgres I am getting too much information by executing this ...like pg_statctics, pg_types extra. I need the only tables that I created. – Anuresh Verma Oct 16 '20 at 14:59

Stefanov.sm · Accepted Answer · 2020-10-16T14:58:30.413

As an alternative you can use information_schema. It is not better then the pg_* objects in the system catalog but is standardized and more portable. Here it is:

select table_schema||'.'||table_name as table_fullname
 from information_schema."tables"
 where table_type = 'BASE TABLE'
  and table_schema not in ('pg_catalog', 'information_schema');

The system objects have been filtered by this expression

table_schema not in ('pg_catalog', 'information_schema')

You can further modify it to only include schemas that you need.

Fetch all tables in a particuler Postgres database using node?

2 Answers2