0

I want to query a database which will select the schema_name,max(date_column),table_name from database in PostgreSQL.

is there any process from which we can achieve the above scenario?

Thanks.

Moushmi
  • 51
  • 9

1 Answers1

0

In Postgres you can use a variation of the "row count for all tables" approach:

select table_schema, table_name,
       (xpath( '/row/max/text()', 
               query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, '')
              )
       )[1]::text::int as max_value
from information_schema.columns
where table_schema = 'public' --<< adjust for your schema name(s)
  and column_name = 'date_column' --<< adjust for the real name of your column
  • Hi,I tried with the above query, I just replace the table_schema and column_name in where condition , I am getting below error: "ERROR: function format(unknown, information_schema.sql_identifier, information_schema.sql_identifier, information_schema.sql_identifier) does not exist LINE 3: query_to_xml(format('select max(%I) from %I.%... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts." – Moushmi Jan 30 '20 at 07:17
  • This works in Postgres. Which database product are you really using? –  Jan 30 '20 at 07:19
  • Yeah I am using postgre (greenplum) only – Moushmi Jan 31 '20 at 08:08
  • Greeplum or Postgres? Those are two very different database products. Does Greenplum support `format()` and `query_to_xml()`? Does it work if cast the paramters? `format('....', column_date::text, table_name::text, table_schema::text)` –  Jan 31 '20 at 08:14