This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 912980eef0f Support generate postgres index sql (#17523)
912980eef0f is described below
commit 912980eef0fcb0a97c5185b05750df6eaa81c28c
Author: Chuxin Chen <chuxinche...@qq.com>
AuthorDate: Wed May 11 14:35:33 2022 +0800
Support generate postgres index sql (#17523)
* support generate postgres index sql
* support generate postgres index sql
---
.../ddlgenerator/PostgreDDLGenerator.java | 21 ++-
.../ddlgenerator/PostgresAbstractLoader.java | 20 +++
.../PostgresColumnPropertiesLoader.java | 21 ++-
.../ddlgenerator/PostgresIndexLoader.java | 175 +++++++++++++++++++++
.../PostgresTablePropertiesLoader.java | 3 +-
.../template/indexes/11_plus/column_details.ftl | 48 ++++++
.../resources/template/indexes/11_plus/create.ftl | 30 ++++
.../template/indexes/11_plus/include_details.ftl | 31 ++++
.../resources/template/indexes/default/alter.ftl | 24 +++
.../template/indexes/default/column_details.ftl | 48 ++++++
.../resources/template/indexes/default/create.ftl | 27 ++++
.../resources/template/indexes/default/nodes.ftl | 30 ++++
.../template/indexes/default/properties.ftl | 45 ++++++
13 files changed, 514 insertions(+), 9 deletions(-)
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgreDDLGenerator.java
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgreDDLGenerator.java
index 9112f02e349..e5be002112d 100644
---
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgreDDLGenerator.java
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgreDDLGenerator.java
@@ -31,22 +31,29 @@ import java.util.Map;
*/
public final class PostgreDDLGenerator implements DialectDDLGenerator {
- // TODO support version, partitions, index etc.
+ // TODO support partitions etc.
@Override
public String generateDDLSQL(final String tableName, final String
schemaName, final DataSource dataSource) throws SQLException {
try (Connection connection = dataSource.getConnection()) {
int majorVersion =
connection.getMetaData().getDatabaseMajorVersion();
int minorVersion =
connection.getMetaData().getDatabaseMinorVersion();
- Map<String, Object> context = new
PostgresTablePropertiesLoader(connection, tableName, schemaName, majorVersion,
minorVersion).loadTableProperties();
- new PostgresColumnPropertiesLoader(connection, majorVersion,
minorVersion).loadColumnProperties(context);
- new PostgresConstraintsLoader(connection, majorVersion,
minorVersion).loadConstraints(context);
- return doGenerateDDLSQL(context, majorVersion, minorVersion);
+ Map<String, Object> context = loadGenerateContext(tableName,
schemaName, connection, majorVersion, minorVersion);
+ String tableSql = generateCreateTableSql(context, majorVersion,
minorVersion);
+ String indexSql = new PostgresIndexLoader(connection,
majorVersion, minorVersion).loadIndexSql(context);
+ return tableSql + System.lineSeparator() + indexSql;
}
}
- private String doGenerateDDLSQL(final Map<String, Object> context, final
int majorVersion, final int minorVersion) {
+ private Map<String, Object> loadGenerateContext(final String tableName,
final String schemaName, final Connection connection, final int majorVersion,
final int minorVersion) {
+ Map<String, Object> result = new
PostgresTablePropertiesLoader(connection, tableName, schemaName, majorVersion,
minorVersion).loadTableProperties();
+ new PostgresColumnPropertiesLoader(connection, majorVersion,
minorVersion).loadColumnProperties(result);
+ new PostgresConstraintsLoader(connection, majorVersion,
minorVersion).loadConstraints(result);
+ return result;
+ }
+
+ private String generateCreateTableSql(final Map<String, Object> context,
final int majorVersion, final int minorVersion) {
formatColumnList(context);
- return FreemarkerManager.getSqlByPgVersion(context,
"table/%s/create.ftl", majorVersion, minorVersion);
+ return FreemarkerManager.getSqlByPgVersion(context,
"table/%s/create.ftl", majorVersion, minorVersion).trim();
}
@SuppressWarnings("unchecked")
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresAbstractLoader.java
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresAbstractLoader.java
index 5910124d3ba..8e8eef54160 100644
---
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresAbstractLoader.java
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresAbstractLoader.java
@@ -21,15 +21,18 @@ import lombok.Getter;
import lombok.SneakyThrows;
import
org.apache.shardingsphere.data.pipeline.postgresql.util.FreemarkerManager;
+import java.sql.Array;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
+import java.util.Arrays;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Map;
+import java.util.stream.Collectors;
/**
* Postgres abstract loader.
@@ -37,6 +40,8 @@ import java.util.Map;
@Getter
public abstract class PostgresAbstractLoader {
+ private static final String SECURITY_LABEL_SPLIT = "=";
+
private final Connection connection;
private final int majorVersion;
@@ -70,4 +75,19 @@ public abstract class PostgresAbstractLoader {
}
return result;
}
+
+ protected void formatSecurityLabels(final Map<String, Object> data) throws
SQLException {
+ if (null == data.get("seclabels")) {
+ return;
+ }
+ Collection<Map<String, String>> formatLabels = new LinkedList<>();
+ Collection<String> securityLabels = Arrays.stream((String[]) ((Array)
data.get("seclabels")).getArray()).collect(Collectors.toList());
+ for (String each : securityLabels) {
+ Map<String, String> securityLabel = new LinkedHashMap<>();
+ securityLabel.put("provider", each.substring(0,
each.indexOf(SECURITY_LABEL_SPLIT)));
+ securityLabel.put("label",
each.substring(each.indexOf(SECURITY_LABEL_SPLIT) + 1));
+ formatLabels.add(securityLabel);
+ }
+ data.put("seclabels", formatLabels);
+ }
}
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresColumnPropertiesLoader.java
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresColumnPropertiesLoader.java
index 930d3dfe799..5aeb1969387 100644
---
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresColumnPropertiesLoader.java
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresColumnPropertiesLoader.java
@@ -43,6 +43,8 @@ public final class PostgresColumnPropertiesLoader extends
PostgresAbstractLoader
private static final Pattern BRACKETS_PATTERN =
Pattern.compile("(\\(\\d+\\))");
+ private static final String ATT_OPTION_SPLIT = "=";
+
public PostgresColumnPropertiesLoader(final Connection connection, final
int majorVersion, final int minorVersion) {
super(connection, majorVersion, minorVersion);
}
@@ -134,9 +136,11 @@ public final class PostgresColumnPropertiesLoader extends
PostgresAbstractLoader
return Arrays.stream((String[]) ((Array)
editTypes).getArray()).sorted(String::compareTo).collect(Collectors.toList());
}
- private void columnFormatter(final Map<String, Object> column, final
Collection<String> editTypes) {
+ private void columnFormatter(final Map<String, Object> column, final
Collection<String> editTypes) throws SQLException {
handlePrimaryColumn(column);
fetchLengthPrecision(column);
+ formatColumnVariables(column);
+ formatSecurityLabels(column);
editTypes.add(column.get("cltype").toString());
column.put("edit_types",
editTypes.stream().sorted().collect(Collectors.toList()));
column.put("cltype", parseTypeName(column.get("cltype").toString()));
@@ -201,6 +205,21 @@ public final class PostgresColumnPropertiesLoader extends
PostgresAbstractLoader
}
}
+ private void formatColumnVariables(final Map<String, Object> column)
throws SQLException {
+ if (null == column.get("attoptions")) {
+ return;
+ }
+ Collection<Map<String, String>> attOptions = new LinkedList<>();
+ Collection<String> columnVariables = Arrays.stream((String[]) ((Array)
column.get("attoptions")).getArray()).collect(Collectors.toList());
+ for (String each : columnVariables) {
+ Map<String, String> columnVariable = new LinkedHashMap<>();
+ columnVariable.put("name", each.substring(0,
each.indexOf(ATT_OPTION_SPLIT)));
+ columnVariable.put("value",
each.substring(each.indexOf(ATT_OPTION_SPLIT) + 1));
+ attOptions.add(columnVariable);
+ }
+ column.put("attoptions", attOptions);
+ }
+
private String getFullDataType(final Map<String, Object> column) {
String namespace = (String) column.get("typnspname");
String typeName = (String) column.get("typname");
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresIndexLoader.java
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresIndexLoader.java
new file mode 100644
index 00000000000..3a9260b0c85
--- /dev/null
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresIndexLoader.java
@@ -0,0 +1,175 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator;
+
+import lombok.SneakyThrows;
+import
org.apache.shardingsphere.data.pipeline.postgresql.util.FreemarkerManager;
+import org.postgresql.jdbc.PgArray;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Collection;
+import java.util.LinkedHashMap;
+import java.util.LinkedList;
+import java.util.Map;
+
+/**
+ * Postgres index loader.
+ */
+public final class PostgresIndexLoader extends PostgresAbstractLoader {
+
+ private static final Integer PG_INDEX_INCLUDE_VERSION = 11;
+
+ public PostgresIndexLoader(final Connection connection, final int
majorVersion, final int minorVersion) {
+ super(connection, majorVersion, minorVersion);
+ }
+
+ /**
+ * Load index sql.
+ *
+ * @param context context
+ * @return index sql
+ */
+ @SneakyThrows
+ public String loadIndexSql(final Map<String, Object> context) {
+ StringBuilder result = new StringBuilder();
+ Collection<Map<String, Object>> indexNodes = getIndexNodes(context);
+ for (Map<String, Object> each : indexNodes) {
+ if (each.containsKey("is_inherited") && (Boolean)
each.get("is_inherited")) {
+ continue;
+ }
+ result.append(getIndexSql(context, each));
+ }
+ return result.toString().trim();
+ }
+
+ private Collection<Map<String, Object>> getIndexNodes(final Map<String,
Object> context) {
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("tid", context.get("tid"));
+ return executeByTemplate(param, "indexes/%s/nodes.ftl");
+ }
+
+ private String getIndexSql(final Map<String, Object> context, final
Map<String, Object> indexNode) throws SQLException {
+ Map<String, Object> indexData = getIndexData(context, indexNode);
+ appendColumnDetails(indexData, (Long) indexNode.get("oid"));
+ if (getMajorVersion() >= PG_INDEX_INCLUDE_VERSION) {
+ appendIncludeDetails(indexData, (Long) indexNode.get("oid"));
+ }
+ return doGenerateIndexSql(indexData);
+ }
+
+ private String doGenerateIndexSql(final Map<String, Object> indexData) {
+ String result = FreemarkerManager.getSqlByPgVersion(indexData,
"indexes/%s/create.ftl", getMajorVersion(), getMinorVersion());
+ result += System.lineSeparator();
+ result += FreemarkerManager.getSqlByPgVersion(indexData,
"indexes/%s/alter.ftl", getMajorVersion(), getMinorVersion());
+ return result;
+ }
+
+ private Map<String, Object> getIndexData(final Map<String, Object>
context, final Map<String, Object> indexNode) {
+ Collection<Map<String, Object>> indexProperties =
fetchIndexProperties(context, indexNode);
+ Map<String, Object> result = indexProperties.iterator().next();
+ result.put("schema", context.get("schema"));
+ result.put("table", context.get("name"));
+ return result;
+ }
+
+ private Collection<Map<String, Object>> fetchIndexProperties(final
Map<String, Object> context, final Map<String, Object> indexNode) {
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("did", context.get("did"));
+ param.put("tid", context.get("tid"));
+ param.put("idx", indexNode.get("oid"));
+ param.put("datlastsysoid", context.get("datlastsysoid"));
+ return executeByTemplate(param, "indexes/%s/properties.ftl");
+ }
+
+ private void appendColumnDetails(final Map<String, Object> indexData,
final Long indexId) throws SQLException {
+ Collection<Map<String, Object>> columnDetails =
fetchColumnDetails(indexId);
+ Collection<Map<String, Object>> columns = new LinkedList<>();
+ Collection<String> columnDisplays = new LinkedList<>();
+ for (Map<String, Object> each : columnDetails) {
+ columns.add(getColumnData(indexData, each));
+ columnDisplays.add(getColumnPropertyDisplayData(each, indexData));
+ }
+ indexData.put("columns", columns);
+ indexData.put("columns_csv", String.join(", ", columnDisplays));
+ }
+
+ private Map<String, Object> getColumnData(final Map<String, Object>
indexData, final Map<String, Object> columnDetail) throws SQLException {
+ Map<String, Object> result = new LinkedHashMap<>();
+ result.put("colname", columnDetail.get("attdef"));
+ result.put("collspcname", columnDetail.get("collnspname"));
+ result.put("op_class", columnDetail.get("opcname"));
+ if ("btree".equals(indexData.get("amname"))) {
+ result.put("sort_order", isSortOrder(columnDetail));
+ result.put("nulls", isNulls(columnDetail));
+ }
+ return result;
+ }
+
+ private boolean isSortOrder(final Map<String, Object> columnDetail) throws
SQLException {
+ if (null != columnDetail.get("options")) {
+ String[] options = (String[]) ((PgArray)
columnDetail.get("options")).getArray();
+ return options.length > 0 && "DESC".equals(options[0]);
+ }
+ return false;
+ }
+
+ private Object isNulls(final Map<String, Object> columnDetail) throws
SQLException {
+ if (null != columnDetail.get("options")) {
+ String[] options = (String[]) ((PgArray)
columnDetail.get("options")).getArray();
+ return options.length > 1 && options[1].split(" ").length > 1 &&
"FIRST".equals(options[1].split(" ")[1]);
+ }
+ return false;
+ }
+
+ private Collection<Map<String, Object>> fetchColumnDetails(final Long
indexId) {
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("idx", indexId);
+ return executeByTemplate(param, "indexes/%s/column_details.ftl");
+ }
+
+ private String getColumnPropertyDisplayData(final Map<String, Object>
each, final Map<String, Object> indexData) throws SQLException {
+ String result = (String) each.get("attdef");
+ if (null != each.get("collnspname")) {
+ result += " COLLATE " + each.get("collnspname");
+ }
+ if (null != each.get("opcname")) {
+ result += " " + each.get("opcname");
+ }
+ if ("btree".equals(indexData.get("amname"))) {
+ String[] options = (String[]) ((PgArray)
each.get("options")).getArray();
+ if (options.length > 0) {
+ result += " " + options[0];
+ }
+ if (options.length > 1) {
+ result += " " + options[1];
+ }
+ }
+ return result;
+ }
+
+ private void appendIncludeDetails(final Map<String, Object> indexData,
final Long oid) {
+ Map<String, Object> param = new LinkedHashMap<>();
+ param.put("idx", oid);
+ Collection<Object> includes = new LinkedList<>();
+ for (Map<String, Object> each : executeByTemplate(param,
"indexes/%s/include_details.ftl")) {
+ includes.add(each.get("colname"));
+ }
+ indexData.put("include", includes);
+ }
+}
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresTablePropertiesLoader.java
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresTablePropertiesLoader.java
index 5f1f5806be4..698445ec6e3 100644
---
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresTablePropertiesLoader.java
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/java/org/apache/shardingsphere/data/pipeline/postgresql/ddlgenerator/PostgresTablePropertiesLoader.java
@@ -75,10 +75,11 @@ public final class PostgresTablePropertiesLoader extends
PostgresAbstractLoader
appendFirstRow(executeByTemplate(parameters,
"table/%s/get_schema_id.ftl"), context);
}
- private void fetchTableProperties(final Map<String, Object> context) {
+ private void fetchTableProperties(final Map<String, Object> context)
throws SQLException {
appendFirstRow(executeByTemplate(context, "table/%s/properties.ftl"),
context);
updateAutovacuumProperties(context);
checkRlspolicySupport(context);
+ formatSecurityLabels(context);
}
private void updateAutovacuumProperties(final Map<String, Object> context)
{
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/11_plus/column_details.ftl
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/11_plus/column_details.ftl
new file mode 100644
index 00000000000..7b929ef627d
--- /dev/null
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/11_plus/column_details.ftl
@@ -0,0 +1,48 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT
+i.indexrelid,
+CASE i.indoption[i.attnum - 1]
+WHEN 0 THEN ARRAY['ASC', 'NULLS LAST']
+WHEN 1 THEN ARRAY['DESC', 'NULLS LAST']
+WHEN 2 THEN ARRAY['ASC', 'NULLS FIRST']
+WHEN 3 THEN ARRAY['DESC', 'NULLS FIRST']
+ELSE ARRAY['UNKNOWN OPTION' || i.indoption[i.attnum - 1]::text, '']
+END::text[] AS options,
+i.attnum,
+pg_catalog.pg_get_indexdef(i.indexrelid, i.attnum, true) as attdef,
+CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname,
+op.oprname AS oprname,
+CASE WHEN length(nspc.nspname::text) > 0 AND length(coll.collname::text) > 0
THEN
+pg_catalog.concat(pg_catalog.quote_ident(nspc.nspname), '.',
pg_catalog.quote_ident(coll.collname))
+ELSE '' END AS collnspname
+FROM (
+SELECT
+indexrelid, i.indoption, i.indclass,
+pg_catalog.unnest(ARRAY(SELECT pg_catalog.generate_series(1, i.indnkeyatts) AS
n)) AS attnum
+FROM
+pg_catalog.pg_index i
+WHERE i.indexrelid = ${idx?c}::OID
+) i
+LEFT JOIN pg_catalog.pg_opclass o ON (o.oid = i.indclass[i.attnum - 1])
+LEFT OUTER JOIN pg_catalog.pg_constraint c ON (c.conindid = i.indexrelid)
+LEFT OUTER JOIN pg_catalog.pg_operator op ON (op.oid = c.conexclop[i.attnum])
+LEFT JOIN pg_catalog.pg_attribute a ON (a.attrelid = i.indexrelid AND a.attnum
= i.attnum)
+LEFT OUTER JOIN pg_catalog.pg_collation coll ON a.attcollation=coll.oid
+LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
+ORDER BY i.attnum;
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/11_plus/create.ftl
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/11_plus/create.ftl
new file mode 100644
index 00000000000..929951732c5
--- /dev/null
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/11_plus/create.ftl
@@ -0,0 +1,30 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+CREATE <#if indisunique!false >UNIQUE </#if>INDEX IF NOT EXISTS <#if
isconcurrent!false >CONCURRENTLY </#if>${name}
+ON ${schema}.${table} <#if amname?? >USING ${amname}</#if>
+(<#list columns as c><#if c?counter != 1 >, </#if>${c.colname}<#if
c.collspcname?? && c.collspcname?length gt 0 > COLLATE
${c.collspcname}</#if><#if c.op_class?? >
+${c.op_class}</#if><#if c.sort_order?? ><#if c.sort_order > DESC<#else>
ASC</#if></#if><#if c.nulls?? > NULLS <#if c.nulls >
+FIRST<#else>LAST</#if></#if></#list>)
+<#if include?size gt 0 >
+INCLUDE(<#list include as col ><#if col?counter != 1 >, </#if>${col}</#list>)
+</#if>
+<#if fillfactor?? >
+WITH (FILLFACTOR=${fillfactor})
+</#if><#if spcname?? >
+TABLESPACE ${spcname}</#if><#if indconstraint?? >
+WHERE ${indconstraint}</#if>;
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/11_plus/include_details.ftl
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/11_plus/include_details.ftl
new file mode 100644
index 00000000000..76ab05cf5cd
--- /dev/null
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/11_plus/include_details.ftl
@@ -0,0 +1,31 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT a.attname as colname
+FROM (
+SELECT
+i.indnkeyatts,
+i.indrelid,
+pg_catalog.unnest(indkey) AS table_colnum,
+pg_catalog.unnest(ARRAY(SELECT pg_catalog.generate_series(1, i.indnatts) AS
n)) attnum
+FROM
+pg_catalog.pg_index i
+WHERE i.indexrelid = ${idx?c}::OID
+) i JOIN pg_catalog.pg_attribute a
+ON (a.attrelid = i.indrelid AND i.table_colnum = a.attnum)
+WHERE i.attnum > i.indnkeyatts
+ORDER BY i.attnum
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/alter.ftl
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/alter.ftl
new file mode 100644
index 00000000000..de24cf5bb35
--- /dev/null
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/alter.ftl
@@ -0,0 +1,24 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+<#if indisclustered!false >
+ALTER TABLE IF EXISTS ${schema}.${table}
+CLUSTER ON ${name};
+</#if>
+<#if description?? && description?length gt 0>
+COMMENT ON INDEX ${schema}.${name}
+IS '${description}';</#if>
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/column_details.ftl
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/column_details.ftl
new file mode 100644
index 00000000000..3da60440f28
--- /dev/null
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/column_details.ftl
@@ -0,0 +1,48 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT
+i.indexrelid,
+CASE i.indoption[i.attnum - 1]
+WHEN 0 THEN ARRAY['ASC', 'NULLS LAST']
+WHEN 1 THEN ARRAY['DESC', 'NULLS LAST']
+WHEN 2 THEN ARRAY['ASC', 'NULLS FIRST']
+WHEN 3 THEN ARRAY['DESC', 'NULLS FIRST']
+ELSE ARRAY['UNKNOWN OPTION' || i.indoption[i.attnum - 1]::text, '']
+END::text[] AS options,
+i.attnum,
+pg_catalog.pg_get_indexdef(i.indexrelid, i.attnum, true) as attdef,
+CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname,
+op.oprname AS oprname,
+CASE WHEN length(nspc.nspname::text) > 0 AND length(coll.collname::text) > 0
THEN
+pg_catalog.concat(pg_catalog.quote_ident(nspc.nspname), '.',
pg_catalog.quote_ident(coll.collname))
+ELSE '' END AS collnspname
+FROM (
+SELECT
+indexrelid, i.indoption, i.indclass,
+pg_catalog.unnest(ARRAY(SELECT pg_catalog.generate_series(1, i.indnatts) AS
n)) AS attnum
+FROM
+pg_catalog.pg_index i
+WHERE i.indexrelid = ${idx?c}::OID
+) i
+LEFT JOIN pg_catalog.pg_opclass o ON (o.oid = i.indclass[i.attnum - 1])
+LEFT OUTER JOIN pg_catalog.pg_constraint c ON (c.conindid = i.indexrelid)
+LEFT OUTER JOIN pg_catalog.pg_operator op ON (op.oid = c.conexclop[i.attnum])
+LEFT JOIN pg_catalog.pg_attribute a ON (a.attrelid = i.indexrelid AND a.attnum
= i.attnum)
+LEFT OUTER JOIN pg_catalog.pg_collation coll ON a.attcollation=coll.oid
+LEFT OUTER JOIN pg_catalog.pg_namespace nspc ON coll.collnamespace=nspc.oid
+ORDER BY i.attnum;
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/create.ftl
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/create.ftl
new file mode 100644
index 00000000000..dbcb09e4036
--- /dev/null
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/create.ftl
@@ -0,0 +1,27 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+CREATE <#if indisunique!false >UNIQUE </#if>INDEX IF NOT EXISTS <#if
isconcurrent!false >CONCURRENTLY </#if>${name}
+ON ${schema}.${table} <#if amname?? >USING ${amname}</#if>
+(<#list columns as c><#if c?counter != 1 >, </#if>${c.colname}<#if
c.collspcname?? && c.collspcname?length gt 0> COLLATE
${c.collspcname}</#if><#if c.op_class?? >
+${c.op_class}</#if><#if c.sort_order?? ><#if c.sort_order > DESC<#else>
ASC</#if></#if><#if c.nulls?? > NULLS <#if c.nulls >
+FIRST<#else>LAST</#if></#if></#list>)
+<#if fillfactor?? >
+WITH (FILLFACTOR=${fillfactor})
+</#if><#if spcname?? >
+TABLESPACE ${spcname}</#if><#if indconstraint?? >
+WHERE ${indconstraint}</#if>;
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/nodes.ftl
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/nodes.ftl
new file mode 100644
index 00000000000..0338370bb2f
--- /dev/null
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/nodes.ftl
@@ -0,0 +1,30 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name,
+(SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM
pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited
+FROM pg_catalog.pg_index idx
+JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
+JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
+LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
+JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
+JOIN pg_catalog.pg_am am ON am.oid=cls.relam
+LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND
dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid
FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid
AND con.oid = dep.refobjid)
+WHERE indrelid = ${tid?c}::OID
+AND conname is NULL
+ORDER BY cls.relname
diff --git
a/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/properties.ftl
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/properties.ftl
new file mode 100644
index 00000000000..ffff2d13543
--- /dev/null
+++
b/shardingsphere-kernel/shardingsphere-data-pipeline/shardingsphere-data-pipeline-dialect/shardingsphere-data-pipeline-postgresql/src/main/resources/template/indexes/default/properties.ftl
@@ -0,0 +1,45 @@
+<#--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+ -->
+
+SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as name, indrelid,
indkey, indisclustered,
+indisvalid, indisunique, indisprimary, n.nspname,indnatts,cls.reltablespace AS
spcoid,
+CASE WHEN (length(spcname::text) > 0 OR cls.relkind = 'I') THEN spcname ELSE
+(SELECT sp.spcname FROM pg_catalog.pg_database dtb
+JOIN pg_catalog.pg_tablespace sp ON dtb.dattablespace=sp.oid
+WHERE dtb.oid = ${ did?c }::oid)
+END as spcname,
+tab.relname as tabname, indclass, con.oid AS conoid,
+CASE WHEN contype IN ('p', 'u', 'x') THEN desp.description
+ELSE des.description END AS description,
+pg_catalog.pg_get_expr(indpred, indrelid, true) as indconstraint, contype,
condeferrable, condeferred, amname,
+(SELECT (CASE WHEN count(i.inhrelid) > 0 THEN true ELSE false END) FROM
pg_inherits i WHERE i.inhrelid = cls.oid) as is_inherited,
+substring(pg_catalog.array_to_string(cls.reloptions, ',') from
'fillfactor=([0-9]*)') AS fillfactor
+<#if datlastsysoid?? >, (CASE WHEN cls.oid <= ${ datlastsysoid?c}::oid THEN
true ElSE false END) AS is_sys_idx </#if>
+FROM pg_catalog.pg_index idx
+JOIN pg_catalog.pg_class cls ON cls.oid=indexrelid
+JOIN pg_catalog.pg_class tab ON tab.oid=indrelid
+LEFT OUTER JOIN pg_catalog.pg_tablespace ta on ta.oid=cls.reltablespace
+JOIN pg_catalog.pg_namespace n ON n.oid=tab.relnamespace
+JOIN pg_catalog.pg_am am ON am.oid=cls.relam
+LEFT JOIN pg_catalog.pg_depend dep ON (dep.classid = cls.tableoid AND
dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid
FROM pg_catalog.pg_class WHERE relname='pg_constraint') AND dep.deptype='i')
+LEFT OUTER JOIN pg_catalog.pg_constraint con ON (con.tableoid = dep.refclassid
AND con.oid = dep.refobjid)
+LEFT OUTER JOIN pg_catalog.pg_description des ON (des.objoid=cls.oid AND
des.classoid='pg_class'::regclass)
+LEFT OUTER JOIN pg_catalog.pg_description desp ON (desp.objoid=con.oid AND
desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)
+WHERE indrelid = ${tid?c}::OID
+AND conname is NULL
+<#if idx?? >AND cls.oid = ${idx?c}::OID </#if>
+ORDER BY cls.relname
