Обсуждение: distinct on extract returns composite type
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 | | |
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)
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.
Hello,
On Sun, Sep 29, 2019 at 11:46 AM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
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
select distinct extract(year from end_time) as year, extract(quarter from end_time) quarter from generate_series
( '2017-09-01'::timestamp
, '2019-04-01'::timestamp
, '3 month'::interval) end_time
;
( '2017-09-01'::timestamp
, '2019-04-01'::timestamp
, '3 month'::interval) end_time
;
--
Félix
In my query I wrapped the columns with distinct : distinct (extract year... , extract quarter..).
In your query you didnt wrap the columns with distinct but you just mentioned it. I guess this is the difference, thanks !
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> In my query I wrapped the columns with distinct : distinct (extract year...
> , extract quarter..).
> In your query you didnt wrap the columns with distinct but you just
> mentioned it. I guess this is the difference, thanks !
Yeah. DISTINCT does not have an argument, it's just a keyword you
can stick in after SELECT. So what you had as the select's targetlist
was (expr,expr), which is read as an implicit row constructor, that
is the same as ROW(expr,expr). One of many arguably not-well-designed
things about SQL syntax :-(
regards, tom lane
Understood, thanks for explanation Tom!
As long as we are on the performance list and not general, it might be worth noting that partitioning should be defined directly on the data and not on a function result I believe. If you always do the extract year and extract quarter thing, it may work out just fine. But just a regular btree index on the date/timestamp/timestamptz field and partitions like the below might be much easier to work with.
MINVALUE to 2018-01-01 /* the top end is always exclusive so it gets referenced as top on this partition and start of the next partition */
2018-01-01 to 2018-04-01
2018-04-01 to 2018-07-01
2018-07-01 to 2018-10-01
2018-10-01 to 2019-01-01
2019-01-01 to 2019-04-012018-01-01 to 2018-04-01
2018-04-01 to 2018-07-01
2018-07-01 to 2018-10-01
2018-10-01 to 2019-01-01
2019-04-01 to 2019-07-01
2019-07-01 to 2019-10-01
2019-10-01 to 2020-01-01
2020-01-01 to MAXVALUE