Internals: Null value and error handling - ajam/tablespoon GitHub Wiki

Tablespoon is very lenient when it comes to datasets that contain null values.

It also makes an effort to highlight errors in your create, insert and query statements.

Reading null values

Let's say you're trying to create a table from this piece of data that includes null values for the average temperature:

var data = [
  {
    city: "New York",
    avg_temperature: null,
    country: 'USA'
  },
  {
    city: 'Los Angeles',
    avg_temperature: 72,
    country: 'USA'
  }]

SQL, even with the wonders of PostgreSQL 9.3, requires that each field be typed, which is impossible if a field is null. Tablespoon is very lenient, however, and will look through as much of your data as necessary until it comes across a non-null or undefined data field.

For this data, it will see that avg_temperature is null and skip to the next object. Since it knows the types for the other columns, Tablespoon will only test the type of avg_temperature. If this field was also null, Tablespoon will throw an error since it can't create a table without a typed column.

What if the data has arrays like this one of low and high temperatures?

var data = [
  {
    city: "New York",
    temperature_range: [null, null],
    country: 'USA'
  },
  {
    city: 'Los Angeles',
    temperature_range: [null, 102],
    country: 'USA'
  }]

Tablespoon will see that temperature_range is an array but it doesn't know what kind of an array, which is a problem because PostgreSQL differentiates between arrays that contain numbers versus strings. It doesn't support mixed-type arrays, unfortunately.

With this data, Tablespoon will test each element in the array before moving on to the next object. So if you have at least one value in array, Tablespoon should be able to generate your table schema.

Errors in queries

If you run into an error in your query, Tablespoon constructs a nice error message for you, showing 100 characters on either side of your error and adding a magenta ^ at the point where SQL had a problem. At the moment, this only works for tables in PostgreSQL mode, but looking into a way to support it in SQLite mode.