How to select all columns in a table except some of them

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

Let’s say you want to select all the columns in a hundred-fields table, but if you also select cartodb_id, you may face a conflict later. Would you build the select query by hand? This query will build the query for you, and returns it as a string

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

…so you need to copy&paste the resulting string and use it as needed.