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

Instalasi

Status : Draft

Ubuntu

Ketika melakukan instalasi, sebetukan nama versinya, misal :

sudo apt-get install postgresql-9.6 

jika tidak akan menemui masalah seperti [1].

Referensi

1. Cannot connect to postgresql on port 5432, https://askubuntu.com/questions/50621/cannot-connect-to-postgresql-on-port-5432

Backup & Restore PostgreSQL

Status : Draft

Dengan pg_dump & pg_restore & psql 

Restore 

psql -U <username> -d <dbname> -1 -f <filename>.sql 

pg_restore -U <username> -d <dbname> -1 <filename>.dump

Backup Restore Berdasarkan SQL Query

Contoh :

Untuk backup

psql -h localhost -d banksoal -U postgres -W -c "copy (select * from soal_naskah where id_soal in(48624,48603)) to '/tmp/copy_soal/soal_naskah.sql'"

Untuk restore

copy soal_naskah from '/tmp/copy_soal/soal_naskah.sql'

Backup Restore Beda Versi PostgreSQL

Studi kasus, backup dari PostgreSQL 9.6.8 ke PostgreSQL 11.1. 

PostgreSQL 9.6.8 menggunakan extension tsearch2, sedangkan di 11.1 extension tersebut sudah tidak ada, maka akan error : 

pg_restore: WARNING:  type input function public.gtrgm_in should not be volatile

pg_restore: WARNING:  type output function public.gtrgm_out should not be volatile

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 275; 1255 16414 FUNCTION dex_init(internal) banksoal

pg_restore: [archiver (db)] could not execute query: ERROR:  could not access file "$libdir/tsearch2": No such file or directory

    Command was: CREATE FUNCTION public.dex_init(internal) RETURNS internal

    LANGUAGE c

    AS '$libdir/tsearch2', 'tsa_dex_init';

Referensi

1. Export specific rows from a PostgreSQL table as INSERT SQL script, http://stackoverflow.com/questions/12815496/export-specific-rows-from-a-postgresql-table-as-insert-sql-script

GROUP_CONCAT di PostgreSQL

Terkadang, ketika melakukan query GROUP BY, kita ingin melihat nilai-nilai pada kolom tertentu, jika di MySQL ada GROUP BY [1], bagaimana kita melakukannya di PostgreSQL ?

Alhamdulillah, di PostgreSQL kita dapat melakukannya dengan string_agg [2][3][4]

SELECT a.farm_id, string_agg(DISTINCT a.name, ',') as animals, string_agg(DISTINCT t.name, ',') as tools FROM animal a, tool t WHERE a.farm_id = t.farm_id GROUP BY a.farm_id;

Referensi

1. Merangkai (Concatenation) String pada Query Agregasi di MySQL, http://workshop.openthinklabs.com/resources/sistem-basis-data-database/mysql-bootcamp/buku-masak-a-k-a-cookbook/merangkai-concatenation-string-pada-query-agregasi-di-mysql
2. string_agg, https://www.postgresql.org/docs/9.5/static/functions-aggregate.html
3. Postgres GROUP_CONCAT, https://coderwall.com/p/eyknwa/postgres-group_concat
4. Aggregate Expressions, https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

Berbagai Teknik Pagination (Paginasi) di PostgreSQL

Status : Draft

Referensi

1. Five ways to paginate in Postgres, from the basic to the exotic, https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/

Memeriksa Apakah String itu Sebuah Tanggal yang Valid atau Tidak

Kita dapat menggunakan fungsi berikut :

CREATE OR REPLACE FUNCTION IS_DATE (text) RETURNS  INTEGER AS $$
BEGIN
     IF ($1 IS NULL) THEN
         RETURN 0;
     END IF;
     PERFORM $1::date;
     RETURN 1;
EXCEPTION WHEN others THEN
     RETURN 0;
END;
$$ language plpgsql;

Referensi

1. Re: IsDate function in plpgsql, https://www.postgresql.org/message-id/4450D7D8.6050200@presinet.com

psql: FATAL: Peer authentication failed for user "username"

Status : Draft

Referensi 

1. Getting error: Peer authentication failed for user “postgres”, when trying to get pgsql working with rails, http://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge
2. Creating and Using A .pgpass file, https://linuxandryan.wordpress.com/2013/03/07/creating-and-using-a-pgpass-file/

PL/SQL : Otomatis Mengupdate Nilai Suatu Kolom Berdasarkan Suatu Perubahan

Status : Draft

Mengupdate nilai suatu kolom, jika nilai kolom lain berubah

Contoh :

CREATE OR REPLACE FUNCTION soal_set_status_akhir()
RETURNS TRIGGER
AS $$
BEGIN
  NEW.status_akhir := CASE WHEN NEW.id_status_soal = 1 THEN 1
                      ELSE 0 END;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS soal_set_status_akhir ON soal;
CREATE TRIGGER soal_set_status_akhir
BEFORE INSERT OR UPDATE ON soal
FOR EACH ROW
  EXECUTE PROCEDURE soal_set_status_akhir();

Referensi

1. Create Trigger, https://www.postgresql.org/docs/9.1/static/sql-createtrigger.html
2. Postgresql Trigger based on value of one column to change or update value of other column in each row., http://stackoverflow.com/questions/20958569/postgresql-trigger-based-on-value-of-one-column-to-change-or-update-value-of-oth
3. Dokumentasi : Database, http://banksoal.openthinklabs.com/2017/01/dokumentasi-database.html