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