If you want to find the location of your log file in Postgres, you'll need to hop into a Psql session.. psql dbname Then it's as simple as running.. show data_directory ; Which will output the data directory, in my case.. /Users/sea...

If you're working with SQLAlchemy, the best database driver for Python, and want to see only SQL syntax errors, you need to use the StatementError exception class. On it, is an attribute `orig`, that contains just the SQL syntax error... and not any data...

Here is some code just in case you want to look at and query the queries hitting your MySQL database. Enter this from the mysql client console. mysql> SET GLOBAL log_output = 'TABLE' mysql> SET GLOBAL general_log = 'ON'; mysql> select event_ti...

Unfortunately PHP's PDO constructor doesn't take a database connection url (in a format that Heroku makes available as a config var) as an argument. It has its own, rather odd syntax. However, it's easy enough to extract url parts with the `parse_url`...

Here is a code snippet that demonstrates how to set up a __ManyToMany through__ relationship in Django. In Rails, the equivalent would be called a __has_many through__ association. If you set the __through__ argument on the ManyToManyField, Django wil...

Here is a little snippet that will return new signups (or new records) for today select id, email, created_at::date date from signups where email not in (select distinct email from signups where created_at < current_date)

Here are a few snippets for cross database queries. It's important to note that you must be explicit in enumerating the columns and types you are querying. Otherwise, things will probably not work as expected. -- enable extension create extension db...

Heroku is awesome! Let's say we want to copy a production database to a staging database. We can use the `pg:backups:restore` command to accomplish this. Here is an example. For the source database we are using the `production-app-name` and for stagi...

When Heroku creates a dump file of your Postgres database it uses the `-Fc` option It is equivalent to running pg_dump -Fc -d name_of_db > name_of_db.dump This command will let you import your database with the `pg_restore` command pg_rest...

If you run into this error when trying to create a user in mysql, chances are you already have this user account created. create user 'someuser'@'localhost' identified by 'somepassword'; ERROR 1396 (HY000): Operation CREATE USER failed for 'someuser...

This query and pattern will return urls in text all within a single SQL query. select substring(column_name from '.*://([^/]*)') as domain_name from table_name; And here it is in a larger query, say for retrieving page view counts for referrers. ...

If you get this error message ActiveRecord::ConnectionTimeoutError could not obtain a database connection within 5.000 seconds (waited 5.001 seconds) Try closing the connection after each request using "after" in Sintara. # app.rb after { A...

Let's create a table that will hold some dummy data. > create table fruits (id serial primary key, name varchar, color varchar); > insert into fruits (name, color) values ('apple', 'red'); > insert into fruits (name, color) values ('ap...

If you have an string of numbers like "1,2,3" and you want to turn it into an array of integers you need to cast it into an integer array type. "{1,2,3}"::int[] This is commonly used together when grabbing a set using the ANY clause. sele...

It's trivial to export data from a mysql database with Ruby and ActiveRecord. All you have to do is establish a connection and define a class that represents the table. require 'rubygems' require 'active_record' ActiveRecord::Base.establish_con...

Output .sql file for MySQL but without the drop table syntax before table name use the --skip-add-drop-table flag mysqldump -u root -p database_name --skip-add-drop-table --skip-lock-tables > database_name.sql

MySQL doesn't support having two columns with time stamping on both initialization and/or on updating at the same time. It would be nice to be able to do *this* where the created_at column gets the current_timestamp on initialization and the updated_at ge...

wget http://redis.googlecode.com/files/redis-0.900_2.tar.gz tar xzvf redis-0.900_2.tar.gz cd redis-0.900 make mv redis-server /usr/bin/ mv redis-cli /usr/bin/