Postgres Support?

[stephbaxter]

Hi I am connecting to postgresql with a table of type 'hypertable' indexed on 'timeStamp'.

I have created a connection of type SQL advanced. I can retrieve data.

I have created action ADD using Fusio\Adapter\Sql\Action\SqlInsert

When I send the json payload the record IS inserted into the database table BUT I get the following erro:

SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: lastval is not yet defined in this session in /var/www/html/fusio/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php on line 18

This seems to be caused by the way postgresql responds and the absence of an ID field / primary key. (this might help https://github.com/nextcloud/files_antivirus/issues/86).

From the postgresql timeseriesdb docs: You can also specify that INSERT returns some or all of the inserted data via the RETURNING statement:

INSERT INTO conditions VALUES (NOW(), 'office', 70.1, 50.1) RETURNING *;

Have you experience using postgresql or perhaps have plans to create a postgresql connector type?

Thanks

[chriskapp]

Hi, ok so in general Fusio and the SQL connection also supports Postgres but it looks like it is a problem regarding the SqlInsert action. In the action we return the last inserted id s. https://github.com/apioo/fusio-adapter-sql/blob/master/src/Action/SqlInsert.php#L57 and it looks like this method produces this error.

Iam not familiar with the postgresql timeseriesdb but it looks like this is an extension to postgres? So Iam not sure whether the problem is related to the extension or whether this also occurs on a plain postgres installation.

Could you maybe provide me your table structure and the JSON which you send to your endpoint, then I could also try to reproduce the problem on a plain postgrest setup.