skip to Main Content

I often find myself wanting to run a query like this on multiple different databases:

select n, pg_size_pretty(s) from (
  select n, pg_total_relation_size(n) s from (
    select format('%I.%I', table_schema, table_name) n from information_schema.tables
  )
)
order by s desc;

How can I make an alias for this query so that I can psql into any database and run it easily?

2

Answers


  1. Chosen as BEST ANSWER

    I found out I can put an alias in my ~/.psqlrc:

    set tablesizes 'select n, pg_size_pretty(s) from ( select n, pg_total_relation_size(n) s from ( select format(''%I.%I'', table_schema, table_name) n from information_schema.tables)) order by s desc;'
    

    Then I can use it like this:

    $ psql $DB_URL
    postgres=# :tablesizes
    ┌───────────────────────────────────────────────────────────────────────┬────────────────┐
    │                                   n                                   │ pg_size_pretty │
    ├───────────────────────────────────────────────────────────────────────┼────────────────┤
    │ pg_catalog.pg_proc                                                    │ 1448 kB        │
    │ pg_catalog.pg_attribute                                               │ 848 kB         │
    │ pg_catalog.pg_rewrite                                                 │ 824 kB         │
    │ pg_catalog.pg_description                                             │ 616 kB         │
    │ pg_catalog.pg_depend                                                  │ 448 kB         │
    │ pg_catalog.pg_statistic                                               │ 336 kB         │
    

  2. Use CREATE VIEW. It is basically a query alias that is executed every time you access it.

    
    -- create
    CREATE TABLE employee (
      empId INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      dept TEXT NOT NULL
    );
    
    -- insert
    INSERT INTO employee VALUES (0001, 'Clark', 'Sales');
    INSERT INTO employee VALUES (0002, 'Dave', 'Accounting');
    INSERT INTO employee VALUES (0003, 'Ava', 'Sales');
    
    
    CREATE VIEW odd_employee AS (
      --- Wrote repeated query here.
      SELECT empId, name
      FROM employee
      WHERE empId % 2 = 1
    );
    
    SELECT * FROM odd_employee;
    
    INSERT INTO employee VALUES (0005, 'Alla', 'IT');
    
    SELECT * FROM odd_employee;
    

    Output:

    CREATE TABLE
    INSERT 0 1
    INSERT 0 1
    INSERT 0 1
    CREATE VIEW
     empid | name  
    -------+-------
         1 | Clark
         3 | Ava
    (2 rows)
    
    INSERT 0 1
     empid | name  
    -------+-------
         1 | Clark
         3 | Ava
         5 | Alla
    (3 rows)
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search