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

Source: Internet
Author: User

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


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.