Обсуждение: 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
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
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 > -----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/ >
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 //