pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddistinct on extract returns composite type
6+ messages / 4 participants
[nested] [flat]
* distinct on extract returns composite type
@ 2019-09-29 09:46 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Mariel Cherkassky @ 2019-09-29 09:46 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: distinct on extract returns composite type
@ 2019-09-29 10:34 Félix GERZAGUET <felix.gerzaguet@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Félix GERZAGUET @ 2019-09-29 10:34 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
>
This seems to work as you expect:
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
;
https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT
--
Félix
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: distinct on extract returns composite type
@ 2019-09-30 13:42 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Félix GERZAGUET <felix.gerzaguet@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Mariel Cherkassky @ 2019-09-30 13:42 UTC (permalink / raw)
To: Félix GERZAGUET <felix.gerzaguet@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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 !
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: distinct on extract returns composite type
@ 2019-09-30 13:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2019-09-30 13:48 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Félix GERZAGUET <felix.gerzaguet@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: distinct on extract returns composite type
@ 2019-09-30 13:51 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Mariel Cherkassky @ 2019-09-30 13:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Félix GERZAGUET <felix.gerzaguet@gmail.com>; pgsql-performance@lists.postgresql.org
Understood, thanks for explanation Tom!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: distinct on extract returns composite type
@ 2019-10-07 21:53 Michael Lewis <mlewis@entrata.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Lewis @ 2019-10-07 21:53 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Félix GERZAGUET <felix.gerzaguet@gmail.com>; pgsql-performance@lists.postgresql.org
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-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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-10-07 21:53 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-29 09:46 distinct on extract returns composite type Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-09-29 10:34 ` Félix GERZAGUET <felix.gerzaguet@gmail.com>
2019-09-30 13:42 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-09-30 13:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-09-30 13:51 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-10-07 21:53 ` Michael Lewis <mlewis@entrata.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox