I want to
drop a ROLE (previously USER/GROUPs) which is still referenced by object(s) in my database. But
PostgreSQL has objections:
ERROR: role "xyz" cannot be dropped because some objects depend on itDETAIL: access to schema public
Continue reading "role ... cannot be dropped because some objects depend on it"
I have seen this question more than once in the past: "How much storage space do i need for my table?"
PostgreSQL can give you this information.The storage space (in bytes) for one specific table:ads=# select pg_relation_size('size_test');
pg_relation_size
------------------
5668864
(1 row)
Same query, but with the result in a human-readable format:ads=# select pg_size_pretty(pg_relation_size('size_test'));
pg_size_pretty
----------------
5536 kB(1 row)
Remember that this is only the size of the table, not included an index or additional stuff.
The size including any index can be found out with:ads=# select pg_size_pretty(pg_total_relation_size('size_test'));
pg_size_pretty
----------------
7656 kB
(1 row)
The size of a complete database:ads=# select pg_size_pretty(pg_database_size('ads'));
pg_size_pretty
----------------
11 MB
(1 row)
You can even find out, how much space a specific value needs:ads=# select pg_column_size(5::smallint);
pg_column_size
----------------
2
(1 row)
ads=# select pg_column_size(5::int);
pg_column_size
----------------
4
(1 row)
ads=# select pg_column_size(5::bigint);
pg_column_size
----------------
8
(1 row)
ads=# select pg_column_size('This is a string'::varchar);
pg_column_size
----------------
20
(1 row)
ads=# select length('This is a string'::varchar);
length
--------
16
(1 row)
The 4 byte difference between the column size and the string length is the storage overhead needed for varchar columns.The size for a tablespace can be found out with pg_tablespace_size(), but i don't have a machine with configured tablespace at hand to show an example.
If you have a table with an unique key field, want to insert new values but you don't know, if the value may already be in the table, you could use the following query:
CREATE TABLE uk_test (id SERIAL, col1 VARCHAR(20) NOT NULL UNIQUE, col2 VARCHAR(20) NOT NULL);
INSERT INTO uk_test (col1, col2) SELECT 'content 1', 'content 2' WHERE NOT EXISTS
(SELECT col1 FROM uk_test WHERE col1='content 1');
This does avoid unique key violation errors in a way, that no insert will happen if the value is already in the table.
I would like to thank depesz from #postgresql for the idea.