PostgreSQL Question posted in
The official documentation can be found here.

How to get daily new row counts for past 1 year for all tables in PostgreSQL

I have database in PostgreSQL and I currently have a query that I found from Stackoverflow to get the list of tables with it’s current row count.

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))
SELECT table_schema,
       TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

However, I want to get the number of new row added daily for the past 1 year for all tables. Assuming all tables has this field created_at where it stores the time when the row added, what’s the SQL query to display that?

Tags:

2

Answers


  1. 0

    This is the statement you can use, but you must redefine your existing query

    select max(created_at) as created_at, count(1) as count from table

    Login or Signup to reply.
  2. 0

    If I get your question and it’s important to check daily pattern I would suggest:

    SELECT DATE(created_at),
       SUM(rows_n) AS rows
FROM tbl
WHERE DATE(created_at) >= CAST((CURRENT_DATE - interval '365' day) as DATE)
GROUP BY DATE(created_at)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search