Posted in IT

Get Postgresql DB size and table sizes

Get DB Size:

SELECT pg_size_pretty(pg_database_size(‘databasename’)) As fulldbsize;

 

Get All Table size from one Database:

SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||’.’||tablename) AS size,
pg_total_relation_size(schemaname||’.’||tablename) AS total_size
FROM pg_tables) AS TABLES
WHERE schemaname=’public’
ORDER BY total_size DESC;

Posted in IT

Alter table change size of the varchar column without drop view

Ini nih cara ngubah length dari varchar, biasa nya tidak bisa langsung alter biasa karena ada dependent ke view.

If you don’t need to change the type of the field, but just the size of it, this approach should work:

Starting with these tables:

CREATE TABLE foo (id integer primary key, names varchar(10));
CREATE VIEW voo AS (SELECT id, names FROM foo);

\d foo and \d voo both show the length as 10:

id     | integer               | not null
names  | character varying(10) | 

Now change the lengths to 20 in the pg_attribute table:

UPDATE pg_attribute SET atttypmod = 20+4
WHERE attrelid IN ('foo'::regclass, 'voo'::regclass)
AND attname = 'names';

(note: the 20+4 is some crazy postgresql legacy thing, the +4 is compulsory.)

 

Repost from: http://stackoverflow.com/questions/3243863/problem-with-postgres-alter-table