pgsql: create table inside database
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), […]
Category: postgresql
delete duplicate rows on pgsql and mysql
This code shows how to delete duplicate rows on pgsql DELETE FROM m_product WHERE sku in( SELECT sku FROM ( SELECT […]
pgsql: replace add break
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 […]
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 […]
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) […]
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 […]