PostgreSQL usage notes (my company uses arterydb, sub-installed postgreSQL), postgresqlarterydb

PostgreSQL usage notes (my company uses arterydb, sub-installed postgreSQL), postgresqlarterydb
-- Start the database
./Bin/aty_ctl-D data-l arterydb. log start
-- Connect to the database
./Bin/atysql-d artery
-- Set Environment Variables
Export LD_LIBRARY_PATH = '/home/arterydb/lib'
-- Delete a table
Drop table table_name;
-- Query whether the 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
$
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 a function
Select Juge_Delete_table ();
-- Query Indexes
Select count (*) from aty_indexes where indexname = 'I _ jg_fy'; -- the query condition must be in lowercase.
-- Create roles and permissions
Create role admin with createdb createrole;
-- Change Password
Alter role davide with password 'hu8jmn3 ';
For more information about this part, see 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 an index
Drop index db_fy. I _jg_fy)
-- Query the number of tables
Select count (*) from aty_tables where schemaname like '% imdb % ';

Sybase-> arterydb type conversion
Binary-> bytea
Image-> bytea
Tinyint-> smallint
Datetime-> timestamp
Execute the SQL File
./Bin/atysql-h localhost-d artery-U arterydb-f
/Home/shsql/ods2etl/D_MSAJLX_ZH.txt;
Bytea Type Insert Image
Create a stored procedure
Create or replace function bytea_import (p_path text, p_result out bytea)
$
Declare
Rochelle 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 the field in the fixed table
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
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 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 indicates the table name.
Order by aty_attribute.attnum;
View the number of connections to the current database
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, '123 ');
Select to_char (current_timestamp, 'yy-MM-DD HH12: MI: ss ');
Arterydb restarts the database
Set Environment Variables
Export ATYDATA = "/home/arterydb/data"
Restart