PostgreSQL

PgSQL: Create table inside database

Posted on

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), notes_description varchar(2550) );

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

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

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

OpenBravo

PgSQL: List and Remove Triggers

Posted on

I want to delete directly on the PgSQL database on my OpenBravo application but it has a los of triggers that prevent certain actions, first I want to list them and after that will want to disable them. List: SELECT event_object_table,trigger_name,event_manipulation,action_statement,action_timing FROM information_schema.triggers WHERE event_object_table='m_inventoryline' ORDER BY event_object_table,event_manipulation Disable: DROP TRIGGER m_inventoryline_trg ON m_inventoryline;

OpenBravo

PgSQL: Cannot Delete Line on Table.

Posted on

This happens when I try to manually delete a record on the Database   ERROR: update or delete on table "m_inoutline" violates foreign key constraint "m_inoutline_canceled_inoutline" on table "m_inoutline" DETAIL: Key (m_inoutline_id)=(8310BD9D63A94DB5A2294EC8E48C6C9F) is still referenced from table "m_inoutline". ********** Error ********** ERROR: update or delete on table "m_inoutline" violates foreign key constraint "m_inoutline_canceled_inoutline" on table […]

Jasper Reports

PgSql: Cannot use Extract function inside a Where Clause

Posted on

For Exmple:   AND (date_part('YEAR',fin_finacc_transaction.statementdate) = $P{Año}) or AND ((extract(Year from  fin_finacc_transaction.statementdate) LIKE $P{Año}) OR $P{Año} = 'ALL') Neither of these work, I guess it has something to do with the Parameter type, as it works when I use a number instead of the Parameter. See error Log. net.sf.jasperreports.engine.JRException: Error executing SQL statement for : […]