Error when using postgres in 0.12

[trbouma]

Hi,

I get this error below upon mint startup when trying to use postgres. Everything works fine when I use SQLite3.

I will see if I can track down, but I wanted to post the issue sooner than later to see if there is an easy fix.

2023-05-07 07:31:25.41 | ERROR | psycopg2.errors.SyntaxError: subquery in FROM must have an alias
LINE 3:         SELECT COALESCE(SUM(s), 0) AS balance FROM (
                                                           ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

The above exception was the direct cause of the following exception:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) subquery in FROM must have an alias
LINE 3:         SELECT COALESCE(SUM(s), 0) AS balance FROM (
                                                           ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

[SQL: 
        CREATE VIEW balance_issued AS
        SELECT COALESCE(SUM(s), 0) AS balance FROM (
            SELECT SUM(amount) AS s
            FROM promises
            WHERE amount > 0
        );
    ]
(Background on this error at: http://sqlalche.me/e/13/f405)

2023-05-07 07:31:25.41 | ERROR | Application startup failed. Exiting.

[trbouma]

Testing in the sqlite3 and psql consoles, this statement works fine for the first, but choke for the latter

select coalesce(sum(s), 0) as balance from (select sum(amount) as s from promises where amount > 0);

This is the error in postres

ERROR:  subquery in FROM must have an alias
LINE 1: select coalesce(sum(s), 0) as balance from (select sum(amoun...
                                                   ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

[trbouma]

Update - postgres expects the syntax to be in this order:

select coalesce(sum(s), 0) as balance from (select sum(amount)from promises where amount > 0) as s;

Of course, this variant chokes in SQLite

Parse error: no such column: s
  select coalesce(sum(s), 0) as balance from (select sum(amount)from promises wh
                      ^--- error here

I will see if I can do a fix and a pull request later today

[trbouma]

Looking positive - looks like the fix I am doing for postgres, doesn't break sqlite3. Should have something later today.

[trbouma]

I have added some quick fixes. You can see in https://github.com/cashubtc/cashu/pull/194