Constraint Checks And Dirty Data

 It is much easier to keep bad data out of your database than it is to have to correct it later. Those costs are measured in time, money, and sanity. One of the easier ways of protecting your data is with a constraint check. 

I was recently approached by someone who urgently needed a constraint check and could afford to 'fix' the bad data after the constraint was in place. They had to quickly keep new 'bad' data out and could afford to correct the old 'bad' data at leisure. 

The Overly Simplified Example

To illustrate how to do just that, here is an example of a table and some simple data.

demo=# create table x1 (a int);
CREATE TABLE
demo=# insert into x1 (a) values (10),(20),(30),(-5),(1);
INSERT 0 5
demo=#

Sometime later, it is discovered that any value of a needs to be greater than five. If we add the constraint, the system will balk and refuse the request.

demo=# alter table x1 add constraint x_id_gt_5 check (a > 5);
ERROR:  check constraint "x_id_gt_5" of relation "x1" is violated by some row

The trick is to tell the ALTER TABLE command to not validate the constraint check. The constraint is added, but the system does not ensure the data conforms to that check. This is done by adding NOT VALID to the ALTER TABLE command.

demo=# alter table x1 add constraint a_gt_5 check (a > 5) not valid;
ALTER TABLE

The data is in the same state as before the constraint was added. The old 'bad' data is still there and can be corrected or deleted later.

demo=# select a from x1;
 a  
----
 10
 20
 30
 -5
  1
(5 rows)

demo=# 

Any new attempts to add new 'bad' data will fail.

demo=# insert into x1 (a) values (3);
ERROR:  new row for relation "x1" violates check constraint "a_gt_5"
DETAIL:  Failing row contains (3).
demo=# 

Now, it is time to fix the rows that do not conform to the check. When that is done, the check can be validated.

demo=# alter table x1 validate constraint a_gt_5;
ALTER TABLE
demo=# 



Constraint Hints


1. Name your constraints. The name 'a_gt_5' describes the constraint and explains why the data was rejected. If you do not name the constraint, the system will assign a name that will list the table name, the column name, the word 'check,' and no clue what went wrong. 

Look at the following definitions. Seeing a_gt_t error informs you what is wrong, But x1_a_check is nebulous, at best. Remember that you may be the poor person trying to figure out why the inserts are failing at three in the morning.

demo=# \d x1
                 Table "public.x1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Check constraints:
    "a_gt_5" CHECK (a > 5) 
    "x1_a_check" CHECK (a > 6)

demo=# 

2. Double-check the constraints.  In the above, having a check on a being greater than five and another greater than six makes no sense.  This type of problem happens as the tables age and multiple people make changes without making sure there no conflict or similar action already established. 


Comments

Post a Comment

Popular posts from this blog

How Does PostgreSQL Store Your Data?

PostgreSQL is an amazing database. And some of us wonder how it takes what we enter on a keyboard and stores it. The following is peeking into the 'clockworks' to see how things are done. Step 1 On a fresh install of Ubuntu with PostgreSQL 17, let us create a database. stoker@ThinkPad:~$ createdb demo Password:  stoker@ThinkPad:~$ psql demo Password for user stoker:  psql (17.2 (Ubuntu 17.2-1.pgdg24.04+1)) Type "help" for help. Now that we are in this new database, we can ask where PostgreSQL stores the data by looking for the data directory. demo=# show data_directory;        data_directory         -----------------------------  /var/lib/postgresql/17/main (1 row) This informs us that our data will be somewhere under the /var/lib/postgresql17/main directory.  Step 2 Issuing a command to create a table starts of series of events that we will study in later posts. Right now, we want to see where the stuff goes. Here a simp...
Read more

Now That We Know Where PostgreSQL Stores Data, We Can Look At How

 Last time , we started looking at how PostgreSQL stores data by discovering where the data is stored and had a quick peek at that data. There is only one record at this point. demo=# SELECT * FROM ex01;  a | b  ---+---  1 | 1 (1 row) And the contents of that file are not precisely illuminating at first glance. For now, just trust me that our row is in there, and you will see where in just a little bit. root@ThinkPad:/var/lib/postgresql/17/main/base/16389# od -c 16390 0000000  \0  \0  \0  \0   (   z 227 001  \0  \0  \0  \0 034  \0 340 037 0000020  \0     004      \0  \0  \0  \0 340 237   @  \0  \0  \0  \0  \0 0000040  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0  \0 * 0017740 362 002  \0  \0  \0  \0  \...
Read more

Beginning PostgreSQL Transactions

Transactions can be a rough concept for those new to databases. Those new to PostgreSQL will find the implementation much different than those in other databases.  The default isolation level in PostgreSQL is READ COMMITTED so that you only see data that has been committed. If someone else works on the same table, you will not see those changes until they are committed. Start with an elementary table: demo=# create table ex03 (a int); CREATE TABLE demo=# insert into ex03 (a) values (1); INSERT 0 1 Behind the scenes, the system sets up some information to track your data, as we learned here and here .  demo=# select a, ctid, xmin, xmax, cmin, cmax from ex03;  a | ctid  | xmin | xmax | cmin | cmax  ---+-------+------+------+------+------  1 | (0,1) |  768 |    0 |    0 |    0 (1 row) The CTID was introduced last time , and the other columns may be new. The documentation details their definitions. For this blog, we are goin...
Read more