PostgreSQL

docker安装pgsql

docker run --name postgres-dev -e TZ=PRC -e POSTGRES_USER=root -e POSTGRES_DB=database -e POSTGRES_PASSWORD=Root123 -p 5433:5432 -v pgdata:/var/lib/postgresql/data -d postgres

创键表时需要注意主键di的问题 类型为bigserial

Name
smallserial:2 bytes,1 to 32767
serial:4 bytes,1t,o 2147483647
bigserial:8 bytes,1to 9223372036854775807

DROP TABLE IF EXISTS "public"."record";
CREATE TABLE "public"."record" (
  "id" bigserial,
  "create_time" timestamp(0),
  "update_time" timestamp(0),
  "test" varchar(255) COLLATE "pg_catalog"."default",
  "test_text" text COLLATE "pg_catalog"."default"
);
 
GRANT ALL PRIVILEGES ON all tables in schema public TO xxxx;
GRANT ALL ON ALL SEQUENCES in schema public TO xxxx;  

如果在history表有一个list字段类型varchar内容类似[{"Id":358579,"status":"SUCCESS"}]取出Id的内容 sql: SELECT (jsonb_array_elements(list::jsonb)->>'Id')::integer AS Id FROM history;

查某一个库里面所有的表和表里有几条数据

SELECT table_catalog, 
       table_schema, 
       table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::int AS row_count
FROM (
  SELECT table_catalog, 
         table_schema, 
         table_name, 
         query_to_xml(format('SELECT count(*) as cnt FROM %I.%I.%I', table_catalog, table_schema, table_name), false, true, '') AS xml_count
  FROM information_schema.tables
  WHERE table_schema = 'public'
) AS counts
ORDER BY row_count DESC;

查询所有序列sequence的名字和序列的最新值

SELECT schemaname,
       sequencename,
       last_value
FROM pg_sequences;