PostgreSQL

PgSQL: Replace Add break

Posted on

UPDATE m_product SET em_mg_longdescription = regexp_replace(em_mg_longdescription,'------------',chr(13)) WHERE em_mg_manufacturer = 1000637 AND value = 'IMI-YW356A01'     Some additional replace scripts   UPDATE m_product SET em_mg_longdescription = regexp_replace(em_mg_longdescription,'</li>',chr(10)) WHERE em_mg_manufacturer = 1000637   UPDATE m_product SET em_mg_longdescription = replace(em_mg_longdescription,'<li>','') WHERE em_mg_manufacturer = 1000637   UPDATE m_product SET em_mg_longdescription = replace(em_mg_longdescription,'<ul>','') WHERE em_mg_manufacturer = 1000637   UPDATE […]

MySQL

PHPMyAdmin: Securing web console

Posted on

I installed PHPMyadmin to check my help manage my database, eventhough I also use the desktop program MySQL Workbench, I fell sometimes its important to use the webconsole. Recently, upon checking my MySQL Logs (/var/logs/mysql/error.log) I encountered that everyday I had thousands of failed logins to the PHPMyAdmin. I sure enough have a very strong […]

PostgreSQL

PostgreSQL: Limit 1 on Group BY

Posted on

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 needed.    

PostgreSQL

PgSQL: Cannot Alter Character Length because of Dependant Views

Posted on

  SQL error: ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view rv_warehouseprice depends on column "sku" In statement: ALTER TABLE "adempiere"."m_product" ALTER COLUMN "sku" TYPE character varying(50) I've read some documentation and the only apparent solution is to delete all the related views and then […]

PostgreSQL

PostgreSQL: Table Sizes

Posted on

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 total_bytes , pg_indexes_size(c.oid) AS index_bytes , pg_total_relation_size(reltoastrelid) AS toast_bytes FROM pg_class c LEFT JOIN pg_namespace […]

MySQL

MySQL: Enable Logging

Posted on

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 mysql:adm /var/log/mysql/mysql.log

MySQL

CSV IMPORT MANUAL

Posted on

Remove Commas from strings Truncate String Length using LEFT function in Excel Use CLEAN Function to remove unwanted breaks and non displaying characters

PostgreSQL

PostgreSQL: Find biggest tables on your database

Posted on

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 ('"' || table_schema || '"."' || table_name || '"') AS table_name FROM information_schema.tables ) AS all_tables ORDER […]

PostgreSQL

PostgreSQL: Update column from another table's column

Posted on

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 )

PostgreSQL

PgSql: pgdump authentication

Posted on

I'm trying to do a backup similar to mysqldump... but it seems that pgsql (very intelligently though) does not allow you to pass pasword on via text on the script. So the solution to be able to execute this command is create a file called ".pgpass" in the folder of the user that will execute […]

MySQL

MySQL: Assign Opportunities to User based on Parent Account

Posted on

Query to Select List:   SELECT * FROM opportunities LEFT JOIN accounts_opportunities ON accounts_opportunities.opportunity_id = opportunities.id LEFT JOIN accounts ON accounts_opportunities.account_id = accounts.id WHERE accounts.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86';     UPDATE Query: UPDATE opportunities x LEFT JOIN accounts_opportunities y ON y.opportunity_id = x.id LEFT JOIN accounts z ON y.account_id = z.id SET x.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86' WHERE […]

MySQL

MySQL: Assigned Calls to User based on Parent Account or Lead

Posted on

Select List of Calls to Update: SELECT * FROM calls LEFT JOIN accounts ON calls.parent_id = accounts.id LEFT JOIN leads ON calls.parent_id = leads.id WHERE ( accounts.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86' OR leads.assigned_user_id = '7077c616-4f2c-f966-d9b6-5581807bae86' ) ;   Update Query: UPDATE calls x LEFT JOIN accounts y ON x.parent_id = y.id LEFT JOIN leads z ON x.parent_id […]