PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

3-Nov-2003 Issue: 50

Archives | General Tidbits | Google General Bits | Docs | Castellano | Português | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

Fifty Issues Old
General Bits 03-Nov-2003

It has been my custom to include an editorial or a survey every ten issues. And now it has been 50 weeks that this column has been published. This is almost a full year of publication. I hope you have found that the quality has improved over this time.

I celebrate the addition of both Portuguese and Spanish language translations. Juliano da Silva and Alvaro Herrera have taken on a difficult task and have done a marvelous job helping to spread the word to their communities.

In the future, I would like to solicit more contributions of functions, schemas and tips from you directly. I will be expanding the Tidbits archives to include more of the code from the various issues. I would like Tidbits to be a good place to look for examples of how to do interesting things.

Two examples of contributions are in this issue. The first is a How To guide and the second is a useful plpgsql function. By sharing both various documents, like Josh's performance documents, and annotated code, like Stephan Szabo's Set Returning Functions examples, I hope to provide a rich resource for PostgreSQL.

Collaboration is the word of the week. Comments, suggestions, documents and code samples can all be submitted to elein.

Editor: elein at
Debian eRServer How To
[Erserver-general] Erserver Debian HOWTO URL 24-Oct-2003

Ivailo T. Ivanov of Mag Studio has written a brief eRServer Debian How To guide. It will provide you with a good start around some of the initial problems with installation with Debian. It is also available from the Tidbits area.

Editor's Note: Full sequence of steps were not tested

Contributors: Ivailo Toshev Ivanov ivo at
Credit Card Validation
[sfpug] [cool hack] Credit Card validator 30-Oct-2003

David Fetter has ported the Business::CreditCard module into plpgsql. This is very useful if you are not able to use plperl for any reason in your database and still need to do credit card validation. The evaluation of the card numbers is also a good example of how to use the ~ operator.

This is a good example of the kind of processing necessary for business applications that can be done in the server. As many applications access the database, it is good to have a database centered validation routine so that all of the application do the validation consistently.

    cc_num     ALIAS FOR $1;
    i          INTEGER;
    d          VARCHAR;
    weight     INTEGER;
    checksum   INTEGER := 0;
    oddness    INTEGER;
    num        INTEGER;
    tempstring VARCHAR;
    -- Strip out non-digits.  Oh, for an s/// construct! :)
    tempstring := '''';
    FOR i IN 1 .. length(cc_num)
        d := SUBSTRING(cc_num, i, 1);
        IF d ~ ''[0-9]''
            tempstring := tempstring || d;
        END IF;
    -- Check length for reasonableness.
    IF (length(tempstring) < 13)
        RAISE NOTICE ''% is too short to be a credit card number.'', cc_num;
    END IF;
    -- Do a Luhn checksum.
    oddness := length(tempstring) % 2;
    FOR i IN 1 .. length(tempstring)
        num := SUBSTRING(tempstring, i, 1)::integer;
        IF (oddness = 0 AND i % 2 = 1)
            num := num * 2;
        ELSIF (oddness = 1 AND i%2 = 0)
            num := num * 2;
        END IF;
        IF num > 9
            num := num - 9;
        END IF;
        checksum := checksum + num;
    IF checksum % 10 <> 0
        RAISE NOTICE ''% does not have a valid checksum.'', cc_num;
    END IF;
    IF tempstring ~ ''^3[47][0-9]{13}$''
        RAISE NOTICE ''% is an American Express card.'', cc_num;
    ELSIF tempstring ~ ''^4[0-9]{12}([0-9]{3})?$''
        RAISE NOTICE ''% is a VISA card.'', cc_num;
    ELSIF tempstring ~ ''^5[1-5][0-9]{14}$''
        RAISE NOTICE ''% is a MasterCard.'', cc_num;
    ELSIF tempstring ~ ''^6011[0-9]{12}$''
        RAISE NOTICE ''% is a Discover card.'', cc_num;
    ELSIF tempstring ~ ''^3(0[0-5]|[68][0-9])[0-9]{11}$''
        RAISE NOTICE ''% is a Diners Club/Carte Blanche.'', cc_num;
    ELSIF tempstring ~ ''^2(014|149)\d{11}$''
        RAISE NOTICE ''% is an enRoute card.'', cc_num;
    ELSIF tempstring ~ ''^(3\d{4}|2131|1800)\d{11}$''
        RAISE NOTICE ''% is a JCB card.'', cc_num;
    ELSIF tempstring ~ ''^56(10\d\d|022[1-5])\d{10}$''
        RAISE NOTICE ''% is a BankCard.'', cc_num;
    END IF;
' LANGUAGE 'plpgsql';

Contributors: David Fetter david at
A Few Optimization Questions Answered
[GENERAL] formatting of SQL sent by PHP to postgres 30-Oct-2003

Question: Is the semicolon at the end of SQL superflous when sent to Postgres from PHP?
Answer: Yes, you can do without the semicolon when sending statements through PHP.

Question: Does the large amount of whitespace generated in PHP SQL statements have an effect on the speed of the query?
Answer: No. It is probably unmeasureable unless there are kilobytes of spaces.

Question: How can I speed up

select max(id) from table;
Answer: Replace it with
select id from table order by id desc limit 1;

Question: Why is one faster than the other? They are the same on SQLServer.
Answer: Due to MVCC, PostgreSQL doesn't use the index for aggregation so max() is slower than the sort which does use the index. Note that this only works when there are no NULLs in the column and there is an index on the column.

For general information and discussion on PostgreSQL Performance, join the pgsql-performance mailing list.

Contributors: Ken Guest, scott.marlowe, David Green, Robert Treat, Greg Stark, Hervé Piedvache, Tom Lane tgl at
Perl Modules for PostgreSQL
[GENERAL] Best Perl Option? 30-Oct-2003

The best perl module for PostgreSQL is DBD::Pg. now hosted on gborg. is also available on gborg or via CPAN.

Contributors: David Busby busby at Doug McNaught doug at Network Administrator netadmin at

Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009

Search General Bits & Search WWW