postgresql: limit 1 on group by

It’s stupidly complicated to get just one record per group on a PostgreSQL query… After an hour of trial and error this worked for me: SELECT DISTINCT ON (c_order_id) c_order_id FROM c_invoice   No Group By or Limit 1 functions […]

postgresql: table sizes

SELECT *, pg_size_pretty(total_bytes) AS total , pg_size_pretty(index_bytes) AS INDEX , pg_size_pretty(toast_bytes) AS toast , pg_size_pretty(table_bytes) AS TABLE FROM ( SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM ( SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME , c.reltuples AS row_estimate , pg_total_relation_size(c.oid) AS […]

mysql: enable logging

mysql> show variables like ‘%log%’; Recreate the log files I deleted by mistake. It appears the MySQL does not recreate them automatically. touch /var/log/mysql.log chown mysql:adm /var/log/mysql.log touch /var/log/mysql/error.log chown mysql:adm /var/log/mysql/error.log touch /var/log/mysql/mysql-slow.log chown mysql:adm /var/log/mysql/mysql-slow.log touch /var/log/mysql/mysql.log chown […]

postgresql: find biggest tables on your database

Excecute the following command on phpPgAdmin on the SQL Tab.   SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS indexes_size, pg_total_relation_size(table_name) AS total_size FROM ( SELECT (‘”‘ || […]

postgresql: update column from another table’s column

UPDATE c_order SET po_refernce_no = (SELECT po_refernce_no FROM “Import” WHERE c_order_id = c_order.c_order_id), tracking_number = (SELECT tracking_number FROM “Import” WHERE c_order_id = c_order.c_order_id)     UPDATE m_product SET manufacturer = (SELECT m_product_po.manufacturer FROM m_product_po WHERE m_product.m_product_id = m_product_po.m_product_id )