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"
重启