postgreSQL 使用笔记（本人公司用的是arterydb,分装的postgreSQL）

最新推荐文章于 2024-09-22 07:10:50 发布

一棵棵大树

最新推荐文章于 2024-09-22 07:10:50 发布

阅读量2.2k

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/u011598529/article/details/44195977

版权

数据库专栏收录该内容

27 篇文章 0 订阅

订阅专栏

--启动数据库
./bin/aty_ctl -D data -l arterydb.log start
--连接数据库
./bin/atysql -d artery
--设置环境变量
export LD_LIBRARY_PATH='/home/arterydb/lib'
--删除表
DROP TABLE table_name;
--查询表是否存在
select count(*) into FYDM from information_schema.tables where table_schema='db_fy' and table_type='BASE TABLE' and table_name='t_aydm_map';

--创建函数
CREATE OR REPLACE FUNCTION Juge_Delete_table() RETURNS void AS
$$
DECLARE
FYDM INT;
begin
      select count(*) into FYDM from information_schema.tables where table_schema='db_fy' and table_type='BASE TABLE' and table_name='t_aydm_map';
      if FYDM>0 then
         drop table db_fy.t_aydm_map;
      end if;
end
$$
language platysql;
--调用函数
select Juge_Delete_table();
--查询索引
select count(*) from aty_indexes where indexname = 'i_jg_fy'; --查询条件必须小写
--创建角色及权限
CREATE ROLE admin WITH CREATEDB CREATEROLE;
--更改密码
ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
此部分详见公司内网（http://172.16.6.233/ArteryDB/sql-createrole.html）
--创建索引
create index I_JG_FY on DB_FY.T_JG (N_FY);
--删除索引
drop index db_fy.i_jg_fy;(索引是对应的,创建db_fy下的索引,对应删除要删shcame"db_fy"下的索引;不指定schame则删除也不用指定)
--查找表数量
select count(*) from aty_tables where schemaname like '%imdb%';

sybase->arterydb类型转换
binary->bytea
image->bytea
tinyint->smallint
datetime->timestamp
执行sql文件
./bin/atysql -h localhost -d artery -U arterydb -f
                               /home/shsql/ods2etl/D_MSAJLX_ZH.txt;
bytea类型插入图片
创建存储过程
   create or replace function bytea_import(p_path text, p_result out bytea)as
   $$
   declare
      l_oid oid;
      r record;
   begin
      p_result := '';
      select lo_import(p_path) into l_oid;
      for r in ( select data
                 from aty_largeobject
                 where loid = l_oid
                 order by pageno ) loop
        p_result = p_result || r.data;
      end loop;
      perform lo_unlink(l_oid);
   end
   $$
   language platysql;
执行插入语句
insert into db_fy.aaa(a) select bytea_import('/home/arterydb/untitled.png');

查找固定表在哪个字段
   SELECT
      c.relname,
      col_description (a.attrelid, a.attnum) AS COMMENT,
      format_type (a.atttypid, a.atttypmod) AS TYPE,
      a.attname AS NAME,
      a.attnotnull AS notnull
   FROM
    aty_class AS c,
      aty_attribute AS a
   WHERE a.attrelid = c.oid
      AND a.attnum > 0
      AND a.attname = 'n_ajbs'
查看表结构
   SELECT
attname,typname,adsrc
     FROM
           aty_attribute
           INNER JOIN aty_class ON aty_attribute.attrelid = aty_class.oid
           INNER JOIN aty_type   ON aty_attribute.atttypid = aty_type.oid
           LEFT OUTER JOIN aty_attrdef ON aty_attrdef.adrelid = aty_class.oid AND aty_attrdef.adnum = aty_attribute.attnum
           LEFT OUTER JOIN aty_description ON aty_description.objoid = aty_class.oid AND aty_description.objsubid = aty_attribute.attnum
     WHERE
           aty_attribute.attnum > 0
          AND attisdropped <> 't'
           AND aty_class.relname= 't_aydm' --t_aydm为表名
     ORDER BY aty_attribute.attnum ;
查看当前数据库连接数
SELECT aty_stat_get_backend_pid(s.backendid) AS procpid,
       aty_stat_get_backend_activity(s.backendid) AS current_query
    FROM (SELECT aty_stat_get_backend_idset() AS backendid) AS s;
数字转字符串
select to_char(1252323, '9999999999999');
select to_char(current_timestamp, 'YY-MM-DD HH12:MI:SS');
arterydb重启数据库
设置环境变量
export ATYDATA="/home/arterydb/data"
重启