SQL: WYBIERZ Wszystkie kolumny oprócz niektórych

Czy istnieje sposób na SELECTwszystkie kolumny w tabeli oprócz określonych? Byłoby bardzo wygodne, aby wybrać wszystkie nieblobowe lub nie geometryczne kolumny z tabeli.

Coś jak:

SELECT * -the_geom FROM segments;

• Kiedyś słyszałem, że ta funkcja została celowo wyłączona ze standardu SQL, ponieważ zmiana dodawania kolumn do tabeli zmieni wyniki zapytania. Czy to prawda? Czy argument jest prawidłowy?
• Czy istnieje obejście, szczególnie w PostgreSQL?

Taka funkcja nie istnieje ani w Postgres, ani w standardzie SQL (AFAIK). Myślę, że to dość interesujące pytanie, więc trochę googłem i natknąłem się na ciekawy artykuł na postgresonline.com .

Pokazują podejście, które wybiera kolumny bezpośrednio ze schematu:

SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
        FROM information_schema.columns As c
            WHERE table_name = 'officepark' 
            AND  c.column_name NOT IN('officeparkid', 'contractor')
    ), ',') || ' FROM officepark As o' As sqlstmt

Możesz stworzyć funkcję, która robi coś takiego. Takie tematy były również omawiane na listach mailingowych, ale ogólny konsensus był prawie taki sam: zapytanie o schemat.

Jestem pewien, że istnieją inne rozwiązania, ale myślę, że wszystkie one będą wymagały jakiegoś magicznego schematu - kwerendowania foo.

BTW: Bądź ostrożny, SELECT * ...ponieważ może to skutkować karami za wydajność

Prawdziwa odpowiedź jest taka, że ​​po prostu nie możesz. Jest to wymagana funkcja od dziesięcioleci, a programiści odmawiają jej wdrożenia.

Popularna odpowiedź sugerująca zapytania do tabel schematów nie będzie w stanie działać efektywnie, ponieważ optymalizator Postgres uważa funkcje dynamiczne za czarne skrzynki (patrz przypadek testowy poniżej). Oznacza to, że indeksy nie będą używane, a łączenia nie będą wykonywane inteligentnie. Lepiej byłoby z jakimś systemem makr, takim jak m4. Przynajmniej nie dezorientuje optymalizatora (ale nadal może mylić.) Bez samodzielnego pisania kodu i pisania funkcji lub używania interfejsu języka programowania, który utknął.

Poniżej napisałem prosty dowód koncepcji pokazujący, jak słaba byłaby wydajność przy bardzo prostej dynamicznej realizacji w plpgsql. Zauważ też, że poniżej muszę wymusić funkcję zwracającą ogólny rekord do określonego typu wiersza i wyliczyć kolumny. Tak więc ta metoda nie będzie działać dla „zaznacz wszystko oprócz”, chyba że chcesz przerobić tę funkcję dla wszystkich swoich tabel.

test=# create table atest (i int primary key);
CREATE TABLE
test=# insert into atest select generate_series(1,100000);
INSERT 0 100000

test=# create function get_table_column(name text) returns setof record as
$$
    declare r record;
    begin
    for r in execute 'select  * from ' || $1 loop
    return next r;
    end loop;
    return; 
    end; 
$$ language plpgsql; 

test=# explain analyze select i from atest where i=999999;
                                                      QUERY PLAN                                    
----------------------------------------------------------------------------------------------------
-------------------
 Index Only Scan using atest_pkey on atest  (cost=0.29..8.31 rows=1 width=4) (actual time=0.024..0.0
24 rows=0 loops=1)
   Index Cond: (i = 999999)
   Heap Fetches: 0
 Planning time: 0.130 ms
 Execution time: 0.067 ms
(5 rows)

test=# explain analyze
    select * from get_table_column('atest') as arowtype(i int) where i = 999999;
                                                        QUERY PLAN                                  
----------------------------------------------------------------------------------------------------
-----------------------
 Function Scan on get_table_column arowtype  (cost=0.25..12.75 rows=5 width=4) (actual time=92.636..
92.636 rows=0 loops=1)
   Filter: (i = 999999)
   Rows Removed by Filter: 100000
 Planning time: 0.080 ms
 Execution time: 95.460 ms
(5 rows)

Jak widać, wywołanie funkcji skanowało całą tabelę, podczas gdy zapytanie bezpośrednie korzystało z indeksu ( 95,46 ms vs. 00,07 ms ). Tego rodzaju funkcje byłyby źródłem wszelkiego rodzaju skomplikowanych zapytań, które wymagałyby użycia indeksów lub łączenia tabel we właściwej kolejności .

Jest to całkiem możliwe z PostgreSQL od wersji 9.4, w której wprowadzono JSONB. Zastanawiałem się nad podobnym pytaniem, jak pokazać wszystkie dostępne atrybuty w Mapie Google (przez GeoJSON).

johto na kanale irc sugeruje, aby spróbować usunąć element z JSONB.

Oto pomysł

select the_geom,
  row_to_json(foo)::jsonb - 'the_geom'::text attributes
from (
  select * from
  segments
) foo

Podczas gdy zamiast pojedynczych kolumn dostajesz json, właśnie tego chciałem. Być może json może zostać rozwinięty z powrotem do pojedynczych kolumn.

Jedynym sposobem, w jaki możesz (nie mów, że powinieneś) to zrobić, jest użycie dynamicznych instrukcji SQL. Łatwo (jak napisał DrColossos) przeszukanie widoków systemu, znalezienie struktury tabeli i zbudowanie odpowiednich instrukcji.

PS: Dlaczego chcesz wybrać wszystkie / niektóre kolumny bez znajomości / pisania dokładnie struktury tabeli?

Dynamicznie, jak wspomniano powyżej, jest jedyną odpowiedzią, ale jej nie polecę. Co się stanie, jeśli dodasz więcej kolumn na dłuższą metę, ale niekoniecznie są one wymagane dla tego zapytania?

Zaczniesz wyciągać więcej kolumn niż potrzebujesz.

Co jeśli zaznaczenie jest częścią wstawki jak w

Wstaw do tabeli A (col1, col2, col3 .. coln) Zaznacz wszystko oprócz 2 kolumn Z tabeliB

Dopasowanie kolumny będzie nieprawidłowe, a wstawianie nie powiedzie się.

Jest to możliwe, ale nadal zalecam pisanie każdej potrzebnej kolumny dla każdej zapisanej selekcji, nawet jeśli prawie każda kolumna jest wymagana.

Jeśli Twoim celem jest usunięcie bałaganu z ekranu podczas debugowania, nie wyświetlając kolumn z dużymi wartościami danych, możesz zastosować następującą sztuczkę:

(zainstaluj pakiet contrib „hstore”, jeśli jeszcze go nie masz: „ CREATE EXTENSION hstore;”)

Dla tabeli „test” z col1, col2, col3, możesz ustawić wartość „col2” na null przed wyświetleniem:

select (r).* from (select (test #= hstore('col2',null)) as r from test) s;

Lub ustaw dwie kolumny na null przed wyświetleniem:

select (r).* from (select (test #= hstore('col2',null) #= hstore('col1',null)) as r from test) s;

zastrzeżeniem jest to, że „test” musi być tabelą (alias lub podselekcja nie będzie działać), ponieważ typ rekordu podawanego do hstore musi być zdefiniowany.

Jest właśnie obejście, które właśnie odkryłem, ale wymaga wysyłania zapytań SQL z poziomu R. Może to być przydatne dla użytkowników R.

Zasadniczo dplyrpakiet wysyła zapytania SQL (a zwłaszcza PostgreSQL) i przyjmuje -(column_name)argument.

Twój przykład można zapisać następująco:

select(segments, -(the_geom))

W komentarzu wyjaśnisz, że Twoim motywem jest wygoda, aby nie wyświetlać zawartości kolumn z długą zawartością, a nie wyświetlać samej kolumny:

… Czasami chcę wysłać zapytanie do tabeli z kolumną geometryczną, bez wyświetlania bardzo długiego ciągu geometrii, który zakłóca wynik. Nie chcę określać wszystkich kolumn, ponieważ może być ich kilkadziesiąt.

Jest to możliwe przy pomocy funkcji pomocnika, która zastępuje długą treść null(dowolną textkolumną w moim przykładzie, ale zmodyfikowałbyś ją dla typów, które chcesz ukryć):

create table my_table(foo integer, bar integer, baz text);

insert into my_table(foo,bar,baz) values (1,2,'blah blah blah blah blah blah'),(3,4,'blah blah');

select * from my_table;

foo | bar | baz                          
-: | -: | : ----------------------------
  1 | 2 | bla bla bla bla bla bla
  3 | 4 | bla bla                    

create function f(ttype anyelement) returns setof anyelement as
$$
declare
  toid oid;
  tname text;
  nname text;
  cols text;
begin
  --
  select pg_type.oid, pg_namespace.nspname, pg_type.typname
  into toid, nname, tname
  from pg_type join pg_namespace on pg_namespace.oid=pg_type.typnamespace
  where pg_type.oid=pg_typeof(ttype);
  --
  select string_agg((case when data_type<>'text' 
                          then column_name 
                          else 'null::'||data_type||' "'||column_name||'"' end)
                   ,', ' order by ordinal_position)
  into cols
  from information_schema.columns 
  where table_schema=nname and table_name=tname;
  --
  return query execute 'select '||cols||' from '||nname||'.'||tname;
  --
end
$$ language plpgsql;

select * from f(null::my_table);

foo | bar | baz
-: | -: | : ---
  1 | 2 | null 
  3 | 4 | zero

dbfiddle tutaj

• Z perspektywy aplikacji jest to leniwe rozwiązanie. Jest mało prawdopodobne, aby aplikacja automatycznie wiedziała, co zrobić z nowymi kolumnami.

  Aplikacje przeglądarki danych mogą wysyłać zapytania do metadanych o dane i wykluczać kolumny z uruchomionych zapytań lub wybierać podzbiór danych kolumny. Nowe obiekty BLOB można wykluczyć po dodaniu. Dane BLOB dla poszczególnych wierszy można wybrać na żądanie.

• W każdym wariancie SQL, który obsługuje zapytania dynamiczne, zapytanie można zbudować przy użyciu zapytania na metadanych tabel. Dla waszej intencji wykluczę kolumny oparte na typie, a nie nazwie.

Nigdy nie widzisz *w WIDOKACH SQL ... sprawdź \d any_viewu siebie psql. Istnieje (introspektywne) przetwarzanie wstępne dla wewnętrznej reprezentacji.

Wszystko wskazuje, że dyskusja tutaj propozycja problem (implicite w pytaniu i dyskusji) to cukier składni dla programistów, a nie prawdziwe „Optymalizacja SQL problem” ... Cóż, zgaduję, że jest 80% programistów.

Można go więc wdrożyć jako „ wstępną analizę z introspekcją” ... Zobacz, co robi PostgreSQL, gdy deklarujesz SQL-VIEW za pomocą SELECT *: konstruktor VIEW przekształca *się w listę wszystkich kolumn (przez introspekcję i w momencie uruchomienia UTWÓRZ WIDOK kod źródłowy).

Wdrożenie dla CREATE VIEW i PREPARE

To realne wdrożenie. Załóżmy, że tabela tz polami (id serial, name text, the_geom geom).

CREATE VIEW t_full AS SELECT * FROM t;
-- is transformed into SELECT id,name,the_geom FROM t;

CREATE VIEW t_exp_geom AS SELECT * -the_geom FROM t;
-- or other syntax as EXCEPT the_geom
-- Will be transformed into SELECT id,name FROM t;

To samo dotyczy instrukcji PREPARE .

... więc jest to możliwe i właśnie tego potrzebuje 80% programistów, cukru składniowego dla PREPARE i WIDOKÓW!

UWAGA: oczywiście opłacalna składnia może nie jest - column_name, jeśli istnieje pewien konflikt w PostgreSQL, więc możemy zasugerować EXCEPT column_name,
EXCEPT (column_name1, column_name2, ..., column_nameN)lub inny.

To jest moja funkcja, aby wybrać wszystkie oczekiwane kolumny. Połączyłem pomysły z postgresonline.com i postgresql tuturial i z innych źródeł.

CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32),
lastname VARCHAR(32), address VARCHAR(64));
INSERT INTO phonebook(phone, firstname, lastname, address) 
VALUES ('+1 123 456 7890', 'John', 'Doe', 'North America'), 
('+1 321 456 7890', 'Matti', 'Meikeläinen', 'Finland'), 
('+1 999 456 7890', 'Maija', 'Meikeläinen', 'Finland'), 
('+9 123 456 7890', 'John', 'Doe', 'Canada'), 
('+1 123 456 7890', 'John', 'Doe', 'Sweden'), 
('+1 123 456 7890', 'John', 'Doe2', 'North America');

drop function all_except_one(text,text);
CREATE OR REPLACE FUNCTION all_except_one(to_remove TEXT, table_name1 TEXT) 
RETURNS void AS $$

 DECLARE 
 rec_row RECORD;
 curs1 refcursor ;

 BEGIN
  --print column names:
  raise notice '%', ('|'|| ARRAY_TO_STRING(ARRAY(SELECT 
  COLUMN_NAME::CHAR(20) FROM INFORMATION_SCHEMA.COLUMNS WHERE
  TABLE_NAME=table_name1 AND COLUMN_NAME NOT IN (to_remove) ), 
  '|') ||'|') ; 

  OPEN curs1 FOR
  EXECUTE 'select table_1  from (SELECT ' || ARRAY_TO_STRING(ARRAY(
  SELECT COLUMN_NAME::VARCHAR(50) FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME=table_name1 AND COLUMN_NAME NOT IN (to_remove)    
  ), ', ') || ' FROM ' || table_name1 || ' limit 30)   table_1 ';

  LOOP
  -- fetch row into the rec_row
  FETCH curs1 INTO rec_row;

  -- exit when no more row to fetch
  EXIT WHEN NOT FOUND;

  -- build and print the row output

  raise notice '%',(select'| '|| regexp_replace( array_to_string(
  array_agg(a::char(20)),'|'),'["\(.*\)]+',   '','g') ||'|'  from 
  unnest(string_to_array(replace(replace(replace(trim(rec_row::text,
  '()'),'"',''), ', ','|'),')',' '),',')) as a);

  END LOOP;

  -- Close the cursor

  CLOSE curs1;

  END; $$ LANGUAGE plpgsql;

select  all_except_one('phone','phonebook');

--output:
--NOTICE:  |firstname           |lastname            |address             |
--NOTICE:  | John               |Doe                 |North America       |
--NOTICE:  | Matti              |Meikeläinen         |Finland             |
--NOTICE:  | Maija              |Meikeläinen         |Finland             |
--NOTICE:  | John               |Doe                 |Canada              |
--NOTICE:  | John               |Doe                 |Sweden              |
--NOTICE:  | John               |Doe2                |North America       |
-- all_except_one 
-- ----------------
-- (1 row)