PostgreSQL syntax (command line) handbook, dasar-dasar yang harus dipahami dan sering di praktekin saja biar hapal, tapi ga perlu dihapalin.
Getting started (memulai) postgresql. setingan default postgresql kalo di install melalui hombrew package manager darwin atau macOS biasanya nama usernya postgress dan tanpa password.
aku disini punya 3 database dengan nama:
- SwiftTodo
- dvd_rent
- postgres (dabatase bawaan postgresql)
Command Line Masuk Ke Postgresql
untuk masuk ke postgresql yang di set tanpa password, caranya ketik psql diikuti dengan nama database, misal nama database yang kita pilih adalah dvd_rent. Maka command line untuk masuk ke psql di terminal kita ketikkan seperti ini:
$ psql dvd_rent
lalu akan muncul seprti ini jika berhasil masuk :
psql (12.4)
Type "help" for help.
dvd_rent=#
Command Line inside Postgresql
jika sudah masuk di postgresql awalan command line diterminal akan menjadi [namadatabase]=# bukan lagi jadi nama user computer(mac atau linux) yang selalu diawali dengan $ atau ~).
-
Show Help
helphasilnya akan seperti ini:
You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit -
Check List All Database
\lcontoh hasilnya seperti ini :
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------------------+----------+----------+---------+-------+----------------------- SwiftTodo | metalbee | UTF8 | C | C | filem | metalbee | UTF8 | C | C | postgres | metalbee | UTF8 | C | C | template0 | metalbee | UTF8 | C | C | =c/metalbee + | | | | | metalbee=CTc/metalbee template1 | metalbee | UTF8 | C | C | =c/metalbee + | | | | | metalbee=CTc/metalbee -
Check List User
\ducontoh hasilnya :
List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- metalbee | Superuser, Create role, Create DB, Replication, Bypass RLS | {} -
Check List Function
\dfcontoh hasilnya :
List of functions Schema | Name | Result data type | Argument data types | Type --------+----------------------------+------------------+---------------------------------------------------------------------+------ public | _group_concat | text | text, text | func public | film_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | func public | film_not_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | func public | get_customer_balance | numeric | p_customer_id integer, p_effective_date timestamp without time zone | func public | group_concat | text | text | agg public | inventory_held_by_customer | integer | p_inventory_id integer | func public | inventory_in_stock | boolean | p_inventory_id integer | func public | last_day | date | timestamp without time zone | func public | last_updated | trigger | | func public | rewards_report | SETOF customer | min_monthly_purchases integer, min_dollar_amount_purchased numeric | func (10 rows) -
Check Conection
\ccontoh hasilnya :
You are now connected to database "dvd_rent" as user "metalbee".
lebih jelasnya bisa lihat di history line gambar dibawah ini:
-
Check List Indexes
\dicontoh hasilnya:
List of relations Schema | Name | Type | Owner | Table --------+-----------------------------------------------------+-------+----------+--------------- public | actor_pkey | index | metalbee | actor public | address_pkey | index | metalbee | address public | category_pkey | index | metalbee | category public | city_pkey | index | metalbee | city public | country_pkey | index | metalbee | country public | customer_pkey | index | metalbee | customer public | film_actor_pkey | index | metalbee | film_actor public | film_category_pkey | index | metalbee | film_category public | film_fulltext_idx | index | metalbee | film public | film_pkey | index | metalbee | film public | idx_actor_last_name | index | metalbee | actor public | idx_fk_address_id | index | metalbee | customer public | idx_fk_city_id | index | metalbee | address public | idx_fk_country_id | index | metalbee | city public | idx_fk_customer_id | index | metalbee | payment public | idx_fk_film_id | index | metalbee | film_actor public | idx_fk_inventory_id | index | metalbee | rental public | idx_fk_language_id | index | metalbee | film public | idx_fk_rental_id | index | metalbee | payment public | idx_fk_staff_id | index | metalbee | payment public | idx_fk_store_id | index | metalbee | customer public | idx_last_name | index | metalbee | customer public | idx_store_id_film_id | index | metalbee | inventory public | idx_title | index | metalbee | film public | idx_unq_manager_staff_id | index | metalbee | store public | idx_unq_rental_rental_date_inventory_id_customer_id | index | metalbee | rental public | inventory_pkey | index | metalbee | inventory public | language_pkey | index | metalbee | language public | payment_pkey | index | metalbee | payment public | rental_pkey | index | metalbee | rental public | staff_pkey | index | metalbee | staff public | store_pkey | index | metalbee | store (32 rows) ~ ~ ~ (END)
nah kali ada case seperti diatas ada line yang tulisannya (END) cara balik lagi ke command line dvd_rent=# tinggal pencet huruf Q di keyboard.
