23

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???

John Woo
  • 258,903
  • 69
  • 498
  • 492
Brandon Nadeau
  • 3,568
  • 13
  • 42
  • 65
  • Does this answer your question? [Exclude a column using SELECT \* \[except columnA\] FROM tableA?](https://stackoverflow.com/questions/729197/exclude-a-column-using-select-except-columna-from-tablea) – Jason C Jan 30 '22 at 01:09
  • 1
    Looks like it does but I asked this question almost 10 years ago now lol. But that's a helpful post. – Brandon Nadeau Jan 31 '22 at 16:20
  • Yeah it's more for SEO. Also the "does this answer your question" comment gets automatically posted when I mark a duplicate. – Jason C Jan 31 '22 at 17:18

5 Answers5

17

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
John Woo
  • 258,903
  • 69
  • 498
  • 492
6

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.

Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
5

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

user3342816
  • 974
  • 10
  • 24
4

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?

C Perkins
  • 3,733
  • 4
  • 23
  • 37
0

Another approach: exporting to tsv and using cut -f 2-:

sqlite3 -header my.db 'select * from mytable' | tr '|' '\t' | cut -f 2- > all_but_id.tsv
user553965
  • 1,199
  • 14
  • 15