1

I want comma separated unique from_date in one row.

So I am using distinct() function in TO_TIMESTAMP() but getting errors.

SELECT string_agg(TO_CHAR(TO_TIMESTAMP(distinct(from_date) / 1000), 'DD-MM-YYYY'), ',')
FROM trn_day_bookkeeping_income_expense 
GROUP BY from_date,enterprise_id having enterprise_id = 5134650;

I want output like:

01-10-2017,01-11-2017,01-12-2017

But I am getting errors like:

ERROR:  DISTINCT specified, but to_timestamp is not an aggregate function
LINE 1: SELECT string_agg(TO_CHAR(TO_TIMESTAMP(distinct(from_date) /...**
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Rahul
  • 111
  • 3
  • 13
  • what data type is `from_date` Why do you use `to_timestamp()` on it? Why aren't you storing timestamp values in a `timestamp` column? –  Sep 04 '19 at 09:41
  • data type is bigint. to_timestamp() is using for convert bigint to date with format – Rahul Sep 04 '19 at 09:46
  • 1
    Why aren't you storing timestamp values in a `timestamp` column? then you wouldn't need to call `to_timestamp()` everytime you want a proper timestamp –  Sep 04 '19 at 09:47
  • please suggest me how to store timestamp values in a timestamp – Rahul Sep 04 '19 at 09:56
  • Use the data type `timestamp` –  Sep 04 '19 at 10:01

2 Answers2

3

DISTINCT is neither a function nor an operator but an SQL construct or syntax element. Can be added as leading keyword to the whole SELECT list or within most aggregate functions.

Add it to the SELECT list (consisting of a single column in your case) in a subselect where you can also cheaply add ORDER BY. Should yield best performance:

SELECT string_agg(to_char(the_date, 'DD-MM-YYYY'), ',') AS the_dates
FROM  (
   SELECT DISTINCT to_timestamp(from_date / 1000)::date AS the_date
   FROM   trn_day_bookkeeping_income_expense 
   WHERE  enterprise_id = 5134650
   ORDER  BY the_date   -- assuming this is the order you want 
   ) sub;

First generate dates (multiple distinct values may result in the same date!).
Then the DISTINCT step (or GROUP BY).
(While being at it, optionally add ORDER BY.)
Finally aggregate.

An index on (enterprise_id) or better (enterprise_id, from_date) should greatly improve performance.

Ideally, timestamps are stored as type timestamp to begin with. Or timestamptz. See:

DISTINCT ON is a Postgres-specific extension of standard SQL DISTINCT functionality. See:

Alternatively, you could also add DISTINCT(and ORDER BY) to the aggregate function string_agg() directly:

SELECT string_agg(DISTINCT to_char(to_timestamp(from_date / 1000), 'DD-MM-YYYY'), ',' ORDER BY to_char(to_timestamp(from_date / 1000), 'DD-MM-YYYY')) AS the_dates
FROM   trn_day_bookkeeping_income_expense 
WHERE  enterprise_id = 5134650

But that would be ugly, hard to read and maintain, and more expensive. (Test with EXPLAIN ANALYZE).

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

distinct is not a function, it's an operator applied to either all columns in the select list, or a parameter to an aggregate function.

you probably want this: 

SELECT string_agg(distinct TO_CHAR(TO_TIMESTAMP(from_date / 1000), 'DD-MM-YYYY'), ',') 
from trn_day_bookkeeping_income_expense 
group by from_date,enterprise_id 
having enterprise_id = 5134650