本篇博客是对一些比较常见的数据库知识的汇总,并会持续更新。
Postgres
如何获取postgres所有的表里的记录条数?
SELECT
nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname NOT IN ('pg_catalog', 'information_schema') AND
relkind='r'
ORDER BY reltuples DESC;
如何获取postgres里某个表的主键?
public List<String> getPrimaryKeysByTable(String tableName, JdbcTemplate jdbcTemplate) {
String retrievePrimaryKeySql =
"select kc.column_name from information_schema.table_constraints tc join information_schema.key_column_usage kc on kc.table_name = \'"
+ tableName
+ "\' and kc.table_schema = \'public\' and kc.constraint_name = tc.constraint_name where tc.constraint_type