折腾 Zhēteng

PostgreSQL Console Notes

Created: Modified: 3 min read

This note for PostgreSQL console commands.

Switch to expanded display

\x

List databases

\l

Show all the tables

\dt

Show all indexes

\di

Describe a specific table

\d your_table_name

List all views in psql

\dv

To check the definition of a view:

\d+ your_view_name

Example:

\d+ latest_metadata
                          View "public.latest_metadata"
     Column      |           Type           | Collation | Nullable | Default | Storage  | Description
-----------------+--------------------------+-----------+----------+---------+----------+-------------
 instance_id     | text                     |           |          |         | extended |
 launch_date     | timestamp with time zone |           |          |         | plain    |
 last_reboot     | timestamp with time zone |           |          |         | plain    |
 state           | text                     |           |          |         | extended |
 state_priority  | integer                  |           |          |         | plain    |
 last_event_uuid | uuid                     |           |          |         | plain    |
 metadata        | jsonb                    |           |          |         | extended |
View definition:
 SELECT latest_instance_state_metadata.instance_id,
    latest_instance_state_metadata.launch_date,
    latest_instance_state_metadata.last_reboot,
    latest_instance_state_metadata.state,
    latest_instance_state_metadata.state_priority,
    latest_instance_state_metadata.last_event_uuid,
    latest_instance_state_metadata.metadata,
   FROM latest_instance_state_metadata
  WHERE latest_instance_state_metadata.state <> 'EC2_STATE_CHANGE_SHUTTING_DOWN'::text;

Show schema

\d <table_name>

Select by ID

select * from entities where id='e0c275c8-77c6-4f49-9951-572a07642a7b';

Note that you should use single quote, not double quote.

Sample queries

SELECT tweets.*, users.* FROM tweets
  JOIN users ON tweets.sender_id = users.id
  JOIN follows ON follows.followee_id = users.id
  WHERE follows.follower_id = current_user
SELECT a.name FROM (SELECT name FROM agentinformation) a

Statistics:

SELECT schemaname, relname, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze FROM pg_stat_all_tables
explain analyze select id, version, status, created_by, created_at, started_at, results_refreshed_at, terminated_by from entities where status='starting' OR status='running' limit 10;

Output:

                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.25..8.27 rows=2 width=158) (actual time=0.258..0.258 rows=0 loops=1)
   ->  Index Scan using entity_status_not_finished_idx on entities  (cost=0.25..8.27 rows=2 width=158) (actual time=0.257..0.257 rows=0 loops=1)
         Filter: ((status = 'starting'::entity_status) OR (status = 'running'::entity_status))
 Planning Time: 0.119 ms
 Execution Time: 0.283 ms
(5 rows)

Analyze table (writer instance only, read-only can’t do)

analyze (verbose true, skip_locked) <table_name>;
select relname, schemaname, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze from pg_stat_all_tables;

Create index

Assume you have a table: your_table_name and you have a column: metadata which is a jsonb, and in that jsonb, you have a string field keyed with azure-resource-name.

CREATE INDEX idx_azure_resource_name_metadata ON your_table_name USING BTREE ((metadata ->> 'azure-resource-name'::text));

If you need to use LOWER() function for the value in this metadata field:

CREATE INDEX idx_azure_resource_name_metadata_lower ON your_table_name USING BTREE (LOWER((metadata ->> 'azure-resource-name'::text)));

You can use “CREATE INDEX CONCURRENTLY” to create index concurrently.

CREATE INDEX CONCURRENTLY idx_azure_resource_name_metadata ON your_table_name USING BTREE ((metadata ->> 'azure-resource-name'::text));

NOTE

You can’t use CREATE INDEX CONCURRENTLY to create index concurrently in a transaction.

Drop index

Better to use with IF EXISTS

DROP INDEX IF  EXISTS index_name ;

Graph View