Alias "all fields"?

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

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

My table design is - due to some import/update reasons - surely not
the best one, but pretty simple:

id y1970 y1971 y1972 ......
1 23 25 28
2 NULL NULL 5
3 NULL 94 102

What do you think?

Normally i use perl with DBD/DBI to import data into databases and it is
quite
easy to modify raw data with perl and import them.

I would prefer another table design (your import will then not be so simple,
but your "selects" will get "normally" faster and easier).

Table:
id_Table;id_row;t_year;t_year_value
1;1;y1970,23
2;1;y1971;25
...
....
20;3;y1970;NULL
21;3;y1971;94

You will need only id_row if you need all tuples in the same line of your
original line.

I yould do it so, if you have more then 3 or 4 columns of y???? .

Greetings,

-Franz

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 //

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

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

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 //

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

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

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 //