Get the names of the tables of a BD in postgresql


I am doing a script to audit the tables of a postgres database, for which I need to obtain the names of the tables that are in the database and in this way I can go through them through an array, since the Database has many tables and I do not want to do it by hand.

Is there a query in postgresql to get the names of the tables that make up the database?

asked by Juan Pinzón 16.06.2017 в 18:25

3 answers


Based on the official PostgreSQL documentation about the database schema information , what could be done is to consult the information of the tables stored in information_schema since in this there is a table called tables in which is found the name of each table that makes up the database. The query would be the following:

SELECT table_name --seleccionamos solo la columna del nombre de la tabla
FROM information_schema.tables --seleccionamos la información del esquema 
WHERE table_schema='public' --las tablas se encuentran en el esquema publico
AND table_type='BASE TABLE'; --tiene que ser del tipo table ya que aqui se listan tambien las vistas 

In this way the names of the tables that make up the database will be obtained, generating a result that can be iterated in a programming language.

answered by 16.06.2017 / 18:25

Specifically, if you are looking for a procedure to perform any spontaneous action with these tables, I usually build a script at the moment with a very easy structure and it is not necessary to create a function, execute it and then delete it.

Using the query of Juan Pinzón, the script would be as follows:

    _schema text;
    _table text;
    FOR _schema, _table IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        -- No filtro por esquema 'public' porque yo uso muchos más esquemas
        WHERE table_type = 'BASE TABLE'
        -- En este bloque LOOP puedes introducir tu código para hacer lo que quieras
        -- con las tablas
        -- Usando las variables _schema, _table
        RAISE NOTICE 'Código a ejecutar con %, %', _schema, _table;

answered by 21.06.2017 в 18:01

another very useful and proven option; present the same doubt as you


or it can be

=>SELECT * FROM pg_catalog.pg_tables;

merit to link

answered by 16.06.2017 в 18:38