Postgres Link to heading

Select query on jsonb column Link to heading

Example content of the json_column_name: [{"json_key":"json_value"}]

-- Get specific value of the first element of an array
SELECT json_column_name -> 0 ->> 'json_key'
FROM table_name
WHERE json_column_name IS NOT NULL AND json_column_name <> '[]'::jsonb;

-- Get length of an JSON array
select jsonb_array_length(json_column_name)
FROM table_name

Check schema Link to heading

SELECT 
    column_name, 
    data_type, 
    character_maximum_length,
    column_default,
    is_nullable
FROM 
    information_schema.columns
WHERE 
    table_schema = 'table_schema'
    and table_name = 'table_name';

Create an index Link to heading

CREATE INDEX idx_collection_id ON langchain_pg_embedding (collection_id);

Check running activities Link to heading

SELECT pid, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;

Kill a specific activity Link to heading

  1. Via pg_cancel_backend

    SELECT pg_cancel_backend(pid);

    Attempts to cancel the current query, which is safer but might not work if the process is truly idle and not executing any query

  2. Via pg_terminate_backend

    SELECT pg_terminate_backend(pid);

    Forcefully closes the connection, which will definitely stop the transaction but might cause issues for the client

Change ownership of tables Link to heading

Generate SQL statements to change ownership of tables to an user

select 'ALTER TABLE ' || t.tablename || ' OWNER TO psr_master;' 
from  pg_tables t
where t.tableowner = 'masterUser'
and schemaname = 'psr'

Creating / Restoring from dumps Link to heading

Using pg_dump and pg_restore to migrate the data between Postgres databases

pg_dump -v -h <host_adress> -U <user> -F c -f <file_name> -a --schema=<schema> <database>
pg_restore -v -h <host_adress> -U <user> -a --schema=<schema> -d <database> <file_name>
  • -a: only data
  • -F c: using custom format -> makes restoring with pg_restore necessary
  • <user>: can be different ones

MSSQL Link to heading

Show open database connections Link to heading

select dbid, loginame, login_time, * 
from sys.sysprocesses