distinct on extract returns composite type

2019-09-29 Thread Mariel Cherkassky 
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.

Re: distinct on extract returns composite type

2019-09-29 Thread Félix GERZAGUET 
Hello,

On Sun, Sep 29, 2019 at 11:46 AM Mariel Cherkassky <
[email protected]> 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

Re: sequence depends on many tables

2019-09-29 Thread Tom Lane 
Mariel Cherkassky  writes:
>  seq_oid | classid | objid | objsubid | refclassid | refobjid | refobjsubid
> | deptype
> -+-+---+--++--+-+-
>17188 |1259 | 17188 |0 |   1259 |17190 |   1
> | a
>16566 |2604 | 16566 |0 |   1259 |17190 |   1
> | a
>16704 |2606 | 16704 |0 |   1259 |17190 |   3
> | a
>16704 |2606 | 16704 |0 |   1259 |17190 |   5
> | a
>16704 |2606 | 16704 |0 |   1259 |17190 |   4
> | a
> (5 rows)

Well, those entries with objid = 16566 and 16704 are not for sequences,
because the classid is wrong: 2604 is pg_attrdef, and 2606 is
pg_constraint, so the second row is for a default expression belonging
to table 17190 column 1, and the rest are for some kind of constraint
involving columns 3,4,5 (maybe a check constraint?)

In itself there's nothing wrong with these pg_depend entries, but it
is odd that you have different objects with identical OIDs.  Normally
I'd only expect that to be possible once the OID counter has wrapped
around ... but all these OIDs are small, which makes it seem unlikely
that you've consumed enough OIDs to reach wraparound.  Maybe you had
a system crash, or did something weird with backup/recovery, causing
the counter to get reset?

Anyway, the short answer here is that neither objid nor refobjid
should be considered sufficient to identify an object by themselves.
You need to also check classid (refclassid), because OIDs are only
guaranteed unique within a given system catalog.

regards, tom lane