CSI: Miami makes for excellent work background noise. Today’s episode caught my ear with a dubstep song at the beginning. Thanks to the wonders of Shazam, I was able to figure out that it’s Droid Prom by Antiserum & Dubsworth Head over to youtube and turn up your speakers. And for even more tech relevance, this episode has an interesting prototype of the future of phones and a bit of a reaching reference to Steve Jobs.
Dubstep song at the beginning of CSI Miami “Friendly Fire”
Posted: January 8th, 2012By: Tommy Unger
Copy data from local file with Postgres and psql
Posted: February 8th, 2011By: Tommy Unger
It was surprisingly hard to find a functional, working example of copying data from a file not based on the server into a PostgreSQL database.
cat file.txt | psql -c “COPY table_name FROM STDIN” -U username database_name
And don’t forget the handy trick for disabling the password prompt in psotgres.
A few helpful search terms:
- Copy file into Postgres from local filesystem
- Copy local file into Postgres from command line
- Using psql to copy file into Postgres database from file system
Running postgresql from command line without password prompt
Posted: November 30th, 2010By: Tommy Unger
Due to security reasons, postgres (in contrast to mysql) does not allow a user to specify the password on the command line. However, there is a quick and hacky workaround which allow a psql command to be run without the password prompt.
From the command line, simply type:
export PGPASSWORD=yourpassword
From there you can now run:
psql -U username database
PostgreSQL Rows to Columns
Posted: November 15th, 2010By: Tommy Unger
Postgres always surprises me with its robustness and cutting edge developments and extensions. postgis was years ahead of the competition. I’ve recently discovered that PostgreSQL offers the rather sweet feature of aggregating multiple rows into an array, and then the implode-like feature of array_to_string to complete the coolness.
The problem for me was simply finding it again. I’d made a number of searches, including:
- postgres rows to columns
- postgres rows to columns array_to_string
- postgres rows to columns separator
- postgres rows to aggregate
- postgres rows to group
- postgres rows to array
The last one was the key which lead me to stack overflow, and was just what I was looking for.
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
Once I’ve created that function, I can now do this to turn my rows into a comma separated list:
SELECT array_to_string(array_accum(engine_name), ', ')
FROM search_engine
which turns this:
Bing
Google
into this:
Bing, Google
CSharp Cassandra Client
Posted: June 28th, 2010By: Tommy Unger
I was surprised to see how hard it is to find a csharp (C#) cassandra client library. There were 50 step processes and incomplete libraries galore. But somehow I finally ran across hectorsharp. It met all my needs and is working like a charm. Throw in the JsonFx library for parsing my JSON returned from Cassandra and I’m good to go.
Pulling All Database Table Sizes from PostgresSQL
Posted: June 10th, 2010By: Tommy Unger
I was surprised that I didn’t easily find a query which gives me all of the table sizes for all of my Postgres database table. I did find this nice bit:
SELECT pg_size_pretty(pg_total_relation_size(table_name))
But I wanted to take it a step further and get the list of the largest tables in my database. This query worked like a charm:
SELECT table_name, pg_size_pretty(pg_total_relation_size(table_name))
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema = 'public'
ORDER BY pg_total_relation_size(table_name) DESC
Finally, I wanted to know if any of these tables had a column name containing the text “org id”.
SELECT t.table_name, pg_size_pretty(pg_total_relation_size(t.table_name))
, case when cn.table_name is not null then ‘Yes’ ELSE ” end as “has_col”
FROM information_schema.tables t
LEFT JOIN
(select distinct c.table_name
from information_schema.columns c
where c.column_name LIKE ‘%org%id%’) cn on cn.table_name = t.table_name
WHERE t.table_type=’BASE TABLE’ AND table_schema = ‘public’
ORDER BY pg_total_relation_size(t.table_name) DESC
;
Configuring MySQL service on Windows to use a different my.ini file
Posted: December 1st, 2009By: Tommy Unger
MySQL 5 conveniently comes with a windows installer which works flawlessly to get the novice user up and running on the ubiquitous open source database. However, another way to put mysql onto a windows box is without the installer by just downloading the zipped binaries.
Once you’ve unzipped the binaries, you have a few steps to take:
Step 1. Run mysqld –install – This will setup the mysql daemon as a windows service. It will look in your windows base directory %WINDIR% for a my.ini file. (On windows the my.conf file is called my.ini, and I have no idea why this is)
Step 2. Open regedit. And change this key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MySQL\ImagePath to this:
“<path-to-mysql>\bin\mysqld” –defaults-file=”<path-to-mysql>\my.ini” MySQL
or this, if you just want to use one of the default medium sized instance ini file:
“<path-to-mysql>\bin\mysqld” –defaults-file=”<path-to-mysql>\my-medium.ini” MySQL
Email Spam and an argument for gmail
Posted: July 16th, 2009By: Tommy Unger
This post Survey: People really do click on spam ads really got me thinking about a few things. First, my history.
Back in some year, let’s say 1998 or maybe 1997 I got my first portal email address (had an aol address and a college address before that). It was tommy_unger@yahoo.com . Yes, I’m putting the email right out there for all of the bots to parse, and I expect the spam onslaught continue. The address is absolutely worthless because it is 99% spam! Much of it in foreign languages (arabic or persian or something from that part of the world seems popular these days. Then of course there’s the cryptic meaningless text, or just “Hi, come to my website so we can have sexy time”. My 8 month old son could probably detect spam better than Yahoo’s algorithms.
Then, finally, came gmail. I signed up with a better address. Same name as above without the underscore, and low-and-behold, I see about one spam message a month. I remember seeign a video a few years back about gmail’s spam detection and it seems right on the money. What I also like is that gmail also rarely(maybe never) has the false positive case where they dump an important message into the spam bucket.
So, back to the article I began with… It mentions:
few users ever click the “report as spam” button on their email client; most just delete spam messages as they arrive (and fail to help train their spam filters in the process)
I’ve got a hunch that gmail has higher rates of this “report as spam” click than the other email clients. I also have a feeling that yahoo mail’s “report as spam” button is just a black hole that ever gets used to clean up anyone’s spam.
Combine gmail’s possible higher “report as spam” click rate with a probable much better algorithm and/or system and you get a much nicer web mail experience in my opinion. So, thanks yahoo tech blog (you still do content well, of course), for reminding me why I love gmail.
Launched a little family blog
Posted: July 12th, 2009By: Tommy Unger
Where are the Ungers? helps people keep track of us. Highlights include video, geocoding (using google) of locations, iphone and other phone pictures, and much more.
Microsoft Product Names
Posted: July 5th, 2009By: Tommy Unger
I’ve come across the longest product name I’ve ever seen for a Microsoft Software Product. Ladies and gentlemen, I present:
Microsoft Office Communications Server 2007 Speech Server Developer Edition
or, as they say around Redmond: MOCS2SSDE
It’s always been a hobby of mine to keep track of what Microsoft is naming its products. It’s always in such sharp contrast with apple’s naming conventions, “iphone”, “imac”, etc. Microsoft seems to think the more it throws into the name, the more likely people will buy it. Well, I don’t buy it. If only MSFT would just come out with a spreadsheet that looks like this in their annual report:
| Class | Product | Revenue | Profit |
| OS | Windows Vista Consumer | 1,000 | 500 |
| OS | Windows Vista Business | 2,500 | 1,200 |
| OS | Windows Server | 2,500 | 1,500 |
| Office Suite | Consumer | 2,500 | 1,500 |
| Office Suite | Business | 12,500 | 6,500 |
| Hardware | xbox | 11,500 | -11,500 |
| Games | Halo, et. al. | 5,500 | -1,500 |
| Who knows? | Enterprise Business Server Small Business Professional Platinum Edition | 1,500 | -111,500 |
As a sometimes shareholder of MSFT, maybe I just don’t dig deeply enough into things. Maybe it’s all out there already. But, if it’s not out there already, this sheet would go a long way to helping me figure out what’s going on in Redmond.