PostgreSql 9.3 allows us to declare parameter types to match column types, aka Copying Types. Also it allows us to omit the length of VARCHAR fields, without any performance penalty. These two features make PostgreSql a great back end for agile development, because they make PL/PgSql more resilient to changes. Both features are not in SQL Server 2008 R2. I am not sure about later releases of SQL Server.

As we have seen, copying types reduces the need for tedious error-prone busywork after changes to table structure.

Using VARCHAR without explicitly specifying the size

Theoretically, we might want to specify the length of VARCHAR columns, and use that length as some kind of CHECK constraint. However, in practice I usually do not do it - typically this approach is not good enough, for two reasons.

First reason: usually we do not want silent truncation of values that are too long, as shown in the following example:

Usually we want the code to blow up rather than silently truncate a wrong value. CHECK constraints do exactly what we want: blow up. This is why we typically prefer to use CHECK constraints to validate the length of a VARCHAR value.

Second reason: changing column length may require changes of child tables and parameters. Should we have to change the length of ConfirmationCode in a parent table, for example, we will have modify all the child tables that refer to ConfirmationCode.

This process is slow and expensive.

Similarly, we may have to comb through all our code, modifying all the occurrencies of the parameter @ConfirmationCode VARCHAR(6).This is error-prone and inefficient.

This is why in our SQL Server development we typically declare our VARCHAR columns wider than necessary, and enforce the length limit via a CHECK constraint.

In other words, in my practice the need to specify the length of VARCHAR columns is an inconvenience.

In PostgreSql, this inconvenience is eliminated we are recommended not to specify the length of VARCHAR columns at all. This carries no performance penalty, as discussed here

Quoting from the last link: "Whilecharacter(n)has performance advantages in some other database systems, there is no such advantage inPostgreSQL; in factcharacter(n)is usually the slowest of the three because of its additional storage costs. In most situationstextorcharacter varyingshould be used instead."

I am with you - it makes sense to reach out of our comfort zone and see what others are up to. I am not sure whether "The varchar-feature seems very easy to implement" - that really depends on the quality of their source code. If their source code is already brittle, changing it might be just too difficult. Because their source code is closed, we have no way of knowing that. What do you think?

Yep, we've had a connect item up about being able to declare a variable based on the type of a column, for ages. Sadly in a recent discussion with the product team, I was asked "but why would you want that?". Unfortunately, development on improving T-SQL coding seems to have stopped. SQL 2014 has no T-SQL improvements. Wish it wasn't so.

I am with you - I also feel that "development on improving T-SQL coding seems to have stopped". Regarding "but why would you want that?" - I agree, it is sad when developers do not understand how their product is actually used, although not uncommon.

November 15, 2013 12:33 PM

Leave a Comment

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization.
Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.