User-defined function with anyrange[]

Hello, I want to make an aggregate function range_agg(anyrange) that returns anyrange[]. But when I try to define it, Postgres tells me it doesn't know what an anyrange[] is. I get this error: ERROR: type anyrange[] does not exist I also tried taking an anyrange and returning an anyarray,

Re: FK v.s unique indexes

On Thu, Jul 5, 2018 at 2:36 PM, Rafal Pietrak wrote: > > > W dniu 05.07.2018 o 23:04, David G. Johnston pisze: > > On Thu, Jul 5, 2018 at 1:45 PM, Rafal Pietrak > >wrote: > > > > I was thinking, that when "add constraint" cannot choose appropriate > > index, may

Re: FK v.s unique indexes

W dniu 05.07.2018 o 23:04, David G. Johnston pisze: > On Thu, Jul 5, 2018 at 1:45 PM, Rafal Pietrak >wrote: > > I was thinking, that when "add constraint" cannot choose appropriate > index, may be some explicit help (like ... using ;) would be > due. > >

Re: FK v.s unique indexes

On Thu, Jul 5, 2018 at 1:45 PM, Rafal Pietrak wrote: > I was thinking, that when "add constraint" cannot choose appropriate > index, may be some explicit help (like ... using ;) would be > due. > ​Basically all the FK trigger does is: SELECT EXISTS(SELECT 1 FROM pk_table WHERE pk_col1 = val1

Re: FK v.s unique indexes

W dniu 05.07.2018 o 10:11, Rob Sargent pisze: [---] >> >> Pls consider in real life: load (a person), can have either a (a kind of >> brest cancer); or b (a kind of prostrate) - this is only a cooked >> example attemping to illustrate, that one may need to put additional >>

Re: FK v.s unique indexes

W dniu 05.07.2018 o 15:18, David G. Johnston pisze: > On Thursday, July 5, 2018, Rafal Pietrak > wrote: > > W dniu 04.07.2018 o 00:55, David G. Johnston pisze: > > On Tuesday, July 3, 2018, Rafal Pietrak > >

Re: How to watch for schema changes

On Thu, Jul 5, 2018 at 10:07 AM, Igor Korot wrote: > On Thu, Jul 5, 2018 at 11:49 AM, David G. Johnston > wrote: > > > You can, and depending on how often you intend to execute said code, it > is > > probably the better way. It also requires pl/pgsql while CREATE OR > REPLACE > > "just works"

Re: How to watch for schema changes

On 07/05/2018 08:40 AM, Igor Korot wrote: Hi, David, On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston wrote: On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot wrote: I presume threre is a query which check for the function/trigger existence? Something like: IF NOT EXIST(SELECT * FROM ) CREATE

Re: As a table owner, can I grant "grant" ?

On Thu, Jul 5, 2018 at 9:58 AM, David Gauthier wrote: > Postgres 9.5.2 on linux > > Given that I am not superuser, but DO have createrole... > Can I grant some other role the ability to grant access to a table I > created ? > > For Example: I create a table called foo. As the creator/owner of

As a table owner, can I grant "grant" ?

Postgres 9.5.2 on linux Given that I am not superuser, but DO have createrole... Can I grant some other role the ability to grant access to a table I created ? For Example: I create a table called foo. As the creator/owner of this table, I seem to have the ability to grant

Re: How to watch for schema changes

On Thu, Jul 5, 2018 at 8:40 AM, Igor Korot wrote: > Hi, David, > > On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston > wrote: > > On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot wrote: > >> > >> > >> I presume threre is a query which check for the function/trigger > >> existence? Something like: >

Re: How to watch for schema changes

>As I said I'm writing the client application with libpq/ODBC. How will I get >the results? Igor, You DO NOT need libpq/ODBC . Just use the count option of grep (-c). If it is greater than zero, then send a mail to yourself and/or attach the log for review. -- *Melvin Davidson* *Maj. Database &

Re: Split daterange into sub periods

Hi select ($$[$$|| to_char(min(n.dato),'-MM-DD') || $$,$$ || to_char(max(n.dato),'-MM-DD') || $$]$$)::daterange, daterange(min(n.dato)::date,max(n.dato)::date) from ( select u.dato,anterior,(u.dato-anterior)::interval,sum(case when anterior is null or (u.dato -anterior)::interval='1

Re: How to watch for schema changes

Hi, David, On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston wrote: > On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot wrote: >> >> >> I presume threre is a query which check for the function/trigger >> existence? Something like: >> >> IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION; > >

Re: Split daterange into sub periods

On 05.07.2018 15:49, hmidi slim wrote: Hi, I'm looking for splitting a daterange into many subperiods following this example: Base Date: [2018-01-01, 2018-01-31] overlapped_periods: 1- [ 2018-01-04, 2018-01-06] 2- [ 2018-01-09, 2018-01-12] 3- [ 2018-01-18, 2018-01-19] I try to get such a

Re: How to watch for schema changes

Hi, Melvin, On Tue, Jul 3, 2018 at 6:48 PM, Melvin Davidson wrote: > >>I'm writing a client in C++ with libpq. So I will have to do a lot of >> polling . > Can't you just run a cron job? And what? As I said I'm writing the client application with libpq/ODBC. How will I get the results? Thank

Re: Cloning schemas

Melvin, thank you once again ! Yes and this is working like a charm, I love your function and file ! Best, Jacek czw., 5 lip 2018 o 16:53 Melvin Davidson napisał(a): > > > > On Thu, Jul 5, 2018 at 10:38 AM, Łukasz Jarych wrote: > >> You gave me working example. >> >> the function from here

Re: Cloning schemas

On Thu, Jul 5, 2018 at 10:38 AM, Łukasz Jarych wrote: > You gave me working example. > > the function from here is not working: > > https://www.postgresql.org/message-id/CANu8FiyJtt-0q% > 3DbkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA%40mail.gmail.com > > Best, > Jacek > > czw., 5 lip 2018 o 16:02

Re: Split daterange into sub periods

On Thu, Jul 5, 2018 at 4:16 PM, hmidi slim wrote: > In fact I'm trying to split a period in sub periods. Following this example > : > If I have a period =[2018-01-01, 2018-01-31] and two other periods > [2018-01-04, 2018-01-06] and [2018-01-08, 2018-01-08]. > If I split the base period

Re: Split daterange into sub periods

In fact I'm trying to split a period in sub periods. Following this example : If I have a period =[2018-01-01, 2018-01-31] and two other periods [2018-01-04, 2018-01-06] and [2018-01-08, 2018-01-08]. If I split the base period '[2018-01-01, 2018-01-31]' by the other two periods '[2018-01-04,

Split daterange into sub periods

On Thursday, July 5, 2018, hmidi slim wrote: > > I got this error: > > > > *ERROR: result of range difference would not be contiguous* > > Is there any operators to make the split of daterang > > To refine what Adrian said, operators cannot return a setof result so this is basically impossible.

Re: Split daterange into sub periods

On 07/05/2018 06:49 AM, hmidi slim wrote: Hi, I'm looking for splitting a daterange into many subperiods following this example: Base Date: [2018-01-01, 2018-01-31] overlapped_periods: 1- [ 2018-01-04, 2018-01-06] 2- [ 2018-01-09, 2018-01-12] 3- [ 2018-01-18, 2018-01-19] Overlapping what?

Re: Cloning schemas

On Thu, Jul 5, 2018 at 1:49 AM, Łukasz Jarych wrote: > From link function is not working. > There is no " From link" in PostgreSQL, Would you please be more specific. Please provide a working example.

Split daterange into sub periods

Hi, I'm looking for splitting a daterange into many subperiods following this example: Base Date: [2018-01-01, 2018-01-31] overlapped_periods: 1- [ 2018-01-04, 2018-01-06] 2- [ 2018-01-09, 2018-01-12] 3- [ 2018-01-18, 2018-01-19] I try to get such a result: 1- [ 2018-01-01, 2018-01-03] 2- [

Re: FK v.s unique indexes

On Thursday, July 5, 2018, Rafal Pietrak wrote: > > W dniu 04.07.2018 o 00:55, David G. Johnston pisze: > > On Tuesday, July 3, 2018, Rafal Pietrak > > wrote: > > > > > > ERROR: there is no unique constraint matching given keys for > referenced > > table "test2"

Inefficient plan on 10.4

I have this table: Table "public.relation" Column |Type | Collation | Nullable | Default

Re: FK v.s unique indexes

David Rowley-3 wrote > I don't think there were any actual roadblocks, it was more of just > not enough time in the cycle to make it work due to a few technical > details that required extra effort to make work. > > Alvaro managed to simplify the problem and allow foreign keys to be > defined on

Re: FK v.s unique indexes

> On Jul 5, 2018, at 1:30 AM, Rafal Pietrak wrote: > > > > W dniu 04.07.2018 o 00:55, David G. Johnston pisze: >> On Tuesday, July 3, 2018, Rafal Pietrak > > wrote: >> >> >>ERROR: there is no unique constraint matching given keys for referenced >>table

Re: FK v.s unique indexes

W dniu 03.07.2018 o 11:23, David Rowley pisze: > On 3 July 2018 at 19:30, Rafal Pietrak wrote: [---] >> >> Why is this forbidden? > > I don't think there were any actual roadblocks, it was more of just > not enough time in the cycle to make it work due to a few technical > details