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

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

SELECT table_ids
    FROM cdc_table_sync
    WHERE sync_id = 'FLINK_CDC_SYNC_TABLE_198';
1
2
3

结果为：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
);
1
2
3
4
5
6
7
8
9
10

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';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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;
;
1
2
3
4
5

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

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

-- 查询所有库
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;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66