Tuesday, 8 August 2017

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

Sunday, 30 April 2017

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

Monday, 13 March 2017

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'

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

Saturday, 25 February 2017

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]

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

Friday, 13 January 2017

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

Friday, 6 January 2017

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