Tuesday, 30 August 2016

Menghapus Data dengan Opsi LIMIT

Ketika kita tidak menggunakan data autoincrement seperti serial dan lupa menetapkan primary key dalam sebuah tabel, terkadang ada data yang duplikat, semua kolom isi datanya sama.

Bagaimana cara menghapus hanya beberapa data saja ? misal, hapus 2 data pertama yang sesuai dengan kriteria yang saya pilih ?

Karena di PostgreSQL tidak ada opsi LIMIT dalam klausa DELETE-nya, harus ada triks untuk melakukan hal ini, Alhamdulillah, hal ini dapat dilakukan dengan memanfaatkan ctid

DELETE FROM paragraph_soal
WHERE ctid IN (
  SELECT ctid FROM paragraph_soal WHERE id_paragraph='xyz12345 LIMIT 1
); 
Apa itu ctid ? Dari [1] ctid adalah :

The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change each time it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

Contoh Lain :

select id_paragraph, isi, gambar from paragraph_soal where id_paragraph LIKE '17INA09%' group by id_paragraph, isi, gambar having count(*) > 1

DELETE FROM paragraph_soal
WHERE id_paragraph IN (SELECT id_paragraph
              FROM (SELECT id_paragraph,
                             ROW_NUMBER() OVER (partition BY id_paragraph, isi, gambar ORDER BY id_paragraph) AS rnum
                     FROM paragraph_soal) t
              WHERE t.rnum > 1);

Referensi

Tuesday, 16 August 2016

PostrgreSQL : Export - Import Data

Status : Draft

query_to_xml(query text, nulls boolean, tableforest boolean, targetns text)


SELECT * FROM query_to_xml('select * from set_soal where id_set_soal=64',true,false,'') xml;

"<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<row>
  <id_set_soal>64</id_set_soal>
  <nama>0 Aquaponik Dasar</nama>
  <tahun>2016</tahun>
  <ujian>aquaponik</ujian>
</row>

</table>
"

Jika menggunakan prepared statement, SQL nya harus ditulis seperti ini :

SELECT * FROM query_to_xml('SELECT * FROM set_soal WHERE id_set_soal = '|| $1,true,false,'') set_xml;


Referensi

  1. Mapping Tables to XML, https://www.postgresql.org/docs/current/static/functions-xml.html#FUNCTIONS-XML-MAPPING
  2. Using query_to_xml in PostgreSQL with prepared statements, http://stackoverflow.com/questions/14897398/using-query-to-xml-in-postgresql-with-prepared-statements

Saturday, 26 March 2016

Wednesday, 2 March 2016

Backup Restore Database

Backup Restore Cara Instan 


Untuk backup, gunaka pgadmin, dan pilih tipe plain.

Untuk restore login via command line ke database yang ingin kita restore :

Hapus schema tempat tabel-tabel berada, misal public  :

drop schema public cascade;
create schema public;

Restore

\i nama_file_backup.sql 

Ubah ownership (jika perlu) :

REASSIGN OWNED BY old_role [, ...] TO new_role