Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On 19.02.24 04:32, Darryl Green wrote: I note that in Postgresql 16 identity column handling in partitioned tables has been aligned to the view that the partitioned table as a whole is a single relation (and so a unique identity across partitions). This makes sense. The change that I think yo

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Mon, 19 Feb 2024 at 16:32, Darryl Green wrote: > 2) It would be nice to be able to specify the id as pk on the table being > partitioned (as it was in the non-partitioned definition of the table) once > to document and enforce that the partitions simply inherit the id pk. This > would seem o

Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Sunday, February 18, 2024, Darryl Green wrote: > > I note that in Postgresql 16 identity column handling in partitioned > tables has been aligned to the view that the partitioned table as a whole > is a single relation (and so a unique identity across partitions). This > makes sense. > Where

Partitioning, Identity and Uniqueness (given pg 16 changes)

Hi, I have a table that is capturing what is, basically, time series data. We use identity column as primary key. I'm considering the potential to partition it by the "source" of the time series (100s .. 1000s of sources) as often (but not always) use of this table is to get the time series for on

Re: Partitioning options

"Would probably look at a nested partitioning" I'm not the original poster, but I have a schema with nested (composite) partitions and I do run into some significant inefficiencies compared to flat partitions in various schema metadata operations (queries to get the list of tables, creating foreig

Re: Users and object privileges maintenance

On Sun, Feb 18, 2024, 11:35 Dominique Devienne wrote: > On Sun, Feb 18, 2024 at 4:33 PM Pavel Luzanov > wrote: > >> On 18.02.2024 17:40, Dominique Devienne wrote: >> >> Well, membership in a role mean you can "become that role", no? >> >> Since v16, no. There is now a grant option that controls

Re: Function inserting into tstzrange ? (syntax error at or near...)

On 2/18/24 10:40, Adrian Klaver wrote: On 2/18/24 10:30, Laura Smith wrote: There's not bespoke SQL syntax for constructing a range. You must use a function, something like VALUES(p_event_id, tstzrange(p_start_time,p_end_time,'[)')) ... Thanks all for your swift replies. Serves me right

Re: Function inserting into tstzrange ? (syntax error at or near...)

On 2/18/24 10:30, Laura Smith wrote: There's not bespoke SQL syntax for constructing a range. You must use a function, something like VALUES(p_event_id, tstzrange(p_start_time,p_end_time,'[)')) ... Thanks all for your swift replies. Serves me right for assuming I could use variable substi

Re: Users and object privileges maintenance

On Sun, Feb 18, 2024 at 4:33 PM Pavel Luzanov wrote: > On 18.02.2024 17:40, Dominique Devienne wrote: > > Well, membership in a role mean you can "become that role", no? Thus this > seems logical, > and not confusing to me, that you can act as the owner, since you SET ROLE > to the owner. > > The

Re: Function inserting into tstzrange ? (syntax error at or near...)

> > There's not bespoke SQL syntax for constructing a range. You must > use a function, something like > > VALUES(p_event_id, tstzrange(p_start_time,p_end_time,'[)')) ... Thanks all for your swift replies. Serves me right for assuming I could use variable substitution where text would norma

Re: Function inserting into tstzrange ? (syntax error at or near...)

Laura Smith writes: > I'm sure I'm doing something stupid here, but I think I've got the syntax > right ? > The error I'm seeing: > psql:event_session_funcs.sql:26: ERROR:  syntax error at or near "[" > LINE 11:         VALUES(p_event_id,[p_start_time,p_end_time)) RETURNI... There's not bespoke

Re: Function inserting into tstzrange ? (syntax error at or near...)

On 2/18/24 09:40, Laura Smith wrote: I'm sure I'm doing something stupid here, but I think I've got the syntax right ? The error I'm seeing: psql:event_session_funcs.sql:26: ERROR:  syntax error at or near "[" LINE 11:         VALUES(p_event_id,[p_start_time,p_end_time)) RETURNI... Two ways t

Re: Function inserting into tstzrange ? (syntax error at or near...)

On Sunday, February 18, 2024, Laura Smith < n5d9xq3ti233xiyif...@protonmail.ch> wrote: > I'm sure I'm doing something stupid here, but I think I've got the syntax > right ? > > The error I'm seeing: > psql:event_session_funcs.sql:26: ERROR: syntax error at or near "[" > LINE 11: VALUES(p_

Function inserting into tstzrange ? (syntax error at or near...)

I'm sure I'm doing something stupid here, but I think I've got the syntax right ? The error I'm seeing: psql:event_session_funcs.sql:26: ERROR:  syntax error at or near "[" LINE 11:         VALUES(p_event_id,[p_start_time,p_end_time)) RETURNI... The function: CREATE OR REPLACE FUNCTION new_even

Re: Users and object privileges maintenance

On 2/18/24 02:12, Dominique Devienne wrote: On Sat, Feb 17, 2024 at 10:50 PM Lok P > wrote: We were having past experience in Oracle and are newly getting moved to postgres database. [...] So I just wanted to understand if these grants and privileges for

Re: Users and object privileges maintenance

On 18.02.2024 17:40, Dominique Devienne wrote: Well, membership in a role mean you can "become that role", no? Thus this seems logical, and not confusing to me, that you can act as the owner, since you SET ROLE to the owner. They may acts as the owner even without explicit SET ROLE to the owne

Re: Users and object privileges maintenance

On Sun, Feb 18, 2024 at 3:27 PM Pavel Luzanov wrote: > On 18.02.2024 15:19, Dominique Devienne wrote: > > On Sun, Feb 18, 2024 at 12:30 PM Laurenz Albe > wrote: > > >> 2. In PostgreSQL, there is the important concept of ownership, which is >> not tied to the schema. >>The owner is the user w

Re: Users and object privileges maintenance

On 18.02.2024 15:19, Dominique Devienne wrote: On Sun, Feb 18, 2024 at 12:30 PM Laurenz Albe wrote: 2. In PostgreSQL, there is the important concept of ownership, which is not tied to the schema.    The owner is the user who created the object. Personally I find that confusing. I

Re: Users and object privileges maintenance

On Sun, Feb 18, 2024 at 12:30 PM Laurenz Albe wrote: > 1. Schemas and users are not tied together, they are orthoginal concepts. > Just like operating >system users and directories (and indeed all other databases). > Forgot about that one! OTOH, you could say PostgreSQL has tied USERs and RO

Re: Users and object privileges maintenance

On Sun, 2024-02-18 at 11:12 +0100, Dominique Devienne wrote: > On Sat, Feb 17, 2024 at 10:50 PM Lok P wrote: > > We were having past experience in Oracle and are newly getting moved to > > postgres database. [...] > > So I just wanted to understand if these grants and privileges for > > objects/

Re: Users and object privileges maintenance

On Sat, Feb 17, 2024 at 10:50 PM Lok P wrote: > We were having past experience in Oracle and are newly getting moved to > postgres database. [...] > So I just wanted to understand if these grants and privileges for > objects/users are given and maintained in a similar way in postgres database >