postgreSQL 使用筆記（本人公司用的是arterydb,分裝的postgreSQL）

標籤：

--啟動資料庫
./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"
重啟
 

postgreSQL 使用筆記（本人公司用的是arterydb,分裝的postgreSQL）