Problem with table creation in PostgreSQL

Yeah, I have the same error, only I tried the -dev version and got it. Too bad, because I really wanted to try this module out.

This is the exact error I receive when enabling 5.x-1.x-dev version of Contemplate with a pgsql db on Drupal 5.2:

warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "(" at character 205 in /var/www/aigeanta.net/includes/database.pgsql.inc on line 125.
user warning: query: CREATE TABLE contemplate ( type varchar(32) NOT NULL default '', teaser text NOT NULL, body text NOT NULL, rss text NOT NULL, enclosure varchar(128) NOT NULL, flags int(8) unsigned NOT NULL default '0', PRIMARY KEY (type) ); in /var/www/aigeanta.net/includes/database.pgsql.inc on line 144.
warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "KEY" at character 111 in /var/www/aigeanta.net/includes/database.pgsql.inc on line 125.
user warning: query: CREATE TABLE contemplate_files ( site varchar(255) NOT NULL, data longblob NOT NULL, UNIQUE KEY site (site) ); in /var/www/aigeanta.net/includes/database.pgsql.inc on line 144.

Yes, I get the same error with psql. both with stable and dev!

I'm pretty new to postgres, but I think the problem is just that the install scripts that create the tables are using MySQL-only types. I created the tables by hand using the following SQL statements:

CREATE TABLE contemplate (
    type character(32) DEFAULT '' NOT NULL,
    teaser text NOT NULL,
    body text NOT NULL,
    rss text NOT NULL,
    enclosure character(128) NOT NULL,
    flags integer DEFAULT 0 NOT NULL,
    PRIMARY KEY (type)
);


CREATE TABLE contemplate_files (
    site character(255) NOT NULL,
    data bytea NOT NULL,
    UNIQUE (site)
);

I have also attached a patch to contemplate.install that should fix the problem with the installer, though I haven't been able to test it, since I don't really know how to get drupal to re-run the database creation step of an already known module.

Thanks,
Geoff

I had a problem and accidentally deleted my contemplate and contemplate_files database tables. I had to recreate them in phpMyAdmin by clicking on SQL and entering this code...

Firstly......

CREATE TABLE contemplate (
type character(32) DEFAULT '' NOT NULL,
teaser text NOT NULL,
body text NOT NULL,
rss text NOT NULL,
enclosure character(128) NOT NULL,
flags integer DEFAULT 0 NOT NULL,
PRIMARY KEY (type)
);

and secondly.....

CREATE TABLE contemplate_files (
site varchar(255) NOT NULL,
`data` longblob NOT NULL,
UNIQUE KEY site (site(255))
);

--------------------------------------
Note that the code for CREATE TABLE contemplate_files is slightly different to what ghing provided because that gave me a syntax error.

attached is my patch for this issue.

Tested on Drupal 5.7 and was able to successfully install the tables in PostgreSQL. I have not tested further use of the module at this point.

Patch has been applied to 5.x-1.x-dev will also be included in next release. I also applied the patch to 6.x-1.x-dev.

This still seems to be an issue. I've just installed contemplate-5.x-2.01 and am getting the same warnings.
Are there any plans to integrate the various patches into a new release?

The patch in comment #6 has been applied, see comment #7. I don't have any machines with PostgreSQL installed, if you have anymore patches you want applied, I am more than happy to apply them.

I've tried contemplate again with a fresh install.
Drupal 5.7
Postgresql 8.3.0
cck-5.x-1.6-1
contemplate-5.x-2.01

Module installation completes without errors, but when creating a template errors occur. I've made two adjustments in contemplate.module that seem to stop the warnings/errors.

function contemplate_delete_type_form_submit($form_id, $form_values) {
  $types = node_get_types();
  $sql = 'DELETE FROM {contemplate} WHERE type=\'%s\'';     // Changed ` to '
  $result = db_query($sql, $form_values['type']);
}

function contemplate_delete($type) {
    return db_query('DELETE FROM {contemplate} WHERE type = \'%s\'', $type); // Changed " to '
}

These changes have been committed, will appear in the next release.