distinct on extract returns composite type

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: distinct on extract returns composite type
Date: 2019-09-29 09:46:31
Message-ID: CA+t6e1=FjpvAOwGXveustdC11VbSBrZ2_pVKGKaVFqAvVJh+QQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hey,

I'm working on PG12.
I have the following table :
\d dates_table
Table "public. dates_table "
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+-----------------------------------------------
id | integer | | not null | nextval('
dates_table_seq'::regclass)
end_time | date | | |

I tried to get all the quarters of the dates(and the years) in order to
create a range partition by quarters. I used the following query :
select distinct(extract(year from end_time),extract(quarter from end_time))
from dates_table where end_time is not null;
row
----------
(2017,3)
(2017,4)
(2018,1)
(2018,2)
(2018,3)
(2018,4)
(2019,1)
(2019,2)
(2019,3)
(9 rows)

I'm keep getting composite type (row) instead of two columns. Is there any
sql way to convert the row type into two columns ? I want to get the first
and last dates of each quarter with those columns and with this composite
type I failed doing it

Thanks.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Félix GERZAGUET 2019-09-29 10:34:30 Re: distinct on extract returns composite type
Previous Message Mariel Cherkassky 2019-09-29 06:05:15 Re: sequence depends on many tables