pgsql对时间做格式化统计 将时间戳转换成可视化时间格SELECT *, mc_db_time, fetch_time, ( mc_db_time :: BIGINT - fetch_time :: BIGINT ) AS sum_time, to_char( to_timestamp( fetch_time :: BIGINT ), 'yyyy-MM-dd hh24:MI:ss' ) AS format_fetch_time FROM "public"."log_history" WHERE "table_name" = 'user_master' AND "primary_id" LIKE'%data-sync-%' ORDER BY fetch_time DESC LIMIT 100;针对时间做格式化筛选SELECT COUNT ( user_vuclip_accounts.* ), EXTRACT ( DAY FROM user_vuclip_accounts.created_at ) log_day FROM user_vuclip_accounts WHERE EXTRACT ( YEAR FROM user_vuclip_accounts.created_at ) = EXTRACT ( YEAR FROM NOW( ) ) AND EXTRACT ( month FROM user_vuclip_accounts.created_at ) = EXTRACT ( month FROM NOW( ) - interval '1 month' ) GROUP BY EXTRACT ( DAY FROM user_vuclip_accounts.created_at );

postgrsql update， 给指定字段批量加前缀 使用||连接update user_accounts set principal = ('2022-09-06-' || principal) where user_id NOT IN (select id from users) ;

postgresql 删除重复数据 方法一 普通方法DELETE FROM bookmarks A WHERE A.ID NOT IN ( SELECT MIN ( ID ) FROM bookmarks GROUP BY "user_id", "product_id", "is_from_vuclip" );方法二 速度快DELETE FROM bookmarks A WHERE A.ID = ANY ( ARRAY ( SELECT ID FROM ( SELECT ROW_NUMBER ( ) OVER ( PARTITION BY "user_id", "product_id", "is_from_vuclip" ), ID FROM bookmarks ) T WHERE T.ROW_NUMBER > 1 ) );

创建postgres帐号 切换postgres用户sudo -i -u postgres进入Postgres数据库psql创建root帐号create user root with login password '123456';将root帐号提升为超级帐号ALTER USER root WITH SUPERUSER;