How to Select All Columns Except Some

Learn how to select all column names in a CARTO dataset except some of them.

Overview

When working with tables containing many fields, you may need to exclude specific columns from your selection. Rather than manually listing each desired column, this SQL query generates the selection statement automatically.

Solution

The following query constructs a SELECT statement dynamically, excluding specified fields:

SELECT
    'SELECT '
    || array_to_string(
        ARRAY(
            SELECT
                'o' || '.' || c.column_name
            FROM
                information_schema.columns As c
            WHERE
                table_name = 'my_dataset' AND
                c.column_name NOT IN('field_1', 'field_2')
            ),
        ','
        )
    || ' FROM my_dataset As o' As sqlstmt

This approach queries the database’s information schema to identify all columns, filters out unwanted ones, and generates the complete SELECT statement as a string. You can then copy and paste the resulting output to use in your actual query.