Geek asks and answers

Sqlite: SELECT * BUT id FROM Table

I have many columns in a table, and I need SELECT * FROM Tableto save one column (for example: location) without having to list all the columns that I want to use.

SELECT * EXCEPT id FROM Table???

+7
source share
4 answers

Absolutely not.

But here is a workaround. Create a table VIEWtable for example

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

after creation VIEW, you can now call it,

SELECT * FROM ViewName
+8
source

No, you cannot do this.

You indicate the ones you need, or you agree that the result set contains another column than you need.

+5
source

sqlite - , , . , , .

If someone thinks outside of SQL, the answer is yes: use the main language to iterate through the columns of the table and build the select statement to get the desired schema. See How to get a list of columns in a table for a SQLite database?

+1
source

A rough path, but when necessary for some reason:

A two-step solution in which we first create a 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 run the result to create the view.

It should give something like:

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

One line for easy copying:

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";

0
source

More articles:


All Articles