Monica: Postgresql compatibility

[beajeanm]

I'm running a monica instance on postgres and I run into a few issues during my set-up:

• https://github.com/monicahq/monica/blob/c199c5e4f017404a18b3c9733d8f97ff2fb3e5cb/database/migrations/2017_05_04_193252_alter_activity_nullable.php uses the MODIFY keyword. This is valid for MySql and Oracle, but not Postgresql.
  I had to use DB::statement('ALTER TABLE "activities" ALTER COLUMN "activity_type_id" TYPE INTEGER;');
  I'm not familliar with Laravel migration, so I'm not sure how to make the code pick the right SQL statement based on the configured database.

• A few columns in the users and contacts were created with NOT NULL constraints and monica violated those constraints when creating a new users or inserting a new contact.
  users: gender, facebook_user_id
  contacts: number_of_tasks_in_progress, number_of_tasks_completed, default_avatar_color
  I'm not sure if this was due to a bug on the version of monica I've checked out or an imcompatibility with postgresql.

[erdmenchen]

The NOT NULL constraints issue applies to SQLite, too.

[djaiss]

The first item of this issue is fixed and in production.

[jdambron]

I confirm having the same problem with NOT NULL constraints on SQLite

[beajeanm]

Thanks for fixing the first point. Do we know why we are having inconsistency with nullable columns between MySql and Postgresql/SQLite?
I'm happy to close if you think those have been addressed too. (I haven't run into similar issues when doing the last few upgrades)

[rixx]

I received Postgres errors when setting up, aswell:

  [Illuminate\Database\QueryException]                                                                                   
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "CHANGE"                                              
  LINE 1: ALTER TABLE contacts CHANGE COLUMN gender gender ENUM('male'...                                                
                               ^ (SQL: ALTER TABLE contacts CHANGE COLUMN gender gender ENUM('male', 'female', 'none'))  



  [Doctrine\DBAL\Driver\PDOException]                                        
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "CHANGE"  
  LINE 1: ALTER TABLE contacts CHANGE COLUMN gender gender ENUM('male'...    
                               ^                                             



  [PDOException]                                                             
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "CHANGE"  
  LINE 1: ALTER TABLE contacts CHANGE COLUMN gender gender ENUM('male'...    
                               ^                                             

[simhnna]

@djaiss the second one isn't yet this issue is closed. Please reopen it. I just ran into it as well.

[asbiin]

Migrations with postgres was fixed with #785.
I will not close this issue because (a lot of) other errors happens with Postgres right now ...

[catatonik]

While following the Installing-Monica-on-Debian guide, but replacing mysql with postgresql, the installation fails at step 7.7 running: php artisan setup:production

The error messages while running this command are:

You are about to setup and configure Monica. Do you wish to continue? (yes/no) [no]:

yes

In Connection.php line 664:

SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "about_object_id" cannot be cast automatically to type integer
HINT: You might need to specify "USING about_object_id::integer". (SQL: ALTER TABLE gifts ALTER about_object_id TYPE INT)

In PDOStatement.php line 107:

SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "about_object_id" cannot be cast automatically to type integer
HINT: You might need to specify "USING about_object_id::integer".

In PDOStatement.php line 105:

SQLSTATE[42804]: Datatype mismatch: 7 ERROR: column "about_object_id" cannot be cast automatically to type integer
HINT: You might need to specify "USING about_object_id::integer".

Versions:
monica version: 1.8.1
php version: PHP 7.0.27-0+deb9u1
postgresql version: psql (PostgreSQL) 10.3 (Debian 10.3-1.pgdg90+1)

Would it be possible to fix these, so that monica works with postgresql please?

Many thanks,

[i3anaan]

@asbiin This issue ('Postgresql compatibility' in general) is not fixed yet.

While the initial setup/launch worked after https://github.com/monicahq/monica/pull/1309, actually using the software made more errors surface, errors serious enough to make it unusable. (I do not have access to these errors right now, but intend to post more info once I have)

I suggest to reopen this issue, and specifically have a look at https://github.com/monicahq/monica/pull/781 that brings up an interesting suggestion to increase postgres compatibility by setting MySQL to a more strict mode that is more similar to how postgres works.

Edit: An alternative would be to leave this issue closed as it seems to be about specific issues by now solved, but open a (new) issue for general compatibility with PostgreSQL.