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 […]
Category: databases
pgsql: cannot alter character length because of dependant views
SQL error: ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view rv_warehouseprice […]
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) […]
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. […]
csv import manual
Remove Commas from strings Truncate String Length using LEFT function in Excel Use CLEAN Function to remove unwanted breaks and non displaying […]
sql query: remove leading and trailing double quotes
UPDATE m_product SET name = TRIM(BOTH '"' FROM name) WHERE m_product_id = '1128493' UPDATE m_product set name = replace(name, '""', '"') WHERE […]
idempiere: alter sequence
Update M_Product_ID UPDATE ad_sequence SET currentnext = 1026150 WHERE ad_sequence_id = 114 Update Product PriceList UPDATE ad_sequence SET currentnext = 1026138 WHERE ad_sequence_id […]
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 […]
postgresql: restore db from sql
#Upload the backup .sql file to the server #Execute the following command. root@ubuntu:~# psql -Uadempiere idempiere < /opt/idempiere-server/DBImport/DB3.sql
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 = […]