How to Find Postgres Log File and Postgres Data Directory from PSQL
If you want to find the location of your log file in Postgres, you'll need to hop into a Psql session..
Then it's as simple as running..
show data_directory ;
Which will output the data directory, in my case..
Written by Sean Behan on 05/25/2018
How to Use Named Variables with Postgres and PHP PDO Driver
You can write reusable scripts with Postgres by taking advantage of named variables.
A named variable starts with a `:` in your sql script. Here is an example
You can then use this statement with `psql` and the `--variable` fl...
Written by Sean Behan on 11/11/2017
Connect to Postgres on Heroku using DATABASE_URL Config Var with PHP and PDO
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`...
Written by Sean Behan on 11/10/2017
How to Make Cross Database Queries with Postgres and DBLink Extension
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...
Written by Sean Behan on 03/18/2017
How to Import/Export a Database from One Heroku App to Another Heroku App
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...
Written by Sean Behan on 03/07/2017
How To Create a Dump File in Postgres Compatible with Heroku
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
Written by Sean Behan on 03/06/2017
How to Enable UUIDs in Postgres
The first thing you'll need to do is enable the extension
create extension "uuid-ossp";
To test that it's working
For more info on which version of the algorithm you should use refer to the documentation.
Written by Sean Behan on 03/03/2017
Extract Domain Names From Links in Text with Postgres and a Single SQL Query
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.
Written by Sean Behan on 11/23/2013
How To Install Pyscopg2 Python Postgres Driver on Mac OSX with Homebrew, Postgres.app and VirtualEnv
You might have to append the path of the Postgres.app bin directory to your path in order to install the Python driver for Posgres.
To find the location of the application try this
Written by Sean Behan on 11/19/2013
How To Remove Duplicates From a Table with Postgres
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...
Written by Sean Behan on 10/08/2013
How to cast a string of comma separated numbers into an array of integers for Postgres
If you have an string of numbers like
and you want to turn it into an array of integers you need to cast it into an integer array type.
This is commonly used together when grabbing a set using the ANY clause.
Written by Sean Behan on 09/26/2013