PostGreSQL

Create Super-User

create user <username> createdb createuser;

Create/Modify User Password

alter user <username> with password '<password>';

Modify a column type (PostGreSQL 8)

ALTER TABLE <table> ALTER COLUMN <column> TYPE <new_type>;

Modify a column type (the old way)

BEGIN;
ALTER TABLE <table> ADD COLUMN <new_column> <new_type>;
UPDATE <table> SET <new_column> = CAST(<old_column> AS <new_type>);
ALTER TABLE <table> DROP COLUMN <old_column>;
COMMIT;

Add an UNIQUE constraint

ALTER TABLE <table> ADD CONSTRAINT table_column_key UNIQUE (<column>);

Drop a constraint

ALTER TABLE <table> DROP CONSTRAINT <constraint>;

Log SQL queries

In postgresql.conf:

log_statement = 'all'

View Current SQL Queries

In postgresql.conf:

stats_command_string = true

Then look at table pg_stat_activity

String Concatenation in SQL queries

Example:

UPDATE region SET code='0'||id;

Get next sequence value (update sequence as well)

Example:

SELECT NEXTVAL('hibernate_sequence');

PostGreSQL on Ubuntu (Synaptic installation)

Folder

/etc/postgresql/8.1

Logs

tail /var/log/postgresql/postgresql-8.1-main.log

Allow external hosts

In /etc/postgresql/8.1/main/pg_hba.conf:

host    all         all         206.12.30.0    255.255.255.0      md5

In /etc/postgresql/8.1/main/postgresql.conf:

listen_addresses = '*'

Table sizes

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
ORDER BY total_size DESC;

Source: http://www.postgresql.org/docs/8.2/interactive/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

Large objects size

SELECT 	loid,
pg_size_pretty(count(*) * 2048)
FROM pg_catalog.pg_largeobject
GROUP BY loid
ORDER BY count(*) DESC;
 

Feedback

Muy bueno! muchas gracias!
veronica
Oct 23, 2012
#1