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.
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 |