Pivot Columns Values into a Set #PostgreSQL

Depending on the project you are working on, you may have to generate a set which contains every value found (across several columns) into a set consisting of a single column.

For example you have 3 columns A, B, C:

a1, b1, c1

a2, b2, c2

a3, b3, b3

and you need:

a1

a2

a3

b1

b2

b3

c1

c2

c3

To achieve this you can use the array function called unnest:

select unnest( array[A, B, C] )

from foo

This takes an array of tuples created by array[a,b,c] which looks like this:

{ {a1, b1, c1}, {a2, b2, c2}, {a3, b3, b3} }

and returns a set that contains every value in the array of column values.

Some caveats to consider:

1. The types of the columns must be equivalent or they must be cast
2. The unnest function is available in PostgreSQL 8.4 or greater

Filter the number of databases in pgAdmin

Quick post about reducing the amount of scrolling you have to do in pgAdmin. If you work on a PostgreSQL instance with 124 databases like I do, it helps to be able to filter the list of databases so only the databases you work on show up. This way you don't have to scroll and scan for the DB you want.
To do this:

1. Right click on the connection to the instance you want to change
2. Click on Properties
3. In the "DB restriction" text box add the name of each database you work on single quoted and comma delimited. For example: 'database1','database21','database101'
4. Restart pgAdmin and you are all set. Now you will only see the databases you work with.

Running Amazon's Kindle app on Linux

I was trying to add a comment to this article which goes through the steps needed to get the amazon kindle application running on Linux. The instructions do not work as stated because many people running Ubuntu 10.10 have wine 1.2 which does not work with Amazon's application.

Here is the original post, which is the first post that comes up with you Google it.

My comment was flagged as spam, so I wanted to make sure people know how to get it working. Here is my comment:

This is working for wine 1.3.15 on Ubuntu 10.10. It does not work with the stock version of Wine that is in the default Ubuntu 10.10 repo.
To get the latest version of Wine, you have to add the WineQH repository by following these instructions: http://www.winehq.org/download/deb

After that, uninstall any wine 1.2 packages and install the package called "wine1.3"
I used the Synaptic Package Manager which you can find under
System->Administration

Alternatively you can run the following on the command line after you have uninstalled any wine 1.2 packages you may have:
sudo add-apt-repository ppa:ubuntu-wine/ppa
sudo apt-get update
sudo apt-get install wine1.3

To find what is currently working go here

Hope this helps and happy reading!

@Genspire