Published on

Some basic instruction for PostgreSQL

Authors
  • avatar
    Name
    Lif
    Twitter

Login

Before make some query, we need to login.

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

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 = '...'

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);