top of page
Волнистый абстрактный фон
CerebroSQL

TABLE node for PostgreSQL

List of tables in the selected PostgreSQL database schema. The list can be filtered by entering part of the table name in the " Like name object " field

​

Table list in the PostgreSQL

SELECT c.oid,
      'core' as "Schema",
      c.relname as "Name",                     
      (select count(inhrelid) 
         from pg_inherits 
         where inhparent = c.oid
      ) "CountPar",
       CASE c.relkind        
         WHEN 'r' THEN 'table'  
         WHEN 't' THEN 'TOAST' 
         WHEN 'f' THEN 'foreign table' 
         WHEN 'p' THEN 'partition_table' 
       END as "Type",
      pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
      pg_catalog.obj_description(c.oid, 'pg_class') as "Description",
      u.usename
 FROM pg_catalog.pg_class c                                     
      LEFT JOIN pg_catalog.pg_user u on c.relowner = u.usesysid 
WHERE c.relkind IN ('r','t','f','p')
  AND c.relispartition = false
  and c.relnamespace =  (select oid 
                           from pg_catalog.pg_namespace n 
                          where nspname = $$SCHEMA_NAME)
ORDER BY c.relname

Menu

Узел Table - menu

  • Copy name  - copy object name to clipboard

  • Reindex - generate a command to rebuild indexes on a table 

  • Create code [Select]  - form the text of the query to the object (in the select phrase, the names of all fields are displayed)

  • Create code [Insert]  - form the insert command to insert data

  • Create code [Delete]  - form the delete command to delete all data

  • Create code [Truncate]  - generate a truncate command to clear the table

  • Create code [Drop]  - form a drop command to drop a table

  • Get source  - generate table creation code

  • Get statistics  - display table usage statistics. The data is displayed on the Statistics tab of the main window

  • View data (50 rows)  - display table data (limit 50)

  • Finding functions using a table - search for functions using the selected table. The search is performed in the main process and can take a long time

​

If several nodes are selected in the tree (using the Ctrl button), then the program generates SQL commands for all selected nodes

​Finding functions using a table

select t.*, f.* from 
(
   SELECT  table_name, table_schema,
           table_schema||'.'||table_name full_name,
           table_type
     FROM information_schema.tables
     WHERE table_schema = $$TABLESCHEMA
       and table_name   = $$TABLENAME    
) t
JOIN 
(    
   SELECT n.nspname AS schema_name,
          p.proname AS function_name,
          pg_get_function_arguments(p.oid) AS args,
          pg_get_functiondef(p.oid) AS func_def
   FROM   pg_proc p
   JOIN   pg_namespace n ON n.oid = p.pronamespace
   WHERE  n.nspname not in ('pg_catalog','information_schema') 
      and  n.nspname not like 'pg%'
) f
on position(t.table_name in f.func_def) >0        

Child nodes

TOAST table
List of table columns
Checks created on table columns performed when inserting data
Indexes based on table columns
Table triggers
Foreign keys of a table
Table dependencies (who is referencing it)
Size of the table and its indexes
Table usage statistics
bottom of page