Thread: Alias "all fields"?

Alias "all fields"?

From
Stefan Schwarzer
Date:
Hi there,

I guess I am demanding too much.... But it would be cool to have some
kind of alias for "all fields".

What I mean is this here:

Instead of this:

        SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL
AND .... y2005 NOT NULL

I would like to have this:

       SELECT * FROM gdp WHERE all-fields NOT NULL

This is, because my tables have different - and a different number of
fields.

In principal, I actually just want to have the number of fields which
are NOT NULL...

Thanks for any advice.

Stef


Re: Alias "all fields"?

From
Richard Huxton
Date:
Stefan Schwarzer wrote:
>        SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL AND
> .... y2005 NOT NULL
>
> I would like to have this:
>
>       SELECT * FROM gdp WHERE all-fields NOT NULL

Well you can get closer:

SELECT * FROM gdp WHERE (y1970+y1971+...+y2005) IS NOT NULL;

This makes use of the fact that X+NULL = NULL

--
   Richard Huxton
   Archonet Ltd

Re: Alias "all fields"?

From
Tino Wildenhain
Date:
Stefan Schwarzer schrieb:
> Hi there,
>
> I guess I am demanding too much.... But it would be cool to have some
> kind of alias for "all fields".
>
> What I mean is this here:
>
> Instead of this:
>
>        SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL AND
> .... y2005 NOT NULL
>
> I would like to have this:
>
>       SELECT * FROM gdp WHERE all-fields NOT NULL
>
> This is, because my tables have different - and a different number of
> fields.
>
> In principal, I actually just want to have the number of fields which
> are NOT NULL...

Well, this seems more a matter of correct normalization.
If you organize your tables to have a column for the year,
you would just not have "empty" years to select. Problem solved.
Last not least you can easily count the existing years and
whatnot...

Regards
Tino

ANY (was: Re: Alias "all fields"?)

From
Alban Hertroys
Date:
Richard Huxton wrote:
> Well you can get closer:
>
> SELECT * FROM gdp WHERE (y1970+y1971+...+y2005) IS NOT NULL;
>
> This makes use of the fact that X+NULL = NULL

I was going to suggest

SELECT * FROM gdp WHERE NULL NOT IN (y1970, y1971, y1972);

But that doesn't work.
So I tried using ANY with IS NOT NULL, as according to the documentation
"IN is equivalent to = ANY"
(
http://www.postgresql.org/docs/8.2/static/functions-subquery.html#AEN13967).

So maybe:

SELECT * FROM gdp WHERE NULL IS NOT ANY(y1970, y1971, y1972);

I get nothing but syntax errors... I remember trying to use ANY in the
past and never got it to work...

So, how do you use ANY with a fixed set of values (the way IN can)? And
can this be used to solve the OP's problem without using tricks like
summing NULL values?

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Re: ANY

From
Richard Huxton
Date:
Alban Hertroys wrote:
>
> SELECT * FROM gdp WHERE NULL IS NOT ANY(y1970, y1971, y1972);
>
> I get nothing but syntax errors... I remember trying to use ANY in the
> past and never got it to work...
>
> So, how do you use ANY with a fixed set of values (the way IN can)? And
> can this be used to solve the OP's problem without using tricks like
> summing NULL values?

AFAIK there are two variants of ANY()
   1. sets
   2. arrays

So you should be able to do:
   ... WHERE x = ANY( ARRAY[a, b, c] )

Of course, where x is NULL, I don't think that'll help you (x = NULL
returns NULL). Oh, and you couldn't have nulls in arrays before 8.2 iirc.

--
   Richard Huxton
   Archonet Ltd

Re: Alias "all fields"?

From
Tom Lane
Date:
Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> writes:
> Instead of this:
>         SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL
> AND .... y2005 NOT NULL
> I would like to have this:
>        SELECT * FROM gdp WHERE all-fields NOT NULL

This idea seems rather pointless for any operation other than
null-testing, since nothing else would apply uniformly to all data
types.  For null-testing you can use row null tests:

regression=# select * from int8_tbl i;
        q1        |        q2
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
               22 |
                  |
(7 rows)

regression=# select * from int8_tbl i where row(i.*) is not null;
        q1        |        q2
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 rows)

regression=#

Note: this only works the way you want in 8.2 and up; earlier versions
thought that "row is not null" meant that *any* field is not null,
rather than *all*.

            regards, tom lane

Re: ANY

From
Alban Hertroys
Date:
Richard Huxton wrote:
> Alban Hertroys wrote:
>>
>> SELECT * FROM gdp WHERE NULL IS NOT ANY(y1970, y1971, y1972);

> AFAIK there are two variants of ANY()
>   1. sets
>   2. arrays
>
> So you should be able to do:
>   ... WHERE x = ANY( ARRAY[a, b, c] )

But then the documentation isn't entirely correct. It suggests that it
works similar to IN, but it doesn't.

> Of course, where x is NULL, I don't think that'll help you (x = NULL
> returns NULL). Oh, and you couldn't have nulls in arrays before 8.2 iirc.

Well, as ANY allows the use of an operator, I figured you could use IS
NULL with it.


--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Re: Alias "all fields"?

From
"George Pavlov"
Date:
as everyone has pointed out it does not seem like the best table design
and querying for these fields as normal course of business does not seem
that great, but if you wanted to audit tables like these once in a while
you could easily do it using your favorite scripting language or SQL
itself. here's a simple psql example to get you started:

test=> \t
Tuples only is off.
test=> \a
Output format is aligned.
test=> \t
Showing only tuples.
test=> \a
Output format is unaligned.
test=> \o /tmp/null-test.sql
test=> select 'select ''' || upper(table_name) || '''|| '' not null rows
count: '' || count(*) from '||table_name||' where ' ||
array_to_string(array(select column_name::text from
information_schema.columns c where c.table_name = t.table_name),' is not
null and ') || ' is not null;' from information_schema.tables t where
table_schema = 'test' and table_name like 'emp%';
test=> \o
test=> \i /tmp/null-test.sql
EMPLOYEE not null rows count: 89
EMPLOYEE_ROLE not null rows count: 11
EMPLOYEE_ROLE_PRIVILEGE not null rows count: 266
EMPLOYEE_PRIVILEGE not null rows count: 53
EMPLOYEE_PRIVILEGE_GROUP not null rows count: 9



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Stefan Schwarzer
> Sent: Thursday, September 06, 2007 4:43 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Alias "all fields"?
>
> Hi there,
>
> I guess I am demanding too much.... But it would be cool to
> have some
> kind of alias for "all fields".
>
> What I mean is this here:
>
> Instead of this:
>
>         SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL
> AND .... y2005 NOT NULL
>
> I would like to have this:
>
>        SELECT * FROM gdp WHERE all-fields NOT NULL
>
> This is, because my tables have different - and a different
> number of
> fields.
>
> In principal, I actually just want to have the number of
> fields which
> are NOT NULL...
>
> Thanks for any advice.
>
> Stef
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>

Re: ANY

From
Tom Lane
Date:
Alban Hertroys <alban@magproductions.nl> writes:
> Richard Huxton wrote:
>> AFAIK there are two variants of ANY()
>> 1. sets
>> 2. arrays
>>
>> So you should be able to do:
>> ... WHERE x = ANY( ARRAY[a, b, c] )

> But then the documentation isn't entirely correct. It suggests that it
> works similar to IN, but it doesn't.

The subquery variants (section 9.19) do work the same.  There is nothing
on the other page (9.20) making such a comparison.

            regards, tom lane

Re: ANY

From
Alban Hertroys
Date:
Tom Lane wrote:
> Alban Hertroys <alban@magproductions.nl> writes:
>> Richard Huxton wrote:
>>> AFAIK there are two variants of ANY()
>>> 1. sets
>>> 2. arrays
>>>
>>> So you should be able to do:
>>> ... WHERE x = ANY( ARRAY[a, b, c] )
>
>> But then the documentation isn't entirely correct. It suggests that it
>> works similar to IN, but it doesn't.
>
> The subquery variants (section 9.19) do work the same.  There is nothing
> on the other page (9.20) making such a comparison.

Oh right, there are 2 sections about those expressions... Never noticed
that there was "9.16. Subquery Expressions" and "9.17. Row and Array
Comparisons" listing the same expressions.

(Apparently there are 2 more sections before it in your documentation)

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //