Thread: Alias "all fields"?

Alias "all fields"?

From
Franz.Rasper@izb.de
Date:
Hmm

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

It sounds like a bad table design,
because i think you need an field "f_year" and "value_of_f_year" then
there would be entries like
f_year;value_of_f_year
1970 'NULL'
1970 dfgsd
1971 'NULL'
1971 ....

where f_year IS NOT NULL and value_of_f_year IS NOT NULL

Greetings,

-Franz

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Stefan Schwarzer
Gesendet: Donnerstag, 6. September 2007 13:43
An: pgsql-general@postgresql.org
Betreff: [SPAM] [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: Alias "all fields"?

From
Stefan Schwarzer
Date:
>>        SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL
>> AND .... y2005 NOT NULL
>
> It sounds like a bad table design,
> because i think you need an field "f_year" and "value_of_f_year" then
> there would be entries like
> f_year;value_of_f_year
> 1970 'NULL'
> 1970 dfgsd
> 1971 'NULL'
> 1971 ....
>
> where f_year IS NOT NULL and value_of_f_year IS NOT NULL

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?

>
> Greetings,
>
> -Franz
>
> -----Ursprüngliche Nachricht-----
> Von: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Stefan
> Schwarzer
> Gesendet: Donnerstag, 6. September 2007 13:43
> An: pgsql-general@postgresql.org
> Betreff: [SPAM] [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/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


Re: Alias "all fields"?

From
Franz.Rasper@izb.de
Date:
>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







Re: Alias "all fields"?

From
Tino Wildenhain
Date:
Stefan Schwarzer schrieb:
>
>>>        SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL
>>> AND .... y2005 NOT NULL
>>
>> It sounds like a bad table design,
>> because i think you need an field "f_year" and "value_of_f_year" then
>> there would be entries like
>> f_year;value_of_f_year
>> 1970 'NULL'
>> 1970 dfgsd
>> 1971 'NULL'
>> 1971 ....
>>
>> where f_year IS NOT NULL and value_of_f_year IS NOT NULL
>
> 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?

Make the table:

id | year | value
---+------+------
1  | 1970 |    23
1  | 1971 |    25
1  | 1972 |    28
  ...
2  | 1972 |     5
3  | 1971 |    94
3  | 1972 |   102


primary key: (id,year)
value not null

and be ready.

the import/update reasons are pretty easily solved
that way too.

Regards
Tino


Re: Alias "all fields"?

From
Franz.Rasper@izb.de
Date:
> Make the table:

> id | year | value
> ---+------+------
> 1  | 1970 |    23
> 1  | 1971 |    25
> 1  | 1972 |    28
>  ...
> 2  | 1972 |     5
> 3  | 1971 |    94
> 3  | 1972 |   102

> primary key: (id,year)
> value not null
> and be ready.
>the import/update reasons are pretty easily solved
>that way too.

then your primary key  will consists of two fields.
I prefer the primary keys with one field only.

-Franz


Re: Alias "all fields"?

From
Tino Wildenhain
Date:
Franz.Rasper@izb.de schrieb:
>> Make the table:
>
>> id | year | value
>> ---+------+------
>> 1  | 1970 |    23
>> 1  | 1971 |    25
>> 1  | 1972 |    28
>>  ...
>> 2  | 1972 |     5
>> 3  | 1971 |    94
>> 3  | 1972 |   102
>
>> primary key: (id,year)
>> value not null
>> and be ready.
>> the import/update reasons are pretty easily solved
>> that way too.
>
> then your primary key  will consists of two fields.
> I prefer the primary keys with one field only.

Maybe but this would not help you with this situation here :-)
I think this fittes best but I'm open to alternative approaches.

The wide-table of the original design has definitively much more
problems. And if id is independent from year (not really month or
so) it is usefull imho to have a compound primary key.

Alternatively you can skip the primary key part and just define:

id not null,
year not null,
value not null

unique (id,year)

Regards
Tino

Re: Alias "all fields"?

From
Alvaro Herrera
Date:
Franz.Rasper@izb.de wrote:
>
> > Make the table:
>
> > id | year | value
> > ---+------+------
> > 1  | 1970 |    23
> > 1  | 1971 |    25
> > 1  | 1972 |    28
> >  ...
> > 2  | 1972 |     5
> > 3  | 1971 |    94
> > 3  | 1972 |   102
>
> > primary key: (id,year)
> > value not null
> > and be ready.
> >the import/update reasons are pretty easily solved
> >that way too.
>
> then your primary key  will consists of two fields.
> I prefer the primary keys with one field only.

Is there any reason for that preference?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Alias "all fields"?

From
Stefan Schwarzer
Date:
Ouff.... thanks for all these replies.

A reason for this kind of design.... yeah, I guess these here:

a) not being a professional database designer
b) import through Excel exports... that is, the QC, harmonization and
aggregations are being done in Excel, and then the final result is
being exported into SQL
c) ease of maintenance (in some respect); table overview (we have
some 500 variables and 1500 tables). (I still find it easier to look
for gdp_capita, browse the table and watch out for a specific country/
year/value which I have to change or verify.

We hesitated in deciding which approach to take. But I couldn't
imagine having 1.000.000 or so entries in a single table and sort
them out into the variables/regions/countries for each query or
request...

But as I said, perhaps just the lack of knowing it better.

Stef

On Sep 6, 2007, at 5:27 PM, Alvaro Herrera wrote:

> Franz.Rasper@izb.de wrote:
>>
>>> Make the table:
>>
>>> id | year | value
>>> ---+------+------
>>> 1  | 1970 |    23
>>> 1  | 1971 |    25
>>> 1  | 1972 |    28
>>>  ...
>>> 2  | 1972 |     5
>>> 3  | 1971 |    94
>>> 3  | 1972 |   102
>>
>>> primary key: (id,year)
>>> value not null
>>> and be ready.
>>> the import/update reasons are pretty easily solved
>>> that way too.
>>
>> then your primary key  will consists of two fields.
>> I prefer the primary keys with one field only.
>
> Is there any reason for that preference?
>
> --
> Alvaro Herrera                                http://
> www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Column ordering

From
"Ashish Karalkar"
Date:
Hello All,

I am having a table with  5 columns.
I want to add another column by altering the table at 2nd position
constraint is that I can not drop and recreate the table as column ordering
is of importance.
Is there anyway to do so.

Thanks in advance.

With regards
Ashish...


Re: Column ordering

From
brian
Date:
Ashish Karalkar wrote:
> Hello All,
>
> I am having a table with  5 columns.
> I want to add another column by altering the table at 2nd position
> constraint is that I can not drop and recreate the table as column
> ordering is of importance.
> Is there anyway to do so.

Copy everything into a tmp table, drop the original, recreate it with
the new column in the correct position, and move the data back into it.

brian