PostgreSQL

Installing

Get the port

cd /usr/ports/databases/postgresql83-server
make install clean

Edit /etc/rc.conf to fit your needs

postgresql_enable="YES"
postgresql_data="/home/db/pgsql/data"
postgresql_flags="-w -s -m fast"
postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
postgresql_class="default"

Initialize the database

mkdir /home/db/pgsql/data
chown pgsql /home/db/pgsql/data
initdb

PostgreSQL tips

Add a database and a user

createuser my_user
ALTER USER my_user  WITH PASSWORD '3725cagbcb';
CREATE DATABASE my_base ENCODING 'utf8' OWNER my_user TEMPLATE template0;

Change a password

echo -n "mypasswdmyuser"|md5sum
4f459120acb9663e38b7011b0134b02b
UPDATE pg_authid SET rolpassword='md54f459120acb9663e38b7011b0134b02b' WHERE rolname LIKE 'myuser';

Create a table

CREATE TABLE d_node
(
  id serial NOT NULL,
  "name" character varying(40) NOT NULL,
  CONSTRAINT d_node_pkey PRIMARY KEY (id),
  CONSTRAINT d_node_name_key UNIQUE (name),
  CONSTRAINT d_node_name_check CHECK (name::text <> ''::text)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE d_node OWNER TO my_user;
CREATE INDEX name_idx
  ON d_node
  USING btree
  (name);

Show duplicates

select "preferredUsername" as srv1, "serverId" as sid, count(*) from actor group by srv1,sid HAVING count(*) > 1 ;

Delete duplicates

delete from tag as a using tag as b where a.id < b.id and a.name=b.name;

Show mytable disk usage

\dt+ matable