pgsql: create table inside database

Posted Leave a commentPosted in PostgreSQL

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

pgsql: replace add break

Posted Leave a commentPosted in PostgreSQL

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

postgresql: table sizes

Posted Leave a commentPosted in PostgreSQL

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

postgresql: find biggest tables on your database

Posted Leave a commentPosted in PostgreSQL

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 ('"' || […]