Mengenal PostgREST

PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations.

Referensi

1. PostgREST, http://postgrest.org/

Date Formatting di PostgreSQL

Status : Draft

Referensi

1. The Ultimate Guide to PostgreSQL Date By Examples, http://www.postgresqltutorial.com/postgresql-date/

Mengubah Tipe Data Kolom dari Character menjadi Numeric

Status : Draft

Terkadang kita keliru menentukan tipe data suatu kolom, dan ketika tabel sudah terisi data, kita baru sadar akan kekeliruan tersebut. Jika menggunakan pgAdmin, kita tidak dapat mengubah tipe data suatu kolom dari character ke numerik.

Untuk mengubahnya kita dapat menggunakan SQL :

ALTER TABLE dekan ALTER COLUMN dekan type bigint using dekan::bigint;

Referensi

1. How to change column datatype from character to numeric in PostgreSQL 8.4, https://stackoverflow.com/questions/7683359/how-to-change-column-datatype-from-character-to-numeric-in-postgresql-8-4

Upgrade PostgreSQL

Status : Draft 

Referensi

1. Upgrading PostgreSQL from 9.4 to 9.6 on Debian Jessie, https://gist.github.com/dmitrykustov/27c673ec4f7abd716912e4c830910019

Copy Paste PostgreSQL Schema

Terkadang, kita ingin melakukan copy paste Schema pada PostgreSQL, baik strukturnya saja atau datanya saja, berikut adalah cara copy paste schema pada PostgreSQL :

Copy Paste PostgreSQL Beserta Datanya

#!/bin/bash                                                                                                                                          
##copy_schema.sh                                                                                                                                     
dbusername="$1"
dbname="$2"                                                                                                                                          
from_schema="$3"                                                                                                                                     
to_schema="$4"                                                                                                                                       
                                                                                                                                                     
                                                                                                                                                     
pg_dump -U "$from_schema" -h localhost -W --schema="$from_schema" "$dbname" | sed "s/$from_schema/$to_schema/g" > "$to_schema".sql                         
echo "\i "$to_schema".sql" | psql -v ON_ERROR_STOP=1  -U "$to_schema" -d "$dbname" -h localhost -W            

Copy Paste PostgreSQL Strukturnya Saja

#!/bin/bash                                                                                                                                          
##copy_schema.sh                                                                                                                                     
dbusername="$1"
dbname="$2"                                                                                                                                          
from_schema="$3"                                                                                                                                     
to_schema="$4"                                                                                                                                       
                                                                                                                                                     
                                                                                                                                                     
pg_dump -U "$from_schema" -h localhost -W --schema-only --schema="$from_schema" "$dbname" | sed "s/$from_schema/$to_schema/g" > "$to_schema".sql           
echo "\i "$to_schema".sql" | psql -v ON_ERROR_STOP=1  -U "$to_schema" -d "$dbname" -h localhost -W    

Cara menggunakan : ./copy_schema.sh username nama_schema_lama nama_schema_baru

Ambil record pertama dari setiap ID pada query ORDER BY

Status : Draft

Bagaimana kita mengambil id_soal pertama dari setiap kelompok id_soal ? Solusinya bisa intip di [1]

Atau contoh lain :

WITH par AS (
 SELECT ROW_NUMBER() OVER (PARTITION BY id_paragraph) AS rn, id_paragraph
                           FROM paragraph_soal WHERE id_paragraph='2013 (A.11)20-22'
)
SELECT  * from par where rn = 1;

Referensi 

1. PostgreSQL: Select only the first record per id based on sort order, https://stackoverflow.com/questions/18987650/postgresql-select-only-the-first-record-per-id-based-on-sort-order

Backup Offline PostgreSQL

Status  : Draft

Untuk backup offline, kita dapat mengcopy seluruh folder data PostgreSQL (/var/lib/postgresql/{versi}/main) ke database yang baru, asalkan versinys PostgreSQL nya sama.

Referensi

1. Is it possible to backup my ubuntu postgresql database by copying the /var/lib/postgresql/9.1/main file and pasting it back in?, https://dba.stackexchange.com/questions/93607/is-it-possible-to-backup-my-ubuntu-postgresql-database-by-copying-the-var-lib-p
2. Upgrading PostgreSQL from 9.3 to 9.5 on Ubuntu Raw, https://gist.github.com/johanndt/6436bfad28c86b28f794