CODEWITHSUNDEEP
sqlsqlite
Lukas Eder
Lukas Eder

Reputation: 220842

How to get SQLite column types from view columns

When defining a view like this:

CREATE TABLE x (a VARCHAR(10));
CREATE VIEW v AS SELECT a, a || ' ' AS b FROM x;

I now want to discover the column types of the view's columns using:

PRAGMA table_info('v');

Unfortunately, this results in

cid |name |type        |notnull |dflt_value |pk |
----|-----|------------|--------|-----------|---|
0   |a    |VARCHAR(10) |0       |           |0  |
1   |b    |            |0       |           |0  |

The column type is absent for any kind of column expression. Is there a different way to define the view and / or query the table info in order to get a column type or is that just how SQLite's type affinity works?

Upvotes: 3

Views: 1851

Answers (2)

Stefan
Stefan

Reputation: 1036

I tried this 

CREATE VIEW v2 AS SELECT a, CAST((a || ' ') AS VARCHAR(11)) AS b FROM x;

without success neither.

Isn't the answer documented in the docs. you reference here https://www.sqlite.org/datatype3.html ?

  • 3.2 Affinity Of Expressions: "... Otherwise, an expression has no affinity."
  • 3.3 Column Affinity For Views And Subqueries: ... "expressions always have no affinity".

In fact, your question is probably about what's in the table/view info and not about type affinity (nor type constraints). Seems to me that this is a pure implementation decision in SQLite3 to not set the view info for anything else than predefined attributes in CREATE VIEW.

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 220842

A workaround when using a client like JDBC (e.g. via Xerial) is to fetch one row from the view and check the ResultSetMetaData on it:

try (Connection c = DriverManager.getConnection("jdbc:sqlite::memory:");
    Statement s = c.createStatement()) {

    s.execute("CREATE TABLE x (a VARCHAR(10));");
    s.execute("INSERT INTO x VALUES ('a')");
    s.execute("CREATE VIEW v AS SELECT a, a || ' ' AS b FROM x;");

    try (ResultSet rs = s.executeQuery("SELECT * FROM v LIMIT 1")) {
        ResultSetMetaData meta = rs.getMetaData();

        for (int i = 0; i < meta.getColumnCount(); i++) {
            String name = meta.getColumnName(i + 1);
            String type = meta.getColumnTypeName(i + 1);
            int precision = meta.getPrecision(i + 1);
            System.out.println(name + " " 
                             + type 
                             + (precision > 0 ? " (" + precision + ")" : ""));
        }
    }
}

This yields:

a VARCHAR (10)
b TEXT

However, this doesn't work when the view doesn't produce any rows, in case of which the column type is again unknown:

a VARCHAR (10)
b NULL

Upvotes: 0

Related Questions