Sqlite: SELECT * BUT id FROM Table

Question

I have many columns in a table and wanted to SELECT * FROM Table except for one column (ex: location) without having to list all the columns I want to use.

SELECT * EXCEPT id FROM Table???

Solution 1

Absolutely, no.

But here's a workaround. Create a VIEW of the table, eg

CREATE VIEW ViewName
AS
    SELECT col1, col2, col3, .... -- don't select the column name you want to hide
    FROM tableName;

once the VIEW was created, you can now call it,

SELECT * FROM ViewName

Solution 2

No, you cannot do that.

You list the ones you need, or you accept that the result set contains one more column than you need.

Solution 3

sqlite is an embedded DBMS, and it is expected that some functionality can be implemented with the host language. For instance, stored procedures are excluded since all such advanced logic and flow structures will exist in the host language.

If one thinks outside of SQL, the answer is "yes": Use the host language to iterate through a table's columns and build a select statement to get desired schema. See How can I get the list of a columns in a table for a SQLite database?

Solution 4

A crude way, but when needed for what ever reason:

A two step solution where we first create the query-text to create a view:

SELECT "CREATE TEMP VIEW my_view_1 AS SELECT " ||  (
SELECT 
    group_concat(name, ', ') 
FROM 
    pragma_table_info('my_table') 
WHERE 
    name != 'id') || 
" FROM my_table";

Then execute the result to create the view.

Should give something like:

CREATE TEMP VIEW test1 AS SELECT all, but, id, ... FROM my_table;

One line for easy copy:

SELECT "CREATE TEMP VIEW my_view_1 AS SELECT " || (SELECT group_concat(name, ', ') FROM pragma_table_info('my_table') WHERE name != 'id') || " FROM my_table";

View more solutions