SQL: SELECT Все столбцы, кроме некоторых

Есть ли способ SELECT всех столбцов в таблице, кроме определенных? Это было бы очень удобно для выбора всех столбцов non-blob или non-geometric из таблицы.

Что-то вроде:

SELECT * -the_geom FROM сегментов;
  • Я когда-то слышал, что эта функциональность была специально исключена из стандарта SQL , поскольку изменение добавления столбцов в таблицу будет изменить результаты запроса. Это правда? Допустим ли аргумент?
  • Есть ли обходной путь, особенно в PostgreSQL?
82 голоса | спросил Adam Matan 29 MarpmTue, 29 Mar 2011 12:19:55 +04002011-03-29T12:19:55+04:0012 2011, 12:19:55

10 ответов


48

Такая функция существует ни в Postgres, ни в SQL Standard (AFAIK). Я думаю, что это довольно интересный вопрос, поэтому я немного погуглил и наткнулся на интересную статью о postgresonline.com .

Они показывают подход, который выбирает столбцы непосредственно из схемы:

SELECT 'SELECT' || array_to_string (ARRAY (SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns Как c
            WHERE table_name = 'officepark'
            И c.column_name NOT IN («officeparkid», «подрядчик»)
    ), ',') || 'From officepark As o' As sqlstmt

Вы можете создать функцию, которая делает что-то подобное. Такие темы также обсуждались в списках рассылки, но общий консенсус был почти таким же: запросите схему.

Я уверен, что есть другие решения, но я думаю, что все они будут включать какую-то магическую схему-queriying-foo.

BTW: будьте осторожны с SELECT * ..., так как это может привести к снижению производительности

ответил DrColossos 29 MarpmTue, 29 Mar 2011 12:36:19 +04002011-03-29T12:36:19+04:0012 2011, 12:36:19
11

Настоящий ответ заключается в том, что вы просто не можете практически. Это была запрошенная функция на протяжении десятилетий, и разработчики отказываются ее реализовывать.

Популярный ответ, предлагающий запросить таблицы схем, не сможет эффективно работать, потому что оптимизатор Postgres рассматривает динамические функции как черный ящик (см. ниже пример теста). Это означает, что индексы не будут использоваться, и объединения не будут выполнены разумно. Вам будет намного лучше с какой-то макросистемой вроде m4. По крайней мере, это не смутит оптимизатор (но он все равно может вас смутить.) Без форматирования кода и записи функции самостоятельно или с помощью интерфейса языка программирования вы застряли.

Я написал простое доказательство концепции ниже, показывая, насколько плохая производительность будет с очень простым динамическим исполнением в plpgsql. Также обратите внимание, что ниже я должен принудить функцию, возвращающую общую запись, в конкретный тип строки и перечислять столбцы. Таким образом, этот метод не будет работать для «select all but», если вы не захотите переделать эту функцию для всех ваших таблиц.

test = # create table atest (первичный ключ i int);
СОЗДАТЬ ТАБЛИЦУ
test = # insert in atest select generate_series (1,100000);
INSERT 0 100000

test = # create function get_table_column (name text) возвращает setof record как
$$
    объявить запись r;
    начать
    для r в execute 'select * from' || Цикл $ 1
    return next r;
    конец цикла;
    вернуть;
    конец;
$$ language plpgsql;

test = # объяснить анализ выберите i из atest, где i = 999999;
                                                      ПЛАН QUERY
-------------------------------------------------- --------------------------------------------------
-------------------
 Только индексное сканирование с использованием atest_pkey at atest (стоимость = 0.29..8.31 rows = 1 width = 4) (фактическое время = 0.024..0.0
24 строки = 0 циклов = 1)
   Индексный код: (i = 999999)
   Куча: 0
 Время планирования: 0.130 мс
 Время выполнения: 0,067 мс
(5 строк)

test = # объяснить анализ
    выберите * from get_table_column ('atest') как arowtype (i int), где i = 999999;
                                                        ПЛАН QUERY
-------------------------------------------------- --------------------------------------------------
-----------------------
 Функция Scan on get_table_column arowtype (cost = 0.25..12.75 rows = 5 width = 4) (фактическое время = 92.636 ..
92,636 строк = 0 циклов = 1)
   Фильтр: (i = 999999)
   Строки, удаленные фильтром: 100000
 Время планирования: 0,080 мс
 Время выполнения: 95.460 мс
(5 строк)

Как вы видите, вызов функции сканировал всю таблицу, в то время как прямой запрос использовал индекс ( 95,46 мс против 00,07 мс .) Эти функции позволяли бы запросить любой сложный запрос для использования индексов или объединения таблиц в правильном порядке.

ответил user17130 14 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowSun, 14 Sep 2014 00:05:03 +0400 2014, 00:05:03
8

На самом деле это возможно с PostgreSQL, начиная с 9.4, где был введен JSONB. Я размышлял о подобном вопросе о том, как показать все доступные атрибуты в Google Map (через GeoJSON).

johto на канале irc предложил попробовать удалить элемент из JSONB.

Вот идея

выберите the_geom,
  row_to_json (foo) :: jsonb - 'the_geom' :: текстовые атрибуты
из (
  выберите из
  сегменты
) foo

Пока вы получаете json вместо отдельных столбцов, это именно то, что я хотел. Возможно, json можно развернуть в отдельные столбцы.

ответил mlt 15 22016vEurope/Moscow11bEurope/MoscowTue, 15 Nov 2016 21:37:43 +0300 2016, 21:37:43
6

Единственный способ, которым вы можете (не говорите, что вам следует) сделать это, используя динамические выражения sql. Легко (как писал DrColossos) запрашивать системные представления и находить структуру таблицы и строить правильные утверждения.

PS: Почему вы хотите выбрать все /некоторые столбцы, не зная /не записывая именно вашу структуру таблицы?

ответил Marian 29 MarpmTue, 29 Mar 2011 14:51:18 +04002011-03-29T14:51:18+04:0002 2011, 14:51:18
3

Динамически, как указано выше, является единственным ответом, но я не буду рекомендовать его. Что делать, если вы добавили больше столбцов в долгосрочной перспективе, но они необязательно необходимы для этого запроса?

Вы начнете вытягивать больше столбцов, чем вам нужно.

Что делать, если выбор является частью вставки, как в

Вставить в tableA (col1, col2, col3 .. coln) Выберите все, кроме 2 столбцов FROM tableB

Совпадение столбцов будет неправильным, и ваша вставка не будет выполнена.

Это возможно, но я по-прежнему рекомендую писать каждый необходимый столбец для каждой записи, даже если требуется почти каждый столбец.

ответил Nicolas de Fontenay 29 MarpmTue, 29 Mar 2011 16:53:30 +04002011-03-29T16:53:30+04:0004 2011, 16:53:30
3

Если ваша цель - удалить заглушку с экрана во время отладки, не отображая столбцы с большими значениями данных, вы можете использовать следующий трюк:

(установите пакет «hstore» contrib, если у вас его еще нет: «CREATE EXTENSION hstore;»)

Для таблицы «test» с col1, col2, col3 вы можете установить значение «col2» равным null перед отображением:

select (r). * from (select (test # = hstore ('col2', null)) как r из теста) s;

Или, перед тем, как отобразить, установите два столбца:

select (r). * from (select (test # = hstore ('col2', null) # = hstore ('col1', null)) как r из теста) s;

оговорки заключаются в том, что «тест» должен быть таблицей (псевдоним или подзапрос не будет работать), поскольку должен быть определен тип записи, загружаемый в hstore.

ответил Sean 31 MaramSun, 31 Mar 2013 04:09:54 +04002013-03-31T04:09:54+04:0004 2013, 04:09:54
3

Существует обходное решение, которое я только что обнаружил, но для этого требуется отправить SQL-запросы из R. Это может быть полезно для пользователей R.

В основном пакет dplyr отправляет запросы SQL (и, в частности, PostgreSQL) и принимает аргумент - (column_name).

Итак, ваш пример можно записать следующим образом:

select (сегменты, - (the_geom))
ответил Dario Lacan 12 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowFri, 12 Sep 2014 13:42:44 +0400 2014, 13:42:44
2
  • С точки зрения приложения это ленивое решение. Приложение вряд ли автоматически узнает, что делать с новыми столбцами.

    Приложения браузера данных могут запрашивать метаданные для данных и исключать столбцы из запущенных запросов или выбирать подмножество данных столбца. При добавлении новых BLOB можно исключить. Данные BLOB для определенных строк могут быть выбраны по требованию.

  • В любом варианте SQL, который поддерживает динамические запросы, запрос может быть построен с использованием запроса в метаданных таблиц. Для ваших целей я бы исключил столбцы на основе типа, а не имени.

ответил BillThor 29 MarpmTue, 29 Mar 2011 20:40:41 +04002011-03-29T20:40:41+04:0008 2011, 20:40:41
1

Вы никогда не видите * в SQL-VIEWS ... проверьте \ d any_view на psql. Для внутреннего представления есть (интроспективная) предварительная обработка .


Все обсуждения здесь показывают, что предложение (неявное в вопросе и обсуждениях) является синтаксическим сахаром для программистов, а не реальной «проблемой оптимизации SQL». Ну, думаю, это для 80% программистов.

Итак, можно реализовать как « предварительный парсинг с интроспекцией» ... Посмотрите, что делает PostgreSQL при объявлении SQL-VIEW с помощью SELECT *: VIEW- constructor преобразует * в список всех столбцов (путем самоанализа и в тот момент, когда вы запускаете исходный код CREATE VIEW).

Реализация для CREATE VIEW и PREPARE

Это жизнеспособная реализация. Предположим, что таблица t содержит поля (идентификатор, имя, текст, геометрия_имя).

CREATE VIEW t_full AS SELECT * FROM t;
- преобразуется в SELECT id, name, the_geom FROM t;

CREATE VIEW t_exp_geom AS SELECT * -the_geom FROM t;
- или другой синтаксис как EXCEPT the_geom
- Будет преобразован в SELECT id, name FROM t;

То же самое для инструкции PREPARE .

... так что это возможно, и это то, что нужно 80% программистов, сахара синтаксиса для PREPARE и VIEWS!


ПРИМЕЧАНИЕ: конечно, жизнеспособный синтаксис , возможно, не - column_name, если в PostgreSQL есть некоторый конфликт, поэтому мы можем предложить EXCEPT column_name , EXCEPT (column_name1, column_name2, ..., column_nameN) или другой.

ответил Peter Krauss 1 Jpm1000000pmMon, 01 Jan 2018 17:45:03 +030018 2018, 17:45:03
1

В комментарий вы объясните, что ваш мотив для удобства отображения элементов столбцов с длинным контентом, а не для отображения самого столбца:

  

â € | Иногда я хочу запросить таблицу с геометрическим столбцом, не отображая очень длинную строку геометрии, которая искажает вывод. Я не хочу указывать все столбцы, потому что может быть несколько десятков.

Это возможно с помощью вспомогательной функции, которая заменяет длинное содержимое null (любой текст) в моем примере, но вы должны изменить это для типы, которые вы хотите подавить):

создать таблицу my_table (foo integer, bar integer, baz text);
вставить в my_table (foo, bar, baz) значения (1,2, «бла-бла-бла-бла-бла-бла»), (3,4, «бла-бла»);
выберите * from my_table;
foo | бар | Baz
-: | -: | : ----------------------------
  1 | 2 | бла-бла-бла-бла-бла-бла
  3 | 4 | бла-бла
create function f (ttype anyelement) возвращает setof anyelement as
$$
объявлять
  toid oid;
  tname текст;
  nname текст;
  cols text;
начать
  -
  выберите pg_type.oid, pg_namespace.nspname, pg_type.typname
  в toid, nname, tname
  from pg_type присоединиться к pg_namespace на pg_namespace.oid = pg_type.typnamespace
  где pg_type.oid = pg_typeof (ttype);
  -
  select string_agg ((случай, когда data_type <gt; 'text'
                          then column_name
                          else 'null ::' || data_type || ' "'|| column_name ||'" 'end)
                   , ',' order by ordinal_position)
  в кол
  from information_schema.columns
  где table_schema = nname и table_name = tname;
  -
  return query execute 'select' || cols || ' from '|| nname ||'. '|| tname;
  -
конец
$$ language plpgsql;
select * from f (null :: my_table);
foo | бар | Baz
-: | -: | : ---
  1 | 2 |  NULL 
  3 | 4 |  NULL 

dbfiddle здесь

ответил Jack Douglas 1 Jpm1000000pmMon, 01 Jan 2018 21:00:52 +030018 2018, 21:00:52

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132