schema update to version 35 is not compatible with postgresql 7.4

[kaouete@…]

Hi,

when updating my schema to version 35 with current svn (r985) i get this error :

execSqlUpdate(): An error occured while executing the following SQL query :

BEGIN;


UPDATE schema_info SET value='35' WHERE tag='schema_version';
CREATE TABLE servers ( 
  server_id text NOT NULL PRIMARY KEY,
  is_default_server boolean NOT NULL DEFAULT FALSE,
  name text NOT NULL DEFAULT 'Unnamed server' CHECK (name<>''),
  creation_date date NOT NULL DEFAULT now(),
  hostname text NOT NULL DEFAULT 'localhost' CHECK (name<>''),
  ssl_available BOOLEAN NOT NULL DEFAULT FALSE,
  gmaps_api_key text
);
INSERT INTO servers (server_id, is_default_server, name, creation_date, hostname, ssl_available, gmaps_api_key) VALUES ('auth-wifi-crazydwarves-org', TRUE, 'Unnamed server', (SELECT creation_date FROM networks GROUP BY (creation_date) ORDER BY min(creation_date) LIMIT 1), 'auth.wifi.crazydwarves.org', TRUE, 'ABQIAAAA5q67PKeUpLHnR1x60n17GxTPNWs86dub6uFngnGM2hkyRN6FHxRAFHmlyBKr05GOJ8l2pFSMZVcIEw');
ALTER TABLE networks ADD COLUMN gmaps_initial_latitude NUMERIC(16, 6);
ALTER TABLE networks ADD COLUMN gmaps_initial_longitude NUMERIC(16, 6);
ALTER TABLE networks ADD COLUMN gmaps_initial_zoom_level integer;
ALTER TABLE networks ADD COLUMN gmaps_map_type text NOT NULL DEFAULT 'G_MAP_TYPE' CHECK (gmaps_map_type<>'');
UPDATE networks SET gmaps_initial_latitude = '48.856', gmaps_initial_longitude = '2.348', gmaps_initial_zoom_level = '5';

COMMIT;
VACUUM ANALYZE;

Error message :
ERROR: adding columns with defaults is not implemented HINT: Add the column, then use ALTER TABLE SET DEFAULT.

you can see on the Notes section of  http://www.postgresql.org/docs/7.4/interactive/sql-altertable.html that :

In the current implementation of ADD COLUMN, default and NOT NULL clauses for the new column are not supported. The new column always comes into being with all values null. You can use the SET DEFAULT form of ALTER TABLE to set the default afterward. (You may also want to update the already existing rows to the new default value, using UPDATE.) If you want to mark the column non-null, use the SET NOT NULL form after you've entered non-null values for the column in all rows.

Thanks you :)

[anonymous]

here is my updated version :

BEGIN;


UPDATE schema_info SET value='35' WHERE tag='schema_version';
CREATE TABLE servers ( 
  server_id text NOT NULL PRIMARY KEY,
  is_default_server boolean NOT NULL DEFAULT FALSE,
  name text NOT NULL DEFAULT 'Unnamed server' CHECK (name<>''),
  creation_date date NOT NULL DEFAULT now(),
  hostname text NOT NULL DEFAULT 'localhost' CHECK (name<>''),
  ssl_available BOOLEAN NOT NULL DEFAULT FALSE,
  gmaps_api_key text
);
INSERT INTO servers (server_id, is_default_server, name, creation_date, hostname, ssl_available, gmaps_api_key) VALUES ('auth-wifi-crazydwarves-org', TRUE, 'Unnamed server', (SELECT creation_date FROM networks GROUP BY (creation_date) ORDER BY min(creation_date) LIMIT 1), 'auth.wifi.crazydwarves.org', TRUE, 'ABQIAAAA5q67PKeUpLHnR1x60n17GxTPNWs86dub6uFngnGM2hkyRN6FHxRAFHmlyBKr05GOJ8l2pFSMZVcIEw');
ALTER TABLE networks ADD COLUMN gmaps_initial_latitude NUMERIC(16, 6);
ALTER TABLE networks ADD COLUMN gmaps_initial_longitude NUMERIC(16, 6);
ALTER TABLE networks ADD COLUMN gmaps_initial_zoom_level integer;
ALTER TABLE networks ADD COLUMN gmaps_map_type text CHECK (gmaps_map_type<>'');
ALTER TABLE networks ALTER COLUMN gmaps_map_type SET DEFAULT 'G_MAP_TYPE';
UPDATE networks SET gmaps_map_type = 'G_MAP_TYPE';
ALTER TABLE networks ALTER COLUMN gmaps_map_type SET NOT NULL;
UPDATE networks SET gmaps_initial_latitude = '48.856', gmaps_initial_longitude = '2.348', gmaps_initial_zoom_level = '5';

COMMIT;
VACUUM ANALYZE;

[max-horvath]

I'm sorry for that ... seems it's an error my developer machine runs Postgres 8.1 ...

I'll be fixing it in my next commit ...

[max-horvath]

Fixed in revision [986].