count_nulls(VARIADIC "any")

On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja <marko@joh.to> wrote:

Will finish this up for the next CF, unless someone wants to tell me how
stupid this idea is before that.

I'm kind of puzzled what kind of schema would need this.

--
greg

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Greg Stark wrote:

On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja <marko@joh.to> wrote:

Will finish this up for the next CF, unless someone wants to tell me how
stupid this idea is before that.

I'm kind of puzzled what kind of schema would need this.

I've seen cases where you want some entity to be of either of some
number of types. In those cases you have nullable FKs in separate
columns, and only one of them can be non null.

The name count_nulls() suggest an aggregate function to me, though.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi

2015-08-12 19:18 GMT+02:00 Marko Tiikkaja <marko@joh.to>:

Hi,

I'd like to suggest $SUBJECT for inclusion in Postgres 9.6. I'm sure
everyone would've found it useful at some point in their lives, and the
fact that it can't be properly implemented in any language other than C I
think speaks for the fact that we as a project should provide it.

A quick and dirty proof of concept (patch attached):

=# select count_nulls(null::int, null::text, 17, 'bar');
count_nulls
-------------
2
(1 row)

Its natural habitat would be CHECK constraints, e.g:

CHECK (count_nulls(a,b,c) IN (0, 3))

Will finish this up for the next CF, unless someone wants to tell me how
stupid this idea is before that.

It is not bad idea

+1

Pavel

On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

The name count_nulls() suggest an aggregate function to me, though.

I thought the same.

--
Peter Geoghegan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2015-08-12 7:23 PM, Greg Stark wrote:

On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja <marko@joh.to> wrote:

Will finish this up for the next CF, unless someone wants to tell me how
stupid this idea is before that.

I'm kind of puzzled what kind of schema would need this.

The first example I could find from our schema was specifying the URL
for a Remote Procedure Call. You can either specify a single request
URI, or you can specify the pieces: protocol, host, port, path. So the
constraints look roughly like this:

CHECK ((fulluri IS NULL) <> (protocol IS NULL)),
CHECK ((protocol IS NULL) = ALL(ARRAY[host IS NULL, port IS NULL,
path IS NULL]))

Obviously the second one would be much prettier with count_nulls().

The other example is an OOP inheritance-like schema where an object
could be one of any X number of types. You could write that:

CHECK ((a IS NULL)::int + (b IS NULL)::int + (c IS NULL)::int) = 1)

or just:

CHECK (count_nulls(a,b,c) = 1)

The first example could be redesigned with three tables, but that seems
like a cure worse than the disease.

.m

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-08-12 19:32 GMT+02:00 Peter Geoghegan <pg@heroku.com>:

On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

The name count_nulls() suggest an aggregate function to me, though.

I thought the same.

maybe nulls_count ?

we have regr_count already

Regards

Pavel

On 2015-08-12 7:35 PM, Pavel Stehule wrote:

maybe nulls_count ?

we have regr_count already

But that's an aggregate as well..

.m

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-08-12 19:37 GMT+02:00 Marko Tiikkaja <marko@joh.to>:

On 2015-08-12 7:35 PM, Pavel Stehule wrote:

maybe nulls_count ?

we have regr_count already

But that's an aggregate as well..

my mistake

Pavel

Peter Geoghegan <pg@heroku.com> writes:

On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

The name count_nulls() suggest an aggregate function to me, though.

I thought the same.

Ditto. I'd be fine with this if we can come up with a name that
doesn't sound like an aggregate. The best I can do offhand is
"number_of_nulls()", which doesn't seem very pretty.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Aug 12, 2015 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Geoghegan <pg@heroku.com> writes:

On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

The name count_nulls() suggest an aggregate function to me, though.

I thought the same.

Ditto. I'd be fine with this if we can come up with a name that
doesn't sound like an aggregate. The best I can do offhand is
"number_of_nulls()", which doesn't seem very pretty.

nulls_in(a, b, c) IN (0, 3) - yes the repetition is not great...

David J.
​

On Thu, Aug 13, 2015 at 2:19 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wed, Aug 12, 2015 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Geoghegan <pg@heroku.com> writes:

On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

The name count_nulls() suggest an aggregate function to me, though.

I thought the same.

Ditto. I'd be fine with this if we can come up with a name that
doesn't sound like an aggregate. The best I can do offhand is
"number_of_nulls()", which doesn't seem very pretty.

nulls_in(a, b, c) IN (0, 3) - yes the repetition is not great...

How about these:

nulls_rank() (the analogy being 0 <= "rank" <= "set size")
nnulls()

or just

nulls() (this one might be a bit confusing due to existing NULLS LAST/FIRST
syntax, though)

--
Alex

On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

nnulls()

I think I'd prefer num_nulls() over that.

.m

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-08-13 9:21 GMT+02:00 Marko Tiikkaja <marko@joh.to>:

On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

nnulls()

I think I'd prefer num_nulls() over that.

can be

what about similar twin function num_nonulls()?

Pavel

On Thu, Aug 13, 2015 at 12:55 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-08-13 9:21 GMT+02:00 Marko Tiikkaja <marko@joh.to>:

On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

nnulls()

I think I'd prefer num_nulls() over that.

can be

what about similar twin function num_nonulls()?

+1

On Thu, Aug 13, 2015 at 9:25 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-08-13 9:21 GMT+02:00 Marko Tiikkaja <marko@joh.to>:

On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

nnulls()

I think I'd prefer num_nulls() over that.

can be

what about similar twin function num_nonulls()?

Yes. But I'm can't see any precedent for naming it like num_*... And if
anything, should it be num_nonnulls() then?

2015-08-13 9:47 GMT+02:00 Shulgin, Oleksandr <oleksandr.shulgin@zalando.de>:

On Thu, Aug 13, 2015 at 9:25 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-08-13 9:21 GMT+02:00 Marko Tiikkaja <marko@joh.to>:

On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

nnulls()

I think I'd prefer num_nulls() over that.

can be

what about similar twin function num_nonulls()?

Yes. But I'm can't see any precedent for naming it like num_*... And if
anything, should it be num_nonnulls() then?

it is detail - depends on final naming convention.

Hello,

Here's a patch implementing this under the name num_nulls(). For
January's CF, of course.

.m

Marko Tiikkaja <marko@joh.to> writes:

Here's a patch implementing this under the name num_nulls(). For
January's CF, of course.

What's this do that "count(*) - count(x)" doesn't?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2015-11-21 06:06, Tom Lane wrote:

Marko Tiikkaja <marko@joh.to> writes:

Here's a patch implementing this under the name num_nulls(). For
January's CF, of course.

What's this do that "count(*) - count(x)" doesn't?

This is sort of a lateral version of count(x); the input is a list of
expressions rather than an expression executed over a bunch of input rows.

.m

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers