Some basic instruction for PostgreSql

Login

Before make some query, we need to login.

psql -h <hostname> -p <port> -d <databaseName> -U  <username>

Show Databases

\l;

Change Databases

\c <DataBase Name>;

Create Database

CREATE DATABASE <DataBase Name>;

Delete DATABASE

drop DATABASE <DataBase Name>;

Create table

create table 

Delete table

drop table

Trunc table

TRUNCATE TABLE <tableName> RESTART IDENTITY CASCADE;
# RESTART IDENTITY CASCADE used for reset id

Show tables

Maybe we want to see what tables are in database.

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

Terminate

SELECT * FROM pg_stat_activity WHERE state = 'active';


SELECT pg_cancel_backend(46630)

SELECT pg_terminate_backend(46601)

Insert

insert into strategy_config (account_name, strategy_type, config, category, side) VALUES (
...
)

Update

update  strategy_maker set category = 'private' where strategy_name = '...'

To update jsonb, use

update strategy_config set config = config  || '{"account_name": "GATE_stone"}' where account_name = 'GATE_stone'

UPDATE your_table
SET your_jsonb_field = your_jsonb_field || '{"account_name": "new_account_name"}'
WHERE your_jsonb_field ->> 'symbol' = 'SCTUSDT';

Unique

ALTER TABLE strategy_config
DROP CONSTRAINT strategy_config_strategy_name_strategy_key;



ALTER TABLE strategy_config
ADD CONSTRAINT unique_strategy_account
UNIQUE (strategy_type, account_name);