Production psql Prompts

I deal with lots of production.  When you're dealing with multiple production machines, it's important to know many things: Who you are What you're connected to Where you are Why you are connected When you are connected Most prompts don't give you any of this detail.  If you're familiar with bash, you probably have heard [...]

By | October 25th, 2016|Categories: Scott PostgreSQL, tip|Tags: , , , |7 Comments

FTP I/O from PostgreSQL

PostgreSQL applications at times need to communicate with external servers using FTP. Oracle has the UTL_TCP package that is very useful for PL/SQL applications. PostgreSQL doesn’t have an equivalent package, but it does have something really powerful … the ability to write functions in various scripting languages. PostgreSQL has four languages that are part of [...]

By | August 21st, 2014|Categories: PostgreSQL, tip|Comments Off on FTP I/O from PostgreSQL

Hierarchical Queries using PostgreSQL

One of the major hurdles faced when converting Oracle’s PL/SQL code to any other database is to find the equivalent of START WITH and CONNECT BY PRIOR. This SQL in Oracle provides a very powerful way of writing hierarchical queries, something that comes in really handy when dealing with hierarchical data – this is fairly [...]

By | August 15th, 2014|Categories: PostgreSQL, tip|Comments Off on Hierarchical Queries using PostgreSQL

Eclipse for Perl Developers

I've spent a lot of time in Perl using Eclipse lately. Personally, I prefer to use vim for most of my development. Lately though, I've been spending a lot of time in some complex java with many different references to many different classes; I'll be honest, I am enjoying the index and code-completion. I have [...]

By | June 11th, 2013|Categories: Fun thoughts, tip|Tags: , , , |Comments Off on Eclipse for Perl Developers

Matching Server and Client Constraints

Postgres supports all the SQL-standard constraint types, e.g. UNIQUE, CHECK. These work fine at the server level: CREATE TABLE ledger (id SERIAL, balance NUMERIC(10,2) CHECK (balance > 0)); INSERT INTO ledger VALUES (DEFAULT, -2.00); ERROR: new...

By | April 11th, 2013|Categories: tip|Comments Off on Matching Server and Client Constraints

Dynamic Columns

SQL has never been good at handling dynamic columns — it can easily process tables with a fixed number of columns, but dynamic column handling has always been a challenge. (Dynamic columns are often used to implement user-defined fields.) One appr...

By | April 10th, 2013|Categories: tip|Comments Off on Dynamic Columns

Partial Indexes

Having covered expression indexes, I want to cover another index feature — partial indexes. Partial indexes allow you to create an index on part of a table. Why would you want to do that? index only frequently-accessed values, allowing smaller...

By | April 8th, 2013|Categories: tip|Comments Off on Partial Indexes

A Java Based Command Line PortChecker

The below code snippet is a lovely little piece of Java magic the we use in various installation and configuration scenarios to verify if a port is available or busy [java] import java.io.*; import java.net.*; public class PortChecker { public static void main(String[] args) { int port=0; if (args.length == 1) { try { port [...]

By | April 7th, 2013|Categories: tip|Tags: |Comments Off on A Java Based Command Line PortChecker

Expression Indexes

Having covered virtual columns recently, I wanted to cover one additional type of stored value, expression indexes. Unlike virtual columns, which are computed on each access, expression indexes are stored in index files, allowing rapid access. Let's...

By | April 6th, 2013|Categories: tip|Comments Off on Expression Indexes

Setting JAVA_HOME for JDK6 on Linux and OSX

Here is a useful little code snippet I use as part of BigSQL. [bash] if [ "x$JAVA_HOME" == "x" ]; then macosx_j6="/System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home" ubuntu_j6="/usr/lib/jvm/java-6-openjdk-amd64" redhat_j6="/usr/lib/jvm/java-1.6.0-openjdk.x86_64" if [ -d "$macosx_j6" ]; then export JAVA_HOME="$macosx_j6" elif [ -d "$ubuntu_j6" ]; then export JAVA_HOME="$ubuntu_j6" elif [ -d "$redhat_j6" ]; then export JAVA_HOME="$redhat_j6" else echo "WARNING: Cannot figure out JAVA_HOME." [...]

By | April 5th, 2013|Categories: tip|Tags: , |Comments Off on Setting JAVA_HOME for JDK6 on Linux and OSX