Open2300 - Support Question 2008x 07x 11x 085540

Create Postresql table

Question

I would like to create the BDD with pgsql. But there is only a mysql23200.sql file to create mysql BDD/table. How can I do to create the BDD/table under postgresql, on an small ubuntu hardy server ? Thank you.

Eric

Environment

Open2300 version: 1.11
Libraries: postgresql
Server OS:  

-- EDeVito - 11 Jul 2008

Answer

Ok, I have the answer.

Here is the table for postgresql :

DROP TABLE "weather" CASCADE\g

CREATE TABLE "weather" (
"datetime" timestamp without time zone NOT NULL default '1970-01-01 00:00:00',
"temp_in" decimal(4,1) NOT NULL default '0.0',
"temp_out" decimal(4,1) NOT NULL default '0.0',
"dewpoint" decimal(4,1) NOT NULL default '0.0',
"rel_hum_in" smallint NOT NULL default '0',
"rel_hum_out" smallint NOT NULL default '0',
"wind_speed" decimal(3,1) NOT NULL default '0.0',
"wind_angle" decimal(4,1) NOT NULL default '0.0',
"wind_direction" char(3) NOT NULL default '',
"wind_chill" decimal(4,1) NOT NULL default '0.0',
"rain_1h" decimal(3,1) NOT NULL default '0.0',
"rain_24h" decimal(3,1) NOT NULL default '0.0',
"rain_total" decimal(5,1) NOT NULL default '0.0',
"rel_pressure" decimal(5,1) NOT NULL default '0.0',
"tendency" varchar(7) NOT NULL default '',
"forecast" varchar(6) NOT NULL default '',
unique ("datetime")
) ;

It is also necessary to modifiy pgsql2300.c

replace

sprintf(query, "INSERT INTO %s VALUES ('%s', current_timestamp, %s)", config.pgsql_table, config.pgsql_station, logline);
with

sprintf(query, "INSERT INTO %s VALUES (current_timestamp, %s)", config.pgsql_table, logline);

since there is no column for station-id (and if you don't have more than 1 station it is not necessary, this seemsto be the default with mysql backend).

However, if you wish to keep the station id in the database, it is necessary to add a column in the first place for getting the station-id :

"station-id" varchar(9) NOT NULL default ''

or

"station-id" char(9) NOT NULL default ''

should do the trick.
Topic revision: r2 - 18 Jul 2008, EDeVito
Copyright © 1999-2018 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
This website only use harmless session cookies. See Cookie Policy for details. By using this website you accept the use of these cookies.