Channels
#exposed
Title
# exposed
l
Lbenyehu
08/18/2021, 12:39 PMHi guys! I didn’t find any explanation how to create a table (postgres in my case) with partitions… someone know??
r
rcd27
08/20/2021, 1:01 PMIs this Postgres-specific feature?
l
Lbenyehu
08/22/2021, 9:42 AMI think it is SQL feature
r
rcd27
08/23/2021, 9:27 AMDon't know then. I had specific postgres feature, implemented it via standard Java SQL interface...
l
Lbenyehu
08/23/2021, 9:56 AMCan you share it with me?
r
rcd27
08/23/2021, 7:08 PMSure, no problem. Here I'm sending some raw queries for Postgres to use its features.
Copy code
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")
}Setup is like this:
Copy code
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()
}l
Lbenyehu
09/01/2021, 1:49 PMwow! thanks!
🤝 1
3 Views