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 --启动数据库 ./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）全部内容，希望文章能够帮你解决postgreSQL 使用笔记（本人公司用的是arterydb,分装的postgreSQL）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。