Re: [HACKERS] generated columns

There are some unanswered questions with column grants too. Do we allow granting access to a calculated column which accesses columns the user doesn't have access to? If so then this is a suitable substitute for using updateable views to handle things like granting users access to things like

Re: [HACKERS] generated columns

So yes, distinguishing stored vs. not stored computed columns is useful, especially if the expression can refer to other columns of the same row, though not only then. Examples: -- useful only if stored (assuming these never get updated) inserted_at TIMESTAMP WITHOUT TIME ZONE AS

Re: [HACKERS] generated columns

On Mon, Oct 02, 2017 at 02:30:38PM -0400, Tom Lane wrote: > Nico Williams writes: > > On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote: > >> So for me, i'd rather default to compute on read, as long storing the > >> pre-computed value is an option when

Re: [HACKERS] generated columns

On Mon, Oct 02, 2017 at 02:30:38PM -0400, Tom Lane wrote: > Nico Williams writes: > > On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote: > >> So for me, i'd rather default to compute on read, as long storing the > >> pre-computed value is an option when

Re: [HACKERS] generated columns

Nico Williams writes: > On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote: >> So for me, i'd rather default to compute on read, as long storing the >> pre-computed value is an option when necessary. > Sure, I agree. I was just wondering whether there might

Re: [HACKERS] generated columns

On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote: > I know that for my use-cases, having both options available would be very > appreciated. The vast majority of the computed columns I would use in my > database would be okay to compute on read. But there are for sure some >

Re: [HACKERS] generated columns

I know that for my use-cases, having both options available would be very appreciated. The vast majority of the computed columns I would use in my database would be okay to compute on read. But there are for sure some which would be performance prohibitive to have compute on read, so i'd rather

Re: [HACKERS] generated columns

On Thu, Aug 31, 2017 at 12:16:43AM -0400, Peter Eisentraut wrote: > In previous discussions, it has often been a source of confusion whether > these generated columns are supposed to be computed on insert/update and > stored, or computed when read. The SQL standard is not explicit, but > appears

Re: [HACKERS] generated columns

> On 12 Sep 2017, at 21:35, Jaime Casanova > wrote: > > On 10 September 2017 at 00:08, Jaime Casanova > wrote: >> >> During my own tests, though, i found some problems: > > a few more tests: > > create table t1 ( > id serial, >

Re: [HACKERS] generated columns

On Wed, Sep 13, 2017 at 10:09:37AM +0200, Andreas Karlsson wrote: > On 09/13/2017 04:04 AM, Simon Riggs wrote: > >On 31 August 2017 at 05:16, Peter Eisentraut > > wrote: > >>- index support (and related constraint support) > > > >Presumably you can't index a

Re: [HACKERS] generated columns

On Tue, Sep 12, 2017 at 10:04 PM, Simon Riggs wrote: > I think an option to decide whether the default is STORED or VIRTUAL > would be useful. That seems like it could be a bit of a foot-gun. For example, an extension author who uses generated columns will have to be

Re: [HACKERS] generated columns

On 13 September 2017 at 09:09, Andreas Karlsson wrote: > On 09/13/2017 04:04 AM, Simon Riggs wrote: >> >> On 31 August 2017 at 05:16, Peter Eisentraut >> wrote: >>> >>> - index support (and related constraint support) >> >> >> Presumably you

Re: [HACKERS] generated columns

On 09/13/2017 04:04 AM, Simon Riggs wrote: On 31 August 2017 at 05:16, Peter Eisentraut wrote: - index support (and related constraint support) Presumably you can't index a VIRTUAL column. Or at least I don't think its worth spending time trying to make it

Re: [HACKERS] generated columns

On 31 August 2017 at 05:16, Peter Eisentraut wrote: > Here is another attempt to implement generated columns. This is a > well-known SQL-standard feature, also available for instance in DB2, > MySQL, Oracle. A quick example: > > CREATE TABLE t1 ( > ..., >

Re: [HACKERS] generated columns

> On Sep 12, 2017, at 12:35 PM, Jaime Casanova > wrote: > > also is interesting that in triggers, both before and after, the > column has a null. that seems reasonable in a before trigger but not > in an after trigger Why is a NULL reasonable for before

Re: [HACKERS] generated columns

On 10 September 2017 at 00:08, Jaime Casanova wrote: > > During my own tests, though, i found some problems: > a few more tests: create table t1 ( id serial, height_cm int, height_in int generated always as (height_cm * 10) ) ; """ postgres=# alter table t1

Re: [HACKERS] generated columns

On 30 August 2017 at 23:16, Peter Eisentraut wrote: > Here is another attempt to implement generated columns. This is a > well-known SQL-standard feature, also available for instance in DB2, > MySQL, Oracle. > [...] > > In previous discussions, it has often been

Re: [HACKERS] generated columns

On 31 August 2017 at 05:16, Peter Eisentraut wrote: > Here is another attempt to implement generated columns. This is a > well-known SQL-standard feature, also available for instance in DB2, > MySQL, Oracle. A quick example: > > CREATE TABLE t1 ( > ..., >

[HACKERS] generated columns

Here is another attempt to implement generated columns. This is a well-known SQL-standard feature, also available for instance in DB2, MySQL, Oracle. A quick example: CREATE TABLE t1 ( ..., height_cm numeric, height_in numeric GENERATED ALWAYS AS (height_cm * 2.54) ); (This is