Postgres Tips

PostgresTips.md

JDBC Driver

Name	Value
artifact	postgresql:postgresql:8.4-702.jdbc3
driver	org.postgresql.Driver
JDBC url	jdbc:postgresql://localhost:5432/testdb

Connecting Heroku Postgres

Use ssl to true and sslfactory as NonValidatingFactory

JDBC Url: jdbc:postgresql://ec2-xx.xx.xx.xx.compute-1.amazonaws.com:5432/testdb?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Drop schema without dropping the database

drop schema public cascade;
create schema public;

String aggregation

select t.id, count(t.id), string_agg(cast(t.id as text), ',') from ticket group by t.id having count(t.id) > 1

Staring the database

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Checking the status

pg_ctl -D /usr/local/var/postgres status