pg 数据库 查询 表的元信息

SELECT
   A.attname AS name,format_type (A.atttypid,A.atttypmod) AS type,col_description (A.attrelid,A.attnum) AS comment,
    D.column_default,
   CASE WHEN length(B.attname) > 0 THEN 'PRI' ELSE '' END AS key
FROM
   pg_attribute A
LEFT JOIN (
    SELECT
        pg_attribute.attname
    FROM
        pg_index,
        pg_class,
        pg_attribute
    WHERE
        pg_class.oid ='"dept"' :: regclass
    AND pg_index.indrelid = pg_class.oid
    AND pg_attribute.attrelid = pg_class.oid
    AND pg_attribute.attnum = ANY (pg_index.indkey)
) B ON A.attname = b.attname
INNER JOIN pg_class C on A.attrelid = C.oid
INNER JOIN information_schema.columns D on A.attname = D.column_name
WHERE A.attrelid ='"dept"' :: regclass AND A.attnum> 0 AND NOT A.attisdropped AND D.table_name = 'dept'
ORDER BY A.attnum;

postgres 对数据类型敏感

select * from emp where hire_date=cast('2023-05-01' as date)
在mybatis 中   字符串转时间 必须使用 cast 强转

获取日期相差的天数

select date_part('day' ,to_timestamp('2023-05-29','yyyy-MM-dd'))- date_part('day',to_timestamp('2023-05-12','yyyy-MM-dd')) days

函数

--  日期函数
select current_date,current_time,current_timestamp;
--- 日期加减
select current_date+1;
select current_timestamp + interval '1 hour';
select current_date + interval '1 day';
-- 日期 字符串互转
select to_date('2020-01-01','yyyy-MM-dd');
select to_char(t.create_date,'yyyy-MM-dd') from t_info  t where t.code='001'  and id =1;

select to_timestamp('2020-01-01 12:00:01','yyyy-MM-dd hh24:mi:ss');
-- 时间格式化
Select to_char(now(), 'yyyy-mm-dd hh24:mi:ss')
---获取当前月份的第一天
select date_trunc('month',current_date)::DATE;
--根据第三步的内容，我们还可以获取上一月/年的最后一天
select (date_trunc('month',current_date) - interval'1 day')::DATE

模糊查询

select * from emp
where ename like  any (array['张%','孙%'])

树形查询

WITH RECURSIVE  temp_table  as (
     select id,pid from sys_company
     where pid=#{id}
    union all
       select b.id, a.pid from temp_table a


           join  sys_company  as b
     on b.pid=a.id
) select * from temp_table


;

 -- 根据父节点 查询所有的子孙节点

常用脚本

select * from t_test;
--  增加新的字段
alter table t_test add column  status integer null ;
--   删除字段
ALTER TABLE t_test
    DROP COLUMN status;
-- 修改字段类型
ALTER TABLE t_test ALTER COLUMN describtion TYPE text ;
--  设置 字段 为 非空
ALTER TABLE t_test ALTER COLUMN sex SET NOT NULL;
--  修改 表字段 重命名
ALTER TABLE t_test RENAME COLUMN name TO t_name;

-- 给表中某列 ADD UNIQUE CONSTRAINT（ 添加 UNIQUE 约束），
-- ALTER TABLE table_name
-- ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
-- 给表中 ADD CHECK CONSTRAINT（添加 CHECK 约束）
--ALTER TABLE table_name
--ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);


-- 给表 ADD PRIMARY KEY（添加主键），语法如下：
--ALTER TABLE table_name
-- ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
-- DROP CONSTRAINT （删除约束），语法如下：
--ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;

--- 
-- 清空表数据
TRUNCATE table t_test ;
--  从子查询中获取数据源并插入
insert into  t_test_copy1 select * from t_test;


--  在复制表结构的时候还可以带上数据。不过这种复制会丢失原有表的注释、约束等信息。
create table t_test_bk  as select * from t_test

自连接 查询 当前 人的上级

SELECT e.*,ti.ename FROM emp e
left join emp ti on e.leader = ti.id
;

临时解决问题

--  产生 1 -9  d的 序列
select * from generate_series(1,10);
--  UUID
select  gen_random_uuid();
select t1.* ,t2.create_date  last_date from t_info t1, (
                         select * from (
              select t.* ,
row_number() over( partition by code order by create_date) rno
from  t_info t


                  ) t1


where rno =1


    ) t2
where t1.code =  t2.code
;

--------------------------------------------------------------------------------




select q.*,
       date_part('day' ,last_date)- date_part('day',create_date) days
       from (
              select t1.*,
(select t.create_date from  t_info t where t1.code=t.code  order by create_date limit 1 ) last_date


from t_info  t1


order by  t1.id asc


                  )  q
;
----------------------‘’
select t.*,
       case when (  (select count(1) from t_slave  s where s.mid=t.id and t.create_time<=s.create_date))>0
        then '是'
        else '否'end res
       from t_master t;
----------------- with 用法
with t as


    (select * from tb_branch_level),
    t1 as (
        select * from t  where t.id=1
    );
insert into tb_branch_level_copy select * from t1;

排序相关 如果某个字段为空 则分页和不分页的数据顺序是不一致呢

nulls last   把空字段放在最后

select * from  user_info


order by tsDate desc nulls last ,id desc
########### https://blog.csdn.net/comeonyangzi/article/details/123322299

分页

---   获取第一条
select * from users limit 1;
select * from users
fetch first 1 rows only
;

----------
select * from users


offset 2 rows
fetch first 2 rows only
;