Create Database Pada PostgreSQL

 

1. Create DB

Format :

CREATE DATABASE db_name  

OWNER =  role_name  

TEMPLATE = template           

ENCODING = encoding           

LC_COLLATE = collate              

LC_CTYPE = ctype  

TABLESPACE = tablespace_name  

CONNECTION LIMIT = max_concurrent_connection

Contoh :

CREATE DATABASE dbPostgre;

2. Select Database

2.1. Connect : postgres=# 

2.2. List Database : \l 

2.3. Connect ke database : postgres-# \c dbPostgre 

3. Drop DB

             

DROP DATABASE [ IF EXISTS] name;

REVOKE CONNECT ON DATABASE dbPostgre from public; 

3.1. Command SQL

dropdb [option...] dbname   

contoh :

dropdb -h localhost -p 5432 -U postgress dbPostgre

Password for user postgress: ****  

4. Akses Dalam Bentuk JSON

4.1. JSON

SELECT Purchase_description  -> 'purchaser' AS pruchaser  FROM Purchase; 

4.2.  JSON Condition

SELECT Purchase_description ->> 'purchaser' AS Purchaser  

FROM Purchase  WHERE Purchase_description-> 'items' ->> 'product' = 'ice cream'; 

4.3. Text

SELECT Purchase_description  ->> 'purchaser' AS pruchaser  FROM Purchase; 

4.4. JSON & Text

SELECT Purchase_description -> 'items' ->> 'product' as Product  FROM Purchase  

ORDER BY Product; 

4.5. json_object_keys function

SELECT json_object_keys (Purchase_description->'items')  FROM Purchase;   

4.6. json_each function

SELECT json_each (Purchase_description)  FROM Purchase;

4.7. json_each_text()

SELECT json_each_text(Purchase_description)  FROM Purchase; 

4.8. json_typeof function 

SELECT json_typeof(Purchase_description->'items')  FROM Purchase;