PostgreSQL Use Notes (my company is Arterydb, the sub-installed PostgreSQL)

--Start the database
./bin/aty_ctl-d data-l Arterydb.log Start
--Connect to database
./bin/atysql-d artery
--Set environment variables
Export ld_library_path= '/home/arterydb/lib '
--Delete Table
DROP TABLE table_name;
--whether the query table exists
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 a function
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;
--Call function
Select Juge_delete_table ();
--Query Index
Select COUNT (*) from aty_indexes where indexname = ' i_jg_fy '; --query criteria must be lowercase
--Create Roles and permissions
CREATE ROLE admin with Createdb Createrole;
--Change Password
ALTER ROLE Davide with PASSWORD ' Hu8jmn3 ';
This section is detailed in the company intranet (http://172.16.6.233/ArteryDB/sql-createrole.html)
--Create an index
Create index i_jg_fy on Db_fy. T_JG (N_FY);
--Delete Index
Drop index Db_fy.i_jg_fy; (the index is corresponding, create the index under DB_FY, corresponding to delete the index under Shcame "Db_fy"; do not specify schame to delete or specify)
--Find the number of tables
Select COUNT (*) from aty_tables where SchemaName like '%imdb% ';

Sybase->arterydb Type Conversions
Binary->bytea
Image->bytea
Tinyint->smallint
Datetime->timestamp
Execute SQL file
./bin/atysql-h localhost-d Artery-u arterydb-f
/home/shsql/ods2etl/d_msajlx_zh.txt;
Bytea type Insert Picture
Create a stored procedure
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;
Execute INSERT statement
INSERT into DB_FY.AAA (a) Select Bytea_import ('/home/arterydb/untitled.png ');

Find a fixed table in which field
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 '
View table Structure
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 joins aty_attrdef on aty_attrdef.adrelid = aty_class.oid and Aty_attrdef.adnum = Aty_attribute.attnum
Left OUTER joins aty_description on aty_description.objoid = aty_class.oid and aty_description.objsubid = aty_attribute.at Tnum
WHERE
Aty_attribute.attnum > 0
and attisdropped <> ' t '
and Aty_class.relname= ' T_AYDM '--t_aydm as the table name
ORDER by Aty_attribute.attnum;
View the current number of database connections
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;
Numeric to String
Select To_char (1252323, ' 9999999999999 ');
Select To_char (Current_timestamp, ' yy-mm-dd HH12:MI:SS ');
Arterydb restarting the database
Setting environment variables
Export Atydata= "/home/arterydb/data"
Restart

PostgreSQL Use Notes (my company is Arterydb, the sub-installed PostgreSQL)