Hi guys! I didn’t find any explanation how to crea...

# exposed

[Lbenyehu]

Hi guys! I didn’t find any explanation how to create a table (postgres in my case) with partitions… someone know??

[rcd27]

Is this Postgres-specific feature?

[Lbenyehu]

I think it is SQL feature

[rcd27]

Don't know then. I had specific postgres feature, implemented it via standard Java SQL interface...

[Lbenyehu]

Can you share it with me?

[rcd27]

Sure, no problem. Here I'm sending some raw queries for Postgres to use its features.

private fun preparePostgresForPartialTextSearch() {
        //region create-extension
        val createExtensionQuery = """
            CREATE EXTENSION pg_trgm;
        """.trimIndent()

        try {
            val createExtensionStatement = connection!!.createStatement()
            createExtensionStatement.execute(createExtensionQuery)
        } catch (e: SQLException) {
            log().error(e.message ?: "Error during creating extension in [$databaseName] database")
        }
        //endregion

        //region create indices for eng_short_name
        val createIndicesForEngItems = """
            CREATE INDEX engitems_trgm_idx ON allitems
             USING gin (eng_short_name gin_trgm_ops);
        """.trimIndent()

        try {
            val createIndicesForEngItemsStatement = connection!!.createStatement()
            createIndicesForEngItemsStatement.execute(createIndicesForEngItems)
        } catch (e: SQLException) {
            log().error(e.message ?: "Error during creating engitems_trgm_idx for [allitems] table")
        }
        //endregion

        //region create indices for ru_short_name
        val createIndicesForRuItems = """
            CREATE INDEX ruitems_trgm_idx ON allitems
             USING gin (ru_short_name gin_trgm_ops);
        """.trimIndent()

        try {
            val createIndicesForRuItemsStatement = connection!!.createStatement()
            createIndicesForRuItemsStatement.execute(createIndicesForRuItems)
        } catch (e: SQLException) {
            log().error(e.message ?: "Error during creating ruitems_trgm_idx for [allitems] table")
        }
        //endregion

        log().info("Postgres seems to be prepared for partial text search")
    }

[rcd27]

Setup is like this:

private val database: Database by lazy {
        Database.connect(
            "jdbc:<postgresql://localhost:5432/$databaseName>",
            driver = "org.postgresql.Driver",
            user = "user",
            password = "password"
        )
    }

    private val connection: Connection?

    init {
        log().info("Database initiated: ${database.name}")

        val url = "jdbc:<postgresql://localhost:5432/$databaseName>"
        val user = "user"
        val password = "password"

        connection = try {
            DriverManager.getConnection(url, user, password)
        } catch (e: SQLException) {
            log().error("Error while trying to connect to database: ${e.message}")
            null
        }
        prepareAllItemsTable()
        preparePostgresForPartialTextSearch()
    }

[Lbenyehu]

wow! thanks!