Disclaimer:
These pages about different languages / apis / best practices were mostly jotted down quckily and rarely corrected afterwards.
The languages / apis / best practices may have changed over time (e.g. the facebook api being a prime example), so what was documented as a good way to do something at the time might be outdated when you read it (some pages here are over 15 years old).
Just as a reminder.

Postgresql developer notes regarding database

developer notes for the Postgres database Postgres

How to make Postgres reload pg_hba.conf


pg_ctl reload

Import data from CSV file into table

As superuser


COPY some_table (some_column, some_column2) FROM '/tmp/somefile.csv' DELIMITERS ',' CSV;

Export data to CSV file from table/view

As superuser


\o outfile.csv
COPY ( SELECT param1, param2, param3 from myview) TO STDOUT WITH CSV HEADER;


\o myfile.csv
COPY (
      
) TO STDOUT CSV;

From a select query



\copy (select name from user) To '/tmp/names.txt' With CSV;

Dump and restore a db in postgres


pg_dump -p 1999 -U dilbert databasename > /tmp/databasedump.txt


psql -U dogbert -p 2000 newdatabasename < databasedump.txt

http://carlosbecker.com/posts/dump-postgres-table-inserts


    $ pg_dump \
  -h localhost \
  -p 5432 \
  -U user -W \
  --table="table-name" \
  --data-only \
  --column-inserts \
  database-name > table.sql

    $ psql \
  -h localhost \
  -p 5432 \
  -U user \
  database-name \
  -f table.sql

Analyze queries

Write

explain analyze

and after that the select statement to get an analyze of how much time it take time etc.

Grant superuser to a user

alter user [username] with superuser;

Kill a process

 select pg_terminate_backend(9423);
 pg_terminate_backend 
----------------------
 t
(1 row)

pond91=# \q

pg_cancel_backend() doesnt seem to do anything

Find all constraints

http://stackoverflow.com/questions/16830740/list-constraints-for-all-tables-with-different-owners-in-postgresql

Find the function names in postgresql

SELECT  p.proname
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p
ON      p.pronamespace = n.oid
WHERE   n.nspname = 'public'

Create user and database

create user scott with password 'somePassword';
create database someName with owner scott template template0 encoding 'utf8';

Change varchar size

http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data

See definition of trigger (or other stuff)

\df+ [triggername]

See what is allocated on a tablespace

select relname from pg_class where reltablespace=(select oid from pg_tablespace where spcname='theTableSpaceName');

Find whole words only

    select keywords from sometable where keywords ~* '\y(wrestling|golf)\y' limit 20;

More programming related pages

Workflow: Release process
Workflow: Bug tracking
Teambox (Redbooth) - Mantis connector
Design Patterns
Git & Github

Go / Golang
CVS
CVS backup script
Distribution process
Installation script

Java Server Faces
Facelets
jibx
jBoss
jBpm

Perl tips
Perl links
PostgreSQL
Python / py2app
Shell scripts

Xslt
Node.js
Facebook / Opengraph
PHP developer notes
Redbooth API through php

Website optimization
jqTableKit demo
Javascript / html / css links