sql: separate the notion of a "column type" from that of a "data type"

[knz]

Raphael 'kena' Poss (knz) commented:

Currently all the pages for individual data types int.md string.md etc conflate data type and column type.

In particular they miss the distinction between "inserting a value into a column of a given column type" from "computing a value of a given data type".

This distinction is especially important because:

• we support different data types and column types (e.g. we can compute tuples but not store them, and we can store SERIAL but not compute it);
• for data types without an equivalent column type, it is still possible to create and compute values of that type
• in the documentation about expressions, the reader is in a mindset where expressions can be computed independently from INSERT, and we need to preserve that mindset when we direct the reader to the page for a specific data type. Currently this mindset is disrupted because one of the first thing on each data type page is a reference to "columns of that type".

Steps to improve:

1. (the most important) rephrase the individual pages about data types to avoid referring to column types, avoid phrases like "inserting into a column of that type" and only use INSERT examples as incidental, after another main example without INSERT.
2. create a new page "Storage data types vs. computation data types" which explains which types can be stored in a table and which types cannot, and what happens with the data (representation-wise) when it goes from memory to disk or back again.

Jira Issue: DOC-97

[sploiselle]

@knz Is it a common use case to leverage a database to do computations that it will not store? I realize that it's possible, but curious what the percentage of users are that will actually leverage CockroachDB in this way.

[knz]

Yes we do this all the time in SELECT queries!

The computation in a SELECT can produce values of any data type not only column types.

[sploiselle]

@knz I think that my point is moreso that the common use case of CockroachDB is storing data and not using it for ad-hoc computation through SELECT. Reframing all of the documentation around data types to express an edge case feels too aggressive. The point you're bringing up is salient and important to document; I just want to ensure that we're focusing on how most people will actually interact with the database, and handling uncommon use cases with auxiliary notices (e.g. notes).

[knz]

My point is that the majority of SQL requests from clients will be SELECT statements, and that more than 99% of all expressions ever evaluated by CockroachDB will be to compute values for clients and never reach the database.

Do not let the incidental fact that you personally had to fabricate a lot of INSERT statement to generate your tests and examples bias you from realizing that the majority of interactions with a SQL database happens via SELECT and its rendered expressions.

[jseldess]

@knz, thanks for asking us to revisit this. It'll be helpful to read over your expressions docs before proceeding on reworking the data type pages, which, you're right, do focus on column types. Also, in the meantime, I'm going to look at how other databases handle this in their docs.

Do you have a full list of data types that are not supported as column types, aside from tuples?

[knz]

Data types that cannot be stored:

• tuples
• names
• 1D arrays (currently, but in the future we will likely support them)
• multi-dimensional arrays (planned support probably even more distant in the future)
• entire tables! e.g. as generated by generate_series(...).
  You can have a table as the result of a single value expression. These obviously cannot be inserted into a single table cell, nor (currently) returned to the client, but they can appear as an intermediate value, for example in array(generate_series(1,32)).

Column types that cannot be produced by expressions: SERIAL, BIGINT, INT4, INT8, BYTEA, BLOB, VARCHAR, TEXT, possibly others.