[SQL] Query about table and catalog
Hi; I've got a table with several fields. Among others there are the fields 'soil1', 'soil2', and 'soil3' that are char type. A row can have an empty value in one of these fields, or the three fields can have valid values: cod_grass |suelo1|suelo2 | suelo3 ---+-+-+- 2590 | Xerosoles petrocalcicos| | 181 | Xerosoles calcicos | | 265 | Xerosoles petrocalcicos | | 593 | Zona urbana | | 1112 | Cambisoles calcicos | | 2 | Litosoles | | 3 | Xerosoles calcicos|| 4 | Litosoles | Rendsinas aridicas | 5 | Xerosoles petrocalcicos | | 6 | Litosoles | | 7 | Regosoles calcaricos | Xerosoles calcicos ... In other table I've got a catalog of posible soil types, assigning an integer value to each of possible soil types. tipo_suelo | cod_tipo_suelo -+ Arenosoles albicos | 1 Cambisoles calcicos | 2 Cambisoles eutricos | 3 Is it possible to prepare a query that show the contents of the table of soils and aditional columns after each of the soils fields, showing the corresponding numerical code for that soil, extracted from the catalog? I just know how to do this for one of the soils: SELECT cod_grass, suelo1,cod_tipo_suelo AS cod_suelo1 FROM suelos,suelos_catalogo WHERE suelo1=tipo_suelo ORDER BY cod_grass; But I would like to do the same for the three at a time. Thanks for your help and have a nice day. Javier . ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] union query doubt:
Hi; I've got a table with three fields: DATE, POINT, FLOW. The POINT field can have values among 1 and 6. So, for a same date I have six different points with the correspondings flows. I would like to make a query to obtain something like: DATE POINT1 POINT2 POINT3 POINT4 POINT5 POINT6 where for a date I have the flows data of the different points. I think that I would need to establish different alias for the same field. Is this possible? Perhaps something like: SELECT date, flow AS POINT1 FROM samples WHERE POINT=1 UNION ALL SELECT date, flow AS POINT2 FROM samples WHERE POINT=2 UNION ALL SELECT date, flow AS POINT3 FROM samples WHERE POINT=3 ...; and a SELECT over the result of this subselect, that groups by date, or so? Thanks for you help. Javier ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] UPDATE comparing dates and non-dates data
Hi; I've got date data, extracted from rain gauge stations. The date of a row in my data are structured in three integer fields, and as a result of a query I can get the following (what is quite good for my): cod_station | year | month | day | rain -+--+---+-+-- 7250| 1933 | 8 | 1 |45 7250| 1933 | 8 | 2 |3 7250| 1933 | 8 | 3 |0 ... and this for several rain station for about forty years. My problem is that I need to find missing data (a missing data here is a non-existent row). I think I could prepare a table with a complete series of date field, and ALTER it to ADD aditional fields for every station I need to add: date | stat_7250 | stat_7237 ... ++---+--... 1/11/1999 2/12/1999 ... , and make an UPDATE that in someway, will compare the "date" of this table with an extracted date from "year", "day" and "rain" from the aforementioned SELECT, leaving the non-coincident dates in blank and filling the rain data of the coincident dates. Is this possible? How? Please help. Thank you for any possible advice -- A. Javier García Water and Soil Conservation Department CEBAS-CSIC Apartado 4195 30080 Murcia Spain Tel.: +34 968 39 63 90 Fax: +34 968 39 62 13 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
Thanks Josh and Bret for your answers. But I think I can try a slightly different thing. As I said, I've got data, extracted from rain gauge stations. Perhaps I wasn't explicit enough. My source data are in this format: cod_variable | cod_station | year | month | ten | rain_day1 | wind_day1 | rain_day2 | wind_day2 | rain_day3 | wind_day3 | rain_day4 | wind_day4 | rain_day5 | wind_day5 | rain_day6 | wind_day6 | rain_day7 | wind_day7 | rain_day8 | wind_day8 | rain_day9 | wind_day9 | rain_day10 | wind_day10 | rain_day11 | wind_day11 | ten_sum_rain --+-+--+---+-+---+---+---+---+---+---+---+---+-- 30201| 7237| 1953 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | || 0 and this is just a row (ten means that the data belong to the first, second or third of the weeks of the month). I've got five gauge stations and daily data for about forty years (73000 of these long rows). Missing data are non-existent rows; so, when a row doesn't appears I lost ten rain data (the rain of ten days). But with the help of the wonderful people in this list I could extract rain series (excellent): cod_station | year | month | day | rain -+--+---+-+-- 7250| 1933 | 8 | 1 |0 7250| 1933 | 8 | 2 |0 7250| 1933 | 8 | 3 |0... So, now, in the result of this query, a missing data translates into several non-existent row. Now, I have prepared a table with a complete series of date field (everyday from 1553 to 2004), and made ALTER it to ADD aditional fields for every station I have: year | month | day | st7237 | st7238 | st7239 ... --+---+-+-+-+-+- 1953 | 1 | 1 | | | | 1953 | 1 | 2 | | | | 1953 | 1 | 3 | | | |... My problem is that I need to fill in the gaps (the available rain data in the corresponding stations), and this would be a very good output for me. I've prepared an UPDATE but it doesn't work. Perhaps someone could tell me where is the error (I've not a very good knowledge of Postgresql). The UPDATE is based on the results of the query: UPDATE series_lluvia SET st7237=rain FROM /* here begins the SELECT to obtain the series for one rain gauge station; and it works right from here to the next comment */ SELECT cod_station, year, month, day, rain FROM ( SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 2 as day, rain_day2 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 3 as day, rain_day3 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 4 as day, rain_day4 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 5 as day, rain_day5 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 6 as day, rain_day6 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 7 as day, rain_day7 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 8 as day, rain_day8 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 9 as day, rain_day9 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 10 as day, rain_day10 as rain FROM pluviometria WHERE ten=1 UNION ALL SELECT cod_variable, cod_station, year, month, 11 as day, rain_day1 as rain FROM pluviometria WHERE ten=2 UNION ALL SELECT cod_variable, cod_station, year, month, 12 as day, rain_day2 as rain FROM pluviometria WHERE ten=2 UNION ALL SELECT cod_variable, cod_station, year, month, 13 as day, rain_day3 as rain FROM pluviometria WHERE ten=2 UNION ALL SELECT cod_variable, cod_station, year, month, 14 as day, rain_day4 as rain FROM pluviometria WHERE ten=2 UNION ALL SELECT cod_variable, cod_station, year, month, 15 as day, rain_day5 as rain FROM pluviometria WHERE ten=2 UNION ALL SELECT cod_variable, cod_station, year, month, 16 as day, rain_day6 as rain FROM pluviometria WHERE ten=2 UNION ALL SELECT cod_variable, cod_station, year, month, 17 as day, rain_day7 as rain FROM pluviometria WHERE ten=2 UNION ALL SELECT cod_variable, cod_station, year, month, 18 as day, rain_day8 as rain FROM pluviometria WHERE ten=2 UNION ALL SELECT cod_variable, cod_stat
Re: [SQL] UPDATE with a SELECT and subSELECT(About comparing dates and non dates data)
Thanks Christoph; Typing what you told me there was an errror; I' ve changed it slightly an it seems that this sintax is correct. I say "it seems" because the computer begins to process the query but doesn't finish it. I've leaved it working for more than half an hour, before cancel it, with no result. Thank you anyway. This is what I think is a good sintax for UPDATE - SELECT - SUBSELECT. Perhaps in mor simple cases it works. May someone is interested in it. -- Javier UPDATE series_lluvia SET st7237=( SELECT rain FROM ( SELECT cod_variable, cod_station, year, month, 1 as day, rain_day1 as rain FROM pluviometria WHERE ten=1 UNION ALL ... SELECT cod_variable, cod_station, year, month, 31 as day, rain_day11 as rain FROM pluviometria WHERE ten=3 AND rain_day11 IS NOT NULL ORDER BY cod_station, year, month, day) AS temp WHERE cod_station=7237) AS temp2 WHERE series_lluvia.year=temp2.year AND series_lluvia.month=temp2.month AND series_lluvia.day=temp2.day); -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] EXTERN JOIN with WHEN query
Hi all; This is a query that I guess is not very dificult, but I'm a newbie; I've got a lot of tables, each of them with two columns: SELECT * FROM precal; -> (date) (real) fecha| precipitacion +--- 1996-01-01 | 0.6 1996-02-01 | 0.7 ... But in this table there are some inexistents records (some missing days) And I would like to create lists with a full list of dates and corresponding precipitation data, with gaps when the row didn't exist. So; I've created a table with a complete series of dates from 1950 up to date, and made the query: SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON (fechas.fecha = precal41.fecha); This is perfect. But to make it better, would like to include just the dates from the first one in the precal table. So, I've tried: SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON (fechas.fecha = precal41.fecha) WHEN fechas.fecha >= min(precal41.fecha); With the answer: ERROR: parser: parse error at or near "WHEN" Could you help me with this query? Thanks and regards Javier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] date question
Hi; I've got a table in the way: year | month | day | est7237 | est7238 | est7239 | est7250 --+---+-+-+-+-+- 2002 | 9 | 1 | | | | 2002 | 9 | 2 | | | | 2002 | 9 | 3 | | | | ... This table is: murciadb=# \d series_lluvia_completas Table "series_lluvia_completas" Column | Type | Modifiers -+-+--- year| integer | month | integer | day | integer | est7237 | real| est7238 | real| est7239 | real| est7250 | real| And I've got another related tables with a date field. I need to compare the dates field in the other tables with "year", "month", "day" in this table. Is there a way to create a table from this table, directly in Postgres in which a date field is created based in the values of "year", "month", "day" in this table? Thanks and regards Javier ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] "No such attribute or function 'oid'"
Hi; I'm using ODBC driver for windows, because I use Excel (Microsoft) to work with data. When I try to get data from a Postgres table there is no problem; but when I try to acces a view I get the mesage: "No such attribute or function 'oid'" When I try to access views from ACCESS2000 there is no problem What is the cause of this? Can I resolve it? Thanks and regards Javier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] date question
Hi all; Peter, thank you very much for your help. Just a little thing. I've done as you say: CREATE TABLE rain_series_dated AS SELECT (year * interval '1 year' + month * interval '1 month' + day * interval '1 day') AS fecha,est7237,est7238,est7239,est7250 FROM rain_series ORDER by fecha; I see that the obtained new field is "interval" type: Column | Type | Modifiers -+--+--- fecha | interval | est7237 | real | est7238 | real | And I've tried to use this result to be compared with my other table in which "fecha" is "date" type. The result is that the query halts with no result; I guess that it is because it tries to compare different data types. Is it possible to cast the "interval" obtained type into a "date" one in the creation of the mentioned table? (I've looked it in the documentation, but I can't find the answer) Thanks and regards, Javier El Mar 17 Jun 2003 20:09, escribió: > javier garcia - CEBAS writes: > > Is there a way to create a table from this table, directly in Postgres in > > which a date field is created based in the values of "year", "month", > > "day" in this table? >-- > year * interval '1 year' + month * interval '1 month' + day * interval '1 > day' > > This results in a timestamp value that you can compare to or assign to a > date value. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] new calculated column
Hi; I've got a table with two fields and about 3000 rows, the second one is a character field, what can have about twenty different values; of course these values are repeated a lot of times in the table. I need to create a new column of type integer, whose value depens on the character fields. The values of the new column are not important, the important thing is who can I create this column and assign a different integer to a different char value in the other column. Thanks -- Javier --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] new calculated column
Hi; I've got a table with two fields and about 3000 rows, the second one is a character field, what can have about twenty different values; of course these values are repeated a lot of times in the table. I need to create a new column of type integer, whose value depens on the character fields. The values of the new column are not important, the important thing is who can I create this column and assign a different integer to a different char value in the other column. Thanks -- Javier ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] odbc drivers
Hi; I've got PostgreSQL 7.2 and I would like to use MSAccess2k as a front end; in a LAN, because some of my colleagues just use Windows. Where could I find a ODBC driver for Postgresql to be use under windows2000? thanks and regards -- Javier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Hairy question - transpose columns
Hello; I've got pluviometric data in a bizarre format (spanish administration is rather original) and I need to "transpose" them, to be able to use them as time series data for a model. The original data are in a format like cod_var | Year | Month | Ten | RainDay1 | RainDay2 | RainDay3 | Rainday4 | Rainday5 | RainDay6 | RainDay7 | RainDay8 | Rainday9 | Rainday10 | Rainday11 --+---++-++++++++++++ 452 | 1995 | 1 | 1 | 2 | 5 | 6 | -3 | 0 |5|4 | 5 | 4 | 4 | 452 | 1995 | 3 |2| 4 | 5 | 0 | 5 | 3 | 23 |4 | 34 | 4 | 2 | 452 | 1996 | 12|3| 12| 2 | 3 | 4 | 7 |3|3 | 15 | 2 | 4 |3 452 | 1998 | 9 |2| 2 | 8 | 6 | -3 | 5 |0|2 | 6 | 0 | 1 | 452 | 1998 | 3 |3| 2 |-3 | 7 | 9 | 4 |2|5 | 6 | 1 | 16|3 .. As you may see, the date of a rain datum is defined by the value of the fields Year, Month,Ten (1=first ten days of month; 2=second ten days of month; 3=up to eleven last days of month). and the field in which the datum is (RainDay1, RainDay2...) This is no useful for me because I need something like: cod_var | Year | Month | Day | Rain | --+---++-++-- 452 | 1995 | 1 | 1 | 2 | 452 | 1995 | 1 | 2 | 5 | 452 | 1995 | 1 | 3 | 6 | 452 | 1995 | 1 | 4 | -3 | 452 | 1995 | 1 | 5 | 0 | 452 | 1995 | 1 | 6 | 5 | ... Perhaps this is not possible to do with a database? Should I manage to make a program to fix this? Thanks for your help. I really can't imagine how to do this with Postresql; though I'm a newbye. Regards --- Javier ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Basic question.
Hi; I'm sorry for ask this basic question. But I can't find in the documentation. I'm connected to a database; and how can I execute a query that I've got in a file? And. If I'm not connected to any database. Can I execute a file that makes a query on a database? Thanks - Javier ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] ORDER the result of a query by date
Hello; I've got the result of a query that appears as: cod_variable | cod_station | year | month | day | rain -+---++--+--+-- 30201 | 7237 | 1953 |1 | 1 |2 30201 | 7237 | 1953 |2 | 1 |5 30201 | 7237 | 1953 |3 | 1 |0 30201 | 7237 | 1953 |4 | 1 | -3 . (Of course, thanks to Stephan Szabo for the method to obtain this from my strange source data. I didn't think this was possible.) After have done this query I've realized that I need the data to be ordered by date. Do you have any suggestion? Best regards -- Javier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
