« Posts under sql

Run a huge query as fast and safely as possible

September 9, 2011 / posted in sql, Technobable / No Comments

Use this as a last resort

Queries that take a long time are generally a bad thing. If your application requires these kinds of measures to perform its duties, then chances are you really need to revise your table structures and/or you queries – ideally these queries should take seconds at the most, while data warehouse type reporting queries should be on the order of minutes. That said, sometimes you may need to update your entire schema, delete columns on a table with millions of records, or run a stored proc that goes in and cleans up data across several…

[Read the rest]

Write a Stored Procedure in Postgres 8+

May 5, 2010 / posted in sql, Technobable / No Comments

Stored Procs

Sometimes as a developer we’re tasked with data intensive work like importing data into a database, cleaning up sets of incomplete records or transferring data from one table to another through some kind of filter. While our application would normally be in charge of creating and maintaining the data, sometimes we don’t want to end up writing an entire module or mini application to address these tasks. Since they’re data intensive, a stored procedure might be a good approach to take. Stored procedures are a type of program written using a more robust version of sql (structured query…

[Read the rest]

Set up postgres

January 2, 2010 / posted in Fedora, sql / No Comments

Most linux oses come with postgres and mysql out of the box. If yours doesn’t or you want to run a newer version than the the one your os comes with you should be able to install it using the yum installer. Fedora 11 comes with a visual installer you can use to pick and choose what rpms you want running on your machine. Once you have postgres installed on your box you’ll want to initialize some dbs stuff:

[root@bedrock ~]# service postgresql initdb

open up postgres to listen to domains other than localhost:

[root@bedrock ~]# vi /var/lib/pgsql/data/pg_hba.conf

# connect

…

[Read the rest]