[PostgreSQL] Refactor sql scripts and documentation

[mpmadhavig]

Current Limitation

We have been using the SQL command combination DROP TABLE IF EXISTS + CREATE TABLE for table creation in PostgreSQL queries, as earlier versions of PostgreSQL (prior to version 9.1) did not support CREATE TABLE IF NOT EXISTS [1]. Since Identity Server no longer supports these older PostgreSQL versions [2], we can refactor these queries to use the safer CREATE TABLE IF NOT EXISTS command.

Additionally, I also noticed some instances where documentation references PostgreSQL 9.x versions [3][4], even though the tested versions are listed as 10.19, 13.7, and 14 [2]. This inconsistency could be misleading when determining supported PostgreSQL versions, so these references should be updated for clarity and accuracy.

Also the cleanup script provided in [3] and [4] has a 9.x version and a 11.x version. We will need to understand what would be compatible with the 10.x version before removing the 9.x references.

[1] https://www.postgresql.org/docs/9.1/release-9-1.html#AEN127121
[2] https://is.docs.wso2.com/en/latest/deploy/environment-compatibility/#tested-dbmss
[3] https://is.docs.wso2.com/en/latest/deploy/remove-unused-tokens-from-the-database/#use-the-store-procedure-for-token-cleanup
[4] https://is.docs.wso2.com/en/latest/deploy/configure/databases/data-purging/

Suggested Improvement

To ensure consistency, we may need a refactoring effort to review and clean up PostgreSQL scripts and documentation.

Version

7.1.0