PostgreSQL

PgSQL: View Rows Per Table

Posted on

Two Methods: First: SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;   Second: SELECT nspname AS schemaname,relname,reltuples FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (‘pg_catalog’, ‘information_schema’) AND relkind=’r’ ORDER BY reltuples DESC;

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.