Rozmawialiśmy o tym wiele razy. Schemat informacyjny służy określonym celom. Jeśli znasz się na katalogach systemowych, lepiej służą one większości celów , IMO. Katalogi systemowe są faktycznym źródłem wszystkich informacji.
Schematu informacji dostarcza znormalizowane widoki, które pomagają z przenośności, głównie po drugiej wersji główne PostgreSQL, ponieważ przenośność między różnymi platformami RDBMS zazwyczaj jest iluzją, gdy Twoje zapytania są wyrafinowane wystarczy potrzeby patrzenia w górę katalogów systemowych. Co ważne, Oracle nadal nie obsługuje schematu informacji.
Widoki w schemacie informacyjnym muszą przeskakiwać przez wiele obręczy, aby osiągnąć format zgodny ze standardem. Powoduje to, że są powolne, a czasem bardzo wolne. Porównaj plany i wydajność dla tych podstawowych obiektów:
EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;
Różnica jest niezwykła. To naprawdę zależy od tego, czego szukasz.
Twój przykład
Na przykład SELECT * from tbl
porównaj dwa poniższe zapytania do tej prostej tabeli:
CREATE TEMP TABLE foo(
A numeric(12,3)
, b timestamp(0)
);
Używanie pg_attribute
:
SELECT attname, format_type(atttypid, atttypmod) AS type
FROM pg_attribute
WHERE attrelid = 'foo'::regclass
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum;
format_type()
zwraca pełny typ ze wszystkimi modyfikatorami:
attname | type
--------+-------------------------------
a | numeric(12,3)
b | timestamp(0) without time zone
Zauważ też, że rzutowanie regclass
rozwiązuje nazwę tabeli nieco inteligentnie zgodnie z bieżącym search_path
. Zgłasza także wyjątek, jeśli nazwa jest niepoprawna. Detale:
Używanie information_schema.columns
:
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'foo'
ORDER BY ordinal_position;
Informacje są znormalizowane, ale niekompletne :
column_name | data_type
------------+----------------------------
a | numeric
b | timestamp without time zone
Aby uzyskać pełne informacje o typie danych, należy dodatkowo rozważyć wszystkie te kolumny:
character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision
Powiązane odpowiedzi:
Lista zalet i wad , największe zalety (IMO) pogrubione:
Widoki schematu informacyjnego
- często prostsze (zależy)
- powolny
- wstępnie przetworzone, które mogą, ale nie muszą odpowiadać Twoim potrzebom
- selektywne (użytkownicy widzą tylko obiekty, do których mają uprawnienia)
- zgodny ze standardem SQL (który jest implementowany przez niektóre z głównych RDBMS)
- głównie przenośne w głównych wersjach Postgres
- nie wymagają dużej wiedzy na temat Postgres
- identyfikatory są opisowe, długie i czasem niezręczne
Katalogi systemowe
- często bardziej złożone (zależy), bliżej źródła
- szybki
- pełna (kolumny systemowe jak
oid
zestawie)
- niezgodne ze standardem SQL
- mniej przenośny w głównych wersjach Postgres (ale podstawy się nie zmienią)
- wymagają bardziej szczegółowej wiedzy na temat Postgres
- identyfikatory są zwięzłe, mniej opisowe, ale wygodnie krótkie
Dowolne zapytanie
Aby uzyskać tę samą listę nazw i typów kolumn z zapytania, możesz użyć prostej sztuczki: UTWÓRZ tabelę tymczasową z wyniku zapytania, a następnie użyj tych samych technik, jak powyżej.
Możesz dołączyć LIMIT 0
, ponieważ nie potrzebujesz rzeczywistych danych:
CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT 0;
Aby uzyskać typ danych poszczególnych kolumn, możesz również użyć funkcji pg_typeof()
:
SELECT pg_typeof(1);