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

Correlated Query Error in Netezza

Standard
Beta... you know... for correlation?

Beta… you know… for correlation?

Fun and interesting error today. Here’s the actual error text:

Error 2: this form of correlated query is not supported – consider rewriting

I’d never heard of this “correlated query” business before so I had to look it up to sort out what was going on. Turns out that you can reference a table in the outside part of a query from within a subquery by calling the alias… Or, rather, you can’t in Netezza.

Tip of the day:

Check for alias references in your subquery and get rid of them Continue reading

Zero Padding in PostgreSQL

Standard

Fun new conundrum in dealing with data formats/displays in PostgreSQL! A client recently requested that we provide a data extract that included percentages as a three character number, left zero-padded, rather than a decimal/numeric. There’s not a great way in Postgres to show a numeric with leading zeros (actually, I’ve yet to find a data type that does this consistently as a built-in to any platform). Instead, you have to do a little bit of work to get to your end result as a character (or text) field. Continue reading

Calculating Months Between Two Dates in PostgreSQL

Standard

Calendar Page EOMOne of the most basic calculations on dates is to tell the time elapsed between two dates. Often it is more helpful to show the date as a number of months rather than a number of days. In PostgreSQL, subtracting one date from another will yield a number of days that then takes a tremendously complex formula to manipulate into months.

The best way I have found to get around this is to use the built in AGE function. The age function calculates the difference between two dates and returns an interval. This may not seem like we’ve gotten very far in calculating the number of months between the two dates, but stick with me on this one. You can then EXTRACT the pieces of the interval needed to calculate months and use them in a simple equation.

EXTRACT(year FROM age(end_date,start_date))*12 + EXTRACT(month FROM age(end_date,start_date))

Continue reading