ricardo malla's blog

  create  .pg_pass file on the root folder of the user that will run the pgsql command for example. /root or /user   touch /root/.pgpass   chmod 600 .pgpass   add the credentialas user and password that will be user […]

Example   CREATE TABLE c_ordernotes ( c_ordernotes_id numeric(10,0) CONSTRAINT firstkey PRIMARY KEY, ad_client_id numeric(10,0) NOT NULL, ad_org_id numeric(10,0) NOT NULL, isactive character(1), created timestamp DEFAULT current_timestamp, createdby numeric(10,0), updated timestamp DEFAULT current_timestamp, updatedby numeric(10,0), c_order_id numeric(10,0), line numeric(10,0), notes_name varchar(255), […]

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 […]

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 […]

  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 […]

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 […]