一些SQL小技巧，不定期更新...

PostgreSQL中除IN多参数写法 之 ANY[ARRAY]

Postgresql的in条件查询默认最大参数个数1000
PostgreSQL对于”IN”条件中的参数数量有一定的限制。
这个限制是由可配置的参数 max_expr 控制的
unnest 函数用于将数组展开成一个或多个元素的行集。它接受一个数组作为参数，并返回该数组中的每个元素作为结果集中的一行。使得 ANY 操作符能够将主查询中的列与数组中的值进行比较。

SELECT *
FROM table_name AS a
WHERE a.column = ANY
      (SELECT unnest(ARRAY ['aa', 'test_2', 'ttt', 'b_duration_date']));

Mybatis中写法
select
        <include refid="findAllColumn"/>,a.business_system_range_code_value_id
        from main_system_range_value as a
        <where>
            a.state = '1' and a.dic_group is not null
            <if test="@com.common.util.ObjectHelper@isNotEmpty(dto.dicGroups)">
                and a.dic_group = ANY
                (SELECT unnest(ARRAY
                <foreach collection="dto.dicGroups" item="dicGroup" open="[" close="]" separator=",">
                    #{dicGroup}
                </foreach>
                ))
            </if>
        </where>

要将查询结果作为IN语句的查询条件，查询结果作为子查询，并在主查询中使用IN子句。

SELECT table_ids
    FROM cdc_table_sync
    WHERE sync_id = 'FLINK_CDC_SYNC_TABLE_198';

结果为：c2a9f78ecc3f6a6c97c12956d25e1adc,24d883d4a419578d09dd3801f001b93e,f3e569a5c68023c76b5bb22e25768739,df9e3fe91219ce1d042ee84a0b4b58ea,465d014da17e858fd16bca733141126a,6572ff4511b9164bffb1bfbc480d364e
这样的字符串

SELECT *
FROM your_table
WHERE column_name IN (
  SELECT split_part(result, ',', generate_series(1, array_length(string_to_array(result, ','), 1))) AS element
  FROM (
    SELECT string_agg(quote_literal(table_ids) || ',', '') AS result
    FROM cdc_table_sync
    WHERE sync_id = 'FLINK_CDC_SYNC_TABLE_198'
  ) subquery
);

your_table替换表名，column_name`替换为进行IN匹配的列名。

这里将首先使用之前提供的查询语句，将查询结果以逗号分隔并加上单引号。然后，使用string_to_array函数将结果拆分为一个数组。接下来，通过generate_series函数生成一个序列，用来获取数组中的每个元素。最后，使用split_part函数将每个元素分别作为子查询的结果，并在主查询中使用IN子句。

当主查询执行时，它将比对指定列的值与子查询的结果，如果匹配则返回相应的行。

查询PostgreSQL 唯一键等

-- SELECT 子句后的内容是要检索的字段，包括约束名称、表名、列名、外键表名、外键列名、是否可延迟、初始延迟等。
SELECT tc.constraint_name,
       tc.table_name,
       kcu.column_name,
       ccu.table_name  AS foreign_table_name,
       ccu.column_name AS foreign_column_name,
       tc.is_deferrable,
       tc.initially_deferred
FROM information_schema.table_constraints AS tc
         JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
         JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'UNIQUE'

-- AND tc.table_name = 'table_name';

constraint_type有四种：UNIQUE、PRIMARY KEY、CHECK、FOREIGN KEY, 通过修改上边sql语句的table_name和constraint_type来进行相应的查询。

ALTER TABLE table_name
    ADD CONSTRAINT uk_users_name1 UNIQUE (NAME);
ALTER TABLE table_name
    DROP CONSTRAINT IF EXISTS uk_users_name1;
;

查询PG数据库的默认表空间

SELECT datname AS database_name, spcname AS default_tablespace
FROM pg_database
JOIN pg_tablespace ON pg_database.dattablespace = pg_tablespace.oid;

-- 查询所有库
SELECT datname
FROM pg_database
WHERE datistemplate = false;
-- 查询所有模式
SELECT nspname AS schema_name
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
  AND nspname != 'information_schema';

-- 查询该模式下是否为分区
SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       c.relhassubclass
FROM pg_catalog.pg_class c
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE (c.relkind = 'r' OR c.relkind = 'p') -- 只选择普通表和分区表
  AND n.nspname NOT LIKE 'pg_%'            -- 过滤掉以 "pg_" 开头的模式
  AND n.nspname <> 'information_schema'    -- 过滤掉 information_schema 模式
  AND (CASE WHEN c.relispartition THEN true ELSE false END) = false
ORDER BY schema_name, table_name;

-- 查询主键唯一键
SELECT information_schema.tables.table_schema,
       information_schema.tables.table_name,
       (CASE WHEN information_schema.table_constraints.constraint_name IS NOT NULL THEN true ELSE false END) AS has_primary_key_or_unique_key
FROM information_schema.tables
         LEFT JOIN information_schema.table_constraints ON information_schema.tables.table_name = information_schema.table_constraints.table_name
WHERE information_schema.tables.table_schema = 'public' -- 替换为你要查询的模式
  AND information_schema.tables.table_catalog = 'drug'  -- 替换为你要查询的数据库
  AND (information_schema.table_constraints.constraint_type = 'PRIMARY KEY' OR information_schema.table_constraints.constraint_type = 'UNIQUE');


SELECT information_schema.tables.table_schema,
       information_schema.tables.table_name,
       (CASE WHEN information_schema.table_constraints.constraint_name IS NOT NULL THEN true ELSE false END) AS hasPrimaryKeyOrUniqueKey,
       c.relhassubclass
FROM information_schema.tables
         LEFT JOIN information_schema.table_constraints ON information_schema.tables.table_name = information_schema.table_constraints.table_name
         LEFT JOIN pg_catalog.pg_class c ON information_schema.tables.table_name = c.relname
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE (information_schema.table_constraints.constraint_type = 'PRIMARY KEY' OR information_schema.table_constraints.constraint_type = 'UNIQUE')
  AND (c.relkind = 'r' OR c.relkind = 'p') -- 只选择普通表和分区表
  AND n.nspname NOT LIKE 'pg_%'            -- 过滤掉以 "pg_" 开头的模式
  AND n.nspname <> 'information_schema'    -- 过滤掉 information_schema 模式
  AND (CASE WHEN c.relispartition THEN true ELSE false END) = false
ORDER BY table_schema, table_name;

-- 组合
SELECT distinct information_schema.tables.table_catalog,
                information_schema.tables.table_schema,
                information_schema.tables.table_name,
                (CASE WHEN information_schema.table_constraints.constraint_name IS NOT NULL THEN true ELSE false END) AS hasPrimaryKeyOrUniqueKey,
                c.relhassubclass
FROM information_schema.tables
         LEFT JOIN information_schema.table_constraints ON information_schema.tables.table_name = information_schema.table_constraints.table_name
         LEFT JOIN pg_catalog.pg_class c ON information_schema.tables.table_name = c.relname
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE (information_schema.table_constraints.constraint_type = 'PRIMARY KEY' OR information_schema.table_constraints.constraint_type = 'UNIQUE')
  AND (c.relkind = 'r' OR c.relkind = 'p') -- 只选择普通表和分区表
  AND n.nspname NOT LIKE 'pg_%'            -- 过滤掉以 "pg_" 开头的模式
  AND n.nspname <> 'information_schema'    -- 过滤掉 information_schema 模式
  AND (CASE WHEN c.relispartition THEN true ELSE false END) = false
ORDER BY table_schema, table_name;