Small. Fast. Reliable.
Choose any three.

This information is obsolete. You are looking at the CVSTrac source management system display for SQLite that was replaced by Fossil on 2009-08-11. The information shown here has not been updated since that cut-over. These pages are retained for historical reference only.

This page describes how SQLite determines the names of columns in the results set of a select. All the rules on this page except for case 1 (the "AS" rule) are subject to change from one release of SQLite to the next. The rules described here apply to SQLite version 3.6.0. Prior versions of SQLite used different rules. If an application depends on the column name, then it should use an AS clause to set the column name explicitly.

There are two pragmas which control how column names are chosen:

   PRAGMA short_column_names;
   PRAGMA full_column_names;

Either pragma can be set to "true" or "false" or "on" or "off" or "0" or "1". For example:

   PRAGMA short_column_names=ON;
   PRAGMA full_column_names=OFF;

If you omit the "=VALUE" part then the pragma returns the current setting. The column names generated depend on the value of both pragmas at the time the query statement was prepared. The default settings for these pragmas are as shown above, short=ON and full=OFF. If you want to change these settings, you will have to do so separately for each database connection. The changes are not persistent. They revert to their default value with each new connection.

Case 1: Result set expressions contain an "AS" clause

Whenever there is an AS clause after the expression that defines a column of the result set, the string that follows the AS keyword becomes the name of the column in the result set. The AS clause overrides all other behavior. If an AS clause is present, it does not matter what the settings of the short_column_name and full_column_name pragmas are - the name of the column is always the string that follows the AS keyword.

Case 2: Non-trivial result set expressions

A non-trivial result set expression is one that contains something more than the name of a column from a table in the FROM clause. Any expression that involves the use of a function or a mathematical operator is considered non-trival. However, a table column name that is enclosed in parentheses is still considered trivial.

The name of any non-trivial result set expression is a copy of the text of that expression as it appeared in the SELECT statement.

Case 3: short_column_names=ON

If cases 1 and 2 do not apply and short_column_names=ON then full_column_names is ignored. The name of the result set column is the name of the corresponding table column as it appears in the original CREATE TABLE statement. So, for example, if you have the following CREATE TABLE:

   CREATE TABLE example1(
      abc INTEGER PRIMARY KEY,
      Xyz text
   );

And you do the following query:

   SELECT rowid, xyz FROM example1;

The names of your result set columns will be "abc" and "Xyz", not "rowid" and "xyz". The names of the columns in the CREATE TABLE statement are used, not the names in the SELECT statement.

Case 4: short_column_names=OFF and full_column_names=OFF

If cases 1 and 2 do not apply and short_column_names=OFF and full_column_names=OFF then the column name is original expression text as in case 2. If the column has no expression text (because it is autmatically generated using the "*" and "TABLE.*" wildcard) then the short column names are used as in case 3.

Case 5: short_column_names=OFF and full_column_names=ON

If cases 1 and 2 do not apply and short_column_names=OFF and full_column_names=ON then the result set column name is constructed as "TABLE.COLUMN" where TABLE is the name of the table from which the data is taken and COLUMN is the name of the column within TABLE from which the data was taken.

Case 6: Column Names for VIEWs

In the absence of AS clauses, the column names for VIEWs are computed as if short_column_names=ON. This is true regardless of the actual pragma settings.