PostgreSQL 业务使用手册 #25

GRANT CONNECT ON DATABASE demo_web TO demo_web_ro;

GRANT USAGE ON SCHEMA public TO demo_web_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO demo_web_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO demo_web_ro;

CREATE UNIQUE INDEX ad_advertisers_pkey ON public.ad_advertisers USING btree (app_id, channel_name, account_id);

=>

ALTER TABLE public.ad_advertisers DROP CONSTRAINT ad_advertisers_pkey;
UPDATE ad_advertisers SET account_type = '' WHERE account_type IS NULL;
ALTER TABLE public.ad_advertisers ADD CONSTRAINT ad_advertisers_pkey PRIMARY KEY (app_id, channel_name, account_type, account_id);

SELECT pg_sequence_last_value(
  pg_get_serial_sequence('users', 'id')::regclass
);

ALTER SEQUENCE users_id_seq RESTART WITH 7;

-- 根据表已有的数据进行重置
SELECT setval('users_id_seq', (SELECT coalesce(max(id), 0) FROM users));

pg_dump -h localhost -p 5432 -U postgres -d db_name -t table_name --schema-only

pg_dump --schema-only -h localhost -p 5432 -U postgres -d db_name --schema-only --no-owner --no-privileges -f schema.sql

-- 查看每个表的占用空间大小
select
  table_name, 
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name))),
  pg_size_pretty(pg_relation_size(quote_ident(table_name)))
from information_schema.tables
where table_schema = 'public'
order by pg_total_relation_size(quote_ident(table_name)) desc;

-- 查看每个表的行数
SELECT 
    schemaname AS schema,
    relname AS table,
    n_live_tup AS estimated_row_count
FROM 
    pg_stat_user_tables
ORDER BY 3 desc;

-- 查看某个数据库磁盘占用情况
select pg_size_pretty(pg_database_size('funnydb_web'));

-- 查看全部数据库磁盘占用情况
SELECT datname as db_name, pg_size_pretty(pg_database_size(datname)) as db_usage FROM pg_database order by pg_database_size(datname) desc;

select pg_typeof("column1"), pg_typeof("column2") from table1;

SELECT value, value::numeric FROM property_values where value ~ '^[-0-9.]+$' limit 1000;

select min(id) from (select '10005' as id union all select '995' as id);  -- 错误写法

create table users_old as (select * from users);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price DECIMAL(10,2),
    quantity INTEGER,
    total_price DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) STORED
);

SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name = 'xxx';

SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);

UPDATE users
SET sso_user_id = sso_users.id
FROM sso_users
WHERE users.email = sso_users.login_name and sso_users.id is not null;

SELECT
    pid,
    usename,
    datname,
    client_addr,
    state,
    query_start,
    now() - query_start AS runtime,
    query
FROM
    pg_stat_activity
WHERE
    state <> 'idle'
ORDER BY
    query_start;

SELECT pg_cancel_backend(pid);