Обсуждение: Alias "all fields"?
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/
>> 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
>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
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
> 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
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
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
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
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...
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