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

1. 5.4. System Columns, https://www.postgresql.org/docs/8.2/static/ddl-system-columns.html
2. Deleting duplicates, https://wiki.postgresql.org/wiki/Deleting_duplicates

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

Mengubah Schema Default yang Diakses Oleh User Postgres

Status : Draft

Dari [1] :

ALTER USER user SET search_path TO accountschema;

Referensi

1. Best way to set a PostgreSQL schema using PHP PDO, http://stackoverflow.com/questions/21714096/best-way-to-set-a-postgresql-schema-using-php-pdo

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

atau

--ubah ownership table
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'schema_name';" db_name` ; do  psql -c "alter table schema_name.\"$tbl\" owner to schema_name" db_name ; done

--ubah ownership sequence
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'schema_name';" db_name` ; do  psql -c "alter sequence schema_name.\"$tbl\" owner to schema_name" db_name ; done

--ubah ownership views
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'schema_name';" db_name` ; do  psql -c "alter view schema_name.\"$tbl\" owner to schema_name" db_name ; done

--ubah ownership function
IFS=$'\n'
for fnc in `psql -qAt -c "SELECT  p.proname||''||'(' || oidvectortypes(p.proargtypes) ||')' FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'schema_name';" db_name`
do
  psql -c "ALTER FUNCTION schema_name.$fnc OWNER TO schema_name" db_name ; 
done

Mengubah Kepemilikan Seluruh Tabel

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

Referensi

1. Modify OWNER on all tables simultaneously in PostgreSQL, http://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql