Re: Table cannot be partiotioned using domain in argument

2018-09-11 Thread Márcio A . Sepp 
Hi and thanks for answer,
Nope.  The problem is suggested, if not exactly clearly explained,

by the error message: casting a literal to ddate isn't a guaranteed

fixed process. Wasn't clear enought to me.For example, suppose you created this table and then

did



alter domain ddate add check (value > '2020-01-01');



thereby rendering the partition bound values illegal for the domain.

What would you expect to happen then?Not sure. Maybe check constraint error when insert/update a row?!?! I think this should happen before new record to be routed to the correct partition (in this case there is no partition created to this interval).
We might at some point work out plausible semantics for this situation,

but it hasn't been done yet.



			regards, tom lane Ok to me. I'll use date instead of domain.Thank you so much Tom.

RES: Supartitions in PGSQL 10

2018-01-08 Thread Márcio A . Sepp 
> Kumar, Virendra schrieb am 08.01.2018 um 22:12:
> > Can you please let us know if Sub-partitions are supported in PGSQL
> > (declarative partitions) 10.1. If yes can it be list-list partitions.
> > We have a situation where a table is very big having around 2 billion
> > rows and is growing. We want to use partitions but not sure if
> > sub-partitions are available. The partition key we are looking for is
> > having around 8000 different values so it will be 8000 partitions and
> > I think that number is really too big number of partitions. For your
> > information RDBMS is not yet Postgres, we are evaluating it to see if
> > it can support.
> 
> Quote from the manual:
> https://www.postgresql.org/docs/current/static/ddl-
> partitioning.html#DDL-PARTITIONING-DECLARATIVE
> 
>  Partitions may themselves be defined as partitioned tables, using
> what is called sub-partitioning

Look:
https://www.depesz.com/2017/02/06/waiting-for-postgresql-10-implement-table-
partitioning/

help with generation_series in pg10

2018-01-08 Thread Márcio A . Sepp 

Hi,


In pg10 generation series doesn't work like in 9.5. 
Ex. in 9.5:
z=# select generate_series(1, 10), generate_series(1, 5);
 generate_series | generate_series
-+-
   1 |   1
   2 |   2
   3 |   3
   4 |   4
   5 |   5
   6 |   1
   7 |   2
   8 |   3
   9 |   4
  10 |   5
(10 registros)


so, in version 10 the same sql show different result set.
z=# select generate_series(1, 10), generate_series(1,5);
 generate_series | generate_series
-+-
   1 |   1
   2 |   2
   3 |   3
   4 |   4
   5 |   5
   6 |
   7 |
   8 |
   9 |
  10 |
(10 registros)


how can i have the same in pg10 as i have had in pg 9.x? 

I need it to date type to...  if possible. 


--
Att.
Márcio A. Sepp

RES: help with generation_series in pg10

2018-01-09 Thread Márcio A . Sepp 

> > how can i have the same in pg10 as i have had in pg 9.x?
> 
> Move the function call to the FROM clause:
> 
> select g, (g - 1) % 5 + 1 from generate_series(1, 10) g;

thank you. That is exact what i need.

 
> > I need it to date type to...  if possible.
> 
> There is a generate_series() variant that can return dates (more
> precisely, timestamp with time zone).  But what exactly would you like
> returned?

In the past i use querys like this to generate some timestamp field:
select generate_series (1, 10), generate_series('2018-01-01
10:00'::timestamp, '2018-01-02 10:00'::timestamp, '10 hours');


in some case I need to order the timestamp data field and in others I just
need an interval. So, in some moments an random data already help me and in
others it is necessary to have both fields aligned.


thanks

RES: help with generation_series in pg10

2018-01-10 Thread Márcio A . Sepp 
> >> > how can i have the same in pg10 as i have had in pg 9.x?
> >>
> >> Move the function call to the FROM clause:
> >>
> >> select g, (g - 1) % 5 + 1 from generate_series(1, 10) g;
> >
> > thank you. That is exact what i need.
> >
> >
> >> > I need it to date type to...  if possible.
> >>
> >> There is a generate_series() variant that can return dates (more
> >> precisely, timestamp with time zone).  But what exactly would you
> >> like returned?
> >
> > In the past i use querys like this to generate some timestamp field:
> > select generate_series (1, 10), generate_series('2018-01-01
> > 10:00'::timestamp, '2018-01-02 10:00'::timestamp, '10 hours');
> 
> With the old behavior you're lucky this ever worked at all.  Doing this
> kind of stuff, you want to write it with a single generate_series
> driver if you can or as a cross product:
> 
> select
>   a, b,c
> from generate_series(1,3) a
> cross join generate_series(1,4) b
> cross join generate_series(1,2) c;

Exactly what I need. Thank you so much!!!