I did a setup with latest staging, testing data and 4 additional languages. After installation I went in Extensions > Database and it gives me this message:

Table 'kp7cx_finder_links' does not have column 'description' with type text DEFAULT '' NOT NULL. (From file 3.6.3-2016-10-04.sql.)

So I pressed the Fix-Button and I got this syntax error:
Syntaxfehler bei »DEFAULT« LINE 1: ...inder_links" ALTER COLUMN "description" TYPE text DEFAULT ''... ^SQL=ALTER TABLE "#__finder_links" ALTER COLUMN "description" TYPE text DEFAULT '' NOT NULL;

So next I started the indexing process which went fine but at the end I got this syntax error:

FEHLER: Syntaxfehler bei »t« LINE 1: DELETE t FROM "#finder_taxonomy" AS t LEFT JOIN "#... ^SQL=DELETE t FROM "#__finder_taxonomy" AS t LEFT JOIN "#__finder_taxonomy_map" AS m ON m.node_id = t.id WHERE t.parent_id > 1 AND m.link_id IS NULL

Finally I did a search for "test" in the "Indexed Content" and this message appears (operator doesn´t exist: timestamp without timezone ...):

FEHLER: Operator existiert nicht: timestamp without time zone ~~ unknown LINE 4: ...t%' OR "l"."url" LIKE '%test%' OR "l"."indexdate" LIKE '%te... ^ HINT: Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.SQL=SELECT l.,"t"."title" AS "t_title" FROM "#__finder_links" AS "l" INNER JOIN "#__finder_types" AS "t" ON "t"."id" = "l"."type_id" WHERE ("l"."title" LIKE '%test%' OR "l"."url" LIKE '%test%' OR "l"."indexdate" LIKE '%test%') ORDER BY l.title ASC LIMIT 20 FEHLER: Operator existiert nicht: timestamp without time zone ~~ unknown LINE 4: ...t%' OR "l"."url" LIKE '%test%' OR "l"."indexdate" LIKE '%te... ^ HINT: Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.SQL=SELECT COUNT() FROM "#__finder_links" AS "l" INNER JOIN "#__finder_types" AS "t" ON "t"."id" = "l"."type_id" WHERE ("l"."title" LIKE '%test%' OR "l"."url" LIKE '%test%' OR "l"."indexdate" LIKE '%test%') FEHLER: Operator existiert nicht: timestamp without time zone ~~ unknown LINE 4: ...t%' OR "l"."url" LIKE '%test%' OR "l"."indexdate" LIKE '%te... ^ HINT: Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.SQL=SELECT COUNT(*) FROM "#__finder_links" AS "l" INNER JOIN "#__finder_types" AS "t" ON "t"."id" = "l"."type_id" WHERE ("l"."title" LIKE '%test%' OR "l"."url" LIKE '%test%' OR "l"."indexdate" LIKE '%test%')

@waader the goals of this pr are more simple:

1. let's run index content from Smart Search: Indexed Content
2. let's save/create an article when the content finder plugin is enabled

a lot of more work need to be done, but this PR only affect postgresql related files so no B/C
i'll continue to work to let the com_finder workable even on Postgresql but i'll submit other PR's shortly

I understand. So I repeated the test with the same setup but without the multilingual stuff.

ad 1) let's run index content from Smart Search: Indexed Content:
This brings a syntax error at the end: FEHLER: Syntaxfehler bei »t« LINE 1: DELETE t FROM "#finder_taxonomy" AS t LEFT JOIN "#... ^SQL=DELETE t FROM "#__finder_taxonomy" AS t LEFT JOIN "#__finder_taxonomy_map" AS m ON m.node_id = t.id WHERE t.parent_id > 1 AND m.link_id IS NULL

ad 2. let's save/create an article when the content finder plugin is enabled
When saving changes made to an existing article then you get this syntax error:
Save failed with the following error: FEHLER: Syntaxfehler bei »t« LINE 1: DELETE t FROM "#finder_taxonomy" AS t LEFT JOIN "#... ^SQL=DELETE t FROM "#__finder_taxonomy" AS t LEFT JOIN "#__finder_taxonomy_map" AS m ON m.node_id = t.id WHERE t.parent_id > 1 AND m.link_id IS NULL

i'm currently investigating on it .... but i'm afraid that the needed changes to let it work at least with minimal feature should'nt be only on the Postgresql side ..... i'll updated you....

@waader can you retest ...
now the changes impact Mysql too, so should be tested

Can we not make this query use JDatabaseQuery?

no cause i'm lazy 😄
query rewritten using 3 JDatabaseQuery objects

• added the missed REINDEX
• removed unnecessary clear()

I have not tested this item.

I've tested with MySQL and it works fine. I haven't tested on PostGres which is why I'm not marking this as a successful test.

I created a category called "Test", then created an article in that category.
I checked that a taxonomy for the Test category had been created and the article was correctly indexed.
I deleted the article (and emptied the trash). This would trigger the removeOrphanNodes method to remove the Test category taxonomy.
I did not get any errors, the Test category taxonomy had been removed and the index had been updated correctly.

I have tested this item ✅ successfully on fe8a684

I tested successful on PostgresSQL based on test instruction. Button Index and clear index also works.

Unrelated errors:

• search text field does not work: ERROR: operator does not exist: timestamp without time zone ~ unknown LINE 4: ...e%' OR "l"."url" LIKE '%lime%' OR "l"."indexdate" LIKE '%li... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts

• administrator/index.php?option=com_finder&view=maps: ERROR: column "s.count_published" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a.,s.count_published,s.count_unpublished,COUNT(b.id)... ^ ERROR: column "s.count_published" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a.,s.count_published,s.count_unpublished,COUNT(b.id)... ^ ERROR: column "s.count_published" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a.*,s.count_published,s.count_unpublished,COUNT(b.id)... ^

I have tested this item ✅ successfully on fe8a684

Works with postgres, thanks Alikon!

installation/sql/postgresql/joomla.sql needs an update.

RTC as 2 good testers.

Note: last comment is
"installation/sql/postgresql/joomla.sql needs an update."