Jan 20, 2010

empty() function for postgresql in sql

In PostgreSQL the ASCII NULL or empty string \0 is seen as NOT NULL. This is because postgres developers consider any characters data. I know there are better discussions on it but I can't find them right now. Unfortunately the programming language you are using probably doesn't see it the same way. There's a good chance that initialized variables are set to \0 and so when you try to insert from your language to a NOT NULL field with variables that are seen as undefined in your language, postgres accepts it, and now you have fields that you probably consider to have no data but are NOT NULL.

My function possibly would be better called empty_or_whitespace but in my mind any text field that contains only whitespace is empty, and I'd rather have 1 function and 1 regex deal with both than have 2 functions have to be called on every insert/update.

Here is the gist

It returns true if an empty string is found. It really requires a knowledge of SQL, PostgreSQL's CREATE FUNCTION, and Regular Expressions, to understand. Thanks to the wonderfully helpful PostgreSQL community with perfecting it.

In order to use it to keep ASCII NULLS and fields that someone has just entered whitespace into you need to add it to your table as a constraint.

Here's an example create table gist using it.

remember it returns true on null's and whitespace so you have to say NOT emtpy( field ).

Jan 19, 2010

PostgreSQL initial setup (authentication) Part 2

I ran into so problems and lack of information with my last post on this topic. Firstly my syntax for local all all to local all all ident devel
doesn't seem to work in my current setup. It's possible that it has something to do with the configuration of the Debian/Ubuntu server I was basing that against, and now my targets are Arch Linux and Slackware.

So our goal here will be to provide an alternate user that can log in as postgres via ident. why would you want to do this? maybe you're tired of su - to the postgres unix user after logging in as your administrative user (root?).

First we need to open our pg_ident.conf and add the following line.

admin root postgres

So as I said in the last post on this: "admin" is an arbitrary identifier, root is the system user, and postgres is the database user. Now let's go ahead and make sure the postgres system user can still login.

admin postgres postgres

ok that's all we need to do in the pg_ident.conf. Go ahead and open pg_hba.conf and add this line above the default 3 lines in the file.

local all postgres ident map=admin

as a breakdown: local is for socket connections, all means all databases, ident means it's checking for local users, and map=admin says look at the users in the admin identifier in pg_ident.conf.

Now you should be able to run psql dname postgres as root. You will not be able to use the db postgres user as any system user not in the admin map.