Postgresql: post-its and other lifesavers

postgres_help.rb

# list all databases
psql -l

# list all tables
psql autruche.db -c "\d"

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

# and stop with:
pg_ctl -D /usr/local/var/postgres stop -s -m fast

# update remote database on heroku
heroku db:push postgres://localhost/autruche.db --app autruches

# reset a table serial to 1
ALTER SEQUENCE autruches_id_seq RESTART WITH 1;

# useful psql commands
\list;                          # to list all db at psql prompt
\dt;                            # list all tables in current db
\d+ table_name/view_name;       # describes the structure of each table or view
\e                              # open current query buffer in editor (vim)

# views
CREATE VIEW view_name AS SELECT ....;   # stores a view in your db
\dv                                     # list all views
SELECT * FROM view_name                 # to query (i.e. see) the view
DROP view_name                          # to delete views
ALTER view_name                         # to modify the view
REPLACE view_name                       # to replace the view name with a new query

# use aliases to shorten query strings
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num; 

# query examples
SELECT autruches.callsign, count(flights.id)
FROM flights
JOIN missions ON flights.mission_id = missions.id
JOIN avatars ON flights.avatar_id = avatars.id,
autruches
WHERE missions.campagne_id = 4
AND avatars.autruche_id = autruches.id
GROUP BY callsign;