coding-conundrums

Inserting Multiple Rows into Netezza Table

Standard

access-denied-715x400This one has been irking me for quite a bit. If you have to insert multiple rows into a table from a list or something, you may be tempted to use the standard PostgreSQL method of…

INSERT INTO TABLE_NAME VALUES
(1,2,3),
(4,5,6),
(7,8,9)
;

Be warned – THIS WILL NOT WORK IN NETEZZA! Netezza does not allow for the insertion of multiple rows in one statement if you are using VALUES.

You have two options here: 1) create a file with your values and load it (see Loading Data into Netezza post) or 2) use individual INSERT INTO statements. With a small, simple set of records like the one above, the second method will do fine. It would look like this…

INSERT INTO TABLE_NAME VALUES (1,2,3);
INSERT INTO TABLE_NAME VALUES (4,5,6);
INSERT INTO TABLE_NAME VALUES (7,8,9);

If you have larger sets of data to insert or more complex row structures, consider using an external table.

Official documentation for IBM Netezza INSERT command.

Loading Data into Netezza Using Create External Table

Standard

punch_card.75dpi.rgbNetezza is a super-fast platform for databases… once you have data on it. Somehow, getting the data to the server always seems like a bit of a hassle (admittedly, not as big a hassle as old school punchcards). If you’re using Netezza, you’re probably part of a large organization that may also have some hefty ETL tools that can do the transfer. But if you’re not personally part of the team that does ETL, yet still need to put data onto Netezza, you’ve got to find another way. The EXTERNAL TABLE functionality may just be the solution for you. Continue reading

Common Table Expressions versus Temp Tables in Netezza

Standard

keep-calm-with-or-without-youThis seems to be either a controversial or overly-technical topic: should you use WITH (a common table expression a.k.a. CTE) or a TEMP TABLE. Both can serve similar purposes but each has their own strengths and weaknesses in how they work with other aspects of your query or procedure.

So today we’ll take a look at both without going into crazy detail but covering at least the basics.
Continue reading

Epic Epoch Time in Netezza and PostgreSQL

Standard

EpochTimeBeganEpoch time is both a blessing and a curse. It is super-convenient for counting seconds (and doing calculations based on them) but can also be a pain to try to get into something readable as, or comparable to, a recognizable date. So today we’ll get into and out of epoch to show its flexibility without our brains having to be contortionists too. Continue reading

Netezza Stored Procedures and Optional Arguments

Standard

netezzaLet’s say you have a process that requires a start date, but may or may not have an end date, as a parameter feeding in. How can you accomplish this in Netezza via a stored procedure? The internet has not had great answers to this question but it’s not as hard as some other programmers make it out to be. So today we tackle optional arguments – in easy mode. Continue reading

Fun Uses for Windowing Functions

Standard

I’ve recently come to love (read:be obsessed with) windowing functions in my coding. They’re just so useful and practical.

For those who haven’t experienced the joys of windowing, here’s the deal. They allow you to do calculations across multiple rows without actually having to group, thereby storing aggregate info on each record. That means you keep all the data associated with the row and can add calculated fields that rely on interaction with other rows. Pretty swiffy, huh?

Below are just a few funky functions that I’ve found helpful. I’m not saying that these aren’t resource intensive, but they may just save you from having to join to some crazy aggregation sub-queries and then export to Excel for further manipulation to get the same result. Continue reading

Split Field by Line Break in PostgreSQL

Standard

Broken LinesIn one of the PostgreSQL systems we work with often, there is only one street address field. To get around the need for a change to the table structure, our IT folks simply use a new line character (\n) to denote that there is a second line of address information. The problem becomes, when we go to generate a mailing list, how can we export a list to send to the mailhouse with all address information on one line?

The answer is to use the SPLIT_PART function. See the example below: Continue reading

Changing Sort Order in StudioPress Themes

Standard

Hit a rather irksome problem recently in developing a WordPress website on the StudioPress Genesis framework. I could not find anywhere to change the default sort order (Newest to Oldest by time of publication). Nothing in the functions. Nothing I could find in the lib settings. Nothing in the theme options (which I believe should be changed).

Finally, after hearing back from the developers and having them point me to a nice, clean function for supposedly resolving this, I gave it a try. It had the unfortunate side effect of knocking out my static homepage because it went into the loop, thought the homepage had a loop, and decided to simply sort all the posts in the database into that page. At least it showed them in the specified order.

Thankfully, I came up with a quick way to resolve this pesky problem. All you have to do is wrap the function with a check to determine if it is a page. Here’s the code: Continue reading

Time Part in PostgreSQL

Standard

If you’ve worked with datetime formats at all in PostgreSQL (or any other SQL version) before, you’ve probably dealt with date_part (or extract). In pgSQL, this will pull out the numeric value of the part of the date that you have specified. For instance:

/* Pull the Month from the timestamp without time zone */
date_part('month','2012-03-13 12:45:22')

/*Yields*/
3

Another common one if you’re trying to eliminate the time portion is to truncate the date using date_trunc.

/* Truncate date so all timestamps map to midnight */
date_trunc('day','2012-03-13 12:45:22')

/* Yields */
'2012-03-13 00:00:00'

Earlier today, I went looking for a similar construct to extract and isolate only the time portion and ran across a rather cool little feature. Seems there is a built-in function housed in the pg_catalog schema that allows for very quick parsing. It’s called, unsurprisingly, time.

Extracting Time Part from Timestamp Example

select pg_catalog.time('2012-03-13 12:45:22');

/* Yields */
'12:45:22'

It may not look like much code (which, to be fair, I greatly appreciated), but this works like a charm. You can then use the time syntax for any grouping or editing you want to do. For instance, this was being used to chunk up the day into meal periods for a client analysis so we could state that transaction_time between ’12:00:00′ and ’15:00:00′ is lunch with a simple case statement.

Further Investigation

After finding this awesome little tidbit, I did some more research on this mysterious pg_catalog schema and it turns out that there are TONS of functions hidden away in there (including one simply called ‘date’ that may suit better than date_trunc if you want only the date portion). Found a pretty comprehensive schema of pg_catalog here and will be testing out the functions as needed.

Do you have a better way of handling different components of dates and timestamps? I’d love to hear about it. Please leave a comment below.