PostgreSQL: Różnica między tekstem a varcharem (różne znaki)

Jaka jest różnica między text typem danych a character varying( varchar) typami danych?

Według z dokumentacją

Jeśli zmienna znakowa jest używana bez specyfikatora długości, typ akceptuje łańcuchy o dowolnym rozmiarze. To drugie jest rozszerzeniem PostgreSQL.

i

Ponadto PostgreSQL zapewnia typ tekstu, który przechowuje ciągi dowolnej długości. Chociaż tekst typu nie jest w standardzie SQL, ma go również kilka innych systemów zarządzania bazami danych SQL.

Jaka jest różnica?

Nie ma różnicy, pod maską jest wszystko varlena( tablica o zmiennej długości ).

Sprawdź ten artykuł z Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Kilka najważniejszych:

Podsumowując:

• char (n) - zajmuje zbyt dużo miejsca w przypadku wartości krótszych niż n( nuzupełnia je ) i może prowadzić do subtelnych błędów z powodu dodania spacji końcowych, a także zmiana granicy jest problematyczna
• varchar (n) - problematyczne jest zmienianie limitu w środowisku na żywo (wymaga wyłącznej blokady podczas zmiany tabeli)
• varchar - tak jak tekst
• tekst - dla mnie zwycięzca - ponad (n) typami danych, ponieważ brakuje im ich problemów, a ponad varchar - ponieważ ma odrębną nazwę

W artykule przeprowadzono szczegółowe testy, aby wykazać, że wydajność wstawiania i selekcji dla wszystkich 4 typów danych jest podobna. Szczegółowo przygląda się również alternatywnym sposobom ograniczania długości w razie potrzeby. Wiązania lub domeny oparte na funkcjach mają tę zaletę, że natychmiastowe zwiększenie ograniczenia długości, a na podstawie tego, że zmniejszenie ograniczenia długości łańcucha jest rzadkie, depesz stwierdza, że ​​jeden z nich jest zwykle najlepszym wyborem dla ograniczenia długości.

Jako „ zestaw znaków ” w punktach dokumentacja określająca, varchar(n), char(n), i textsą przechowywane w ten sam sposób. Jedyna różnica polega na tym, że potrzebne są dodatkowe cykle, aby sprawdzić długość, jeśli jest podana, oraz dodatkowa przestrzeń i czas wymagany, jeśli konieczne jest wypełnienie char(n).

Jednak gdy potrzebujesz zapisać tylko jeden znak, użycie specjalnego typu ma niewielką przewagę wydajności "char"(zachowaj podwójne cudzysłowy - są one częścią nazwy typu). Uzyskujesz szybszy dostęp do pola i nie ma narzutu do przechowywania długości.

Właśnie stworzyłem tabelę z losową liczbą 1 000 000 "char"wybraną z małego alfabetu. Zapytanie w celu uzyskania rozkładu częstotliwości ( select count(*), field ... group by field) zajmuje około 650 milisekund, w porównaniu z około 760 dla tych samych danych przy użyciu textpola.

AKTUALIZACJA PORÓWNAWCZYCH NA 2016 R. (str.5.5 +)

I przy użyciu testów porównawczych „czystego SQL” (bez zewnętrznego skryptu)

1. użyj dowolnego string_generatora z UTF8

2. główne punkty odniesienia:

   2.1 WSTAWIĆ

   2.2 WYBIERZ porównywanie i liczenie

CREATE FUNCTION string_generator(int DEFAULT 20,int DEFAULT 10) RETURNS text AS $f$
  SELECT array_to_string( array_agg(
    substring(md5(random()::text),1,$1)||chr( 9824 + (random()*10)::int )
  ), ' ' ) as s
  FROM generate_series(1, $2) i(x);
$f$ LANGUAGE SQL IMMUTABLE;

Przygotuj konkretny test (przykłady)

DROP TABLE IF EXISTS test;
-- CREATE TABLE test ( f varchar(500));
-- CREATE TABLE test ( f text); 
CREATE TABLE test ( f text  CHECK(char_length(f)<=500) );

Wykonaj podstawowy test:

INSERT INTO test  
   SELECT string_generator(20+(random()*(i%11))::int)
   FROM generate_series(1, 99000) t(i);

I inne testy,

CREATE INDEX q on test (f);

SELECT count(*) FROM (
  SELECT substring(f,1,1) || f FROM test WHERE f<'a0' ORDER BY 1 LIMIT 80000
) t;

... i użyj EXPLAIN ANALYZE.

AKTUALIZACJA PONOWNIE 2018 (str.10)

mała edycja, aby dodać wyniki z 2018 roku i wzmocnić rekomendacje.

Wyniki w 2016 i 2018 r

Moje wyniki, po uśrednieniu, na wielu maszynach i wielu testach: wszystkie takie same
(statystycznie mniejsze odchylenie standardowe).

Rekomendacje

• Użyj texttypu danych,
  unikaj starych, varchar(x)ponieważ czasami nie jest to standard, np. W CREATE FUNCTIONklauzulach varchar(x)≠varchar(y) .

• wyraźne limity (z tą samą varcharwydajnością!) za pomocą CHECKklauzuli CREATE TABLE
  np CHECK(char_length(x)<=10).
  Przy nieznacznej utracie wydajności w INSERT / UPDATE można także kontrolować zakresy i strukturę łańcuchów,
  npCHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')

Na instrukcji PostgreSQL

Nie ma różnicy w wydajności między tymi trzema typami, oprócz zwiększonego miejsca do przechowywania przy użyciu pustego wypełnienia i kilku dodatkowych cykli procesora, aby sprawdzić długość podczas przechowywania w kolumnie o ograniczonej długości. Chociaż znak (n) ma przewagę wydajności w niektórych innych systemach baz danych, nie ma takiej przewagi w PostgreSQL; w rzeczywistości znak (n) jest zwykle najwolniejszy z trzech ze względu na dodatkowe koszty przechowywania. W większości sytuacji zamiast tego należy używać tekstu lub różnych znaków.

Zwykle używam tekstu

Referencje: http://www.postgresql.org/docs/current/static/datatype-character.html

Moim zdaniem varchar(n)ma swoje zalety. Tak, wszystkie używają tego samego typu podstawowego i tak dalej. Należy jednak zauważyć, że indeksy w PostgreSQL mają limit wielkości 2712 bajtów na wiersz.

TL; DR: Jeśli używasz texttyp bez ograniczeń i mają indeksy na te kolumny, to jest bardzo prawdopodobne, że trafisz ten limit dla niektórych swoich kolumn i dostać błąd podczas próby wstawienia danych, ale z użyciemvarchar(n) , można temu zapobiec.

Kilka dodatkowych szczegółów: Problem polega na tym, że PostgreSQL nie podaje żadnych wyjątków podczas tworzenia indeksów dla texttypu lub varchar(n)gdzie njest większy niż 2712. Jednak da błąd, gdy spróbuje się wstawić rekord o skompresowanym rozmiarze większym niż 2712. Oznacza to, że możesz łatwo wstawić 100 000 znaków ciągu złożonego z powtarzalnych znaków, ponieważ będzie on skompresowany znacznie poniżej 2712 znaków, ale możesz nie być w stanie wstawić łańcucha zawierającego 4000 znaków, ponieważ rozmiar skompresowanego pliku jest większy niż 2712 bajtów. Używając varchar(n)gdzie nnie jest dużo większy niż 2712, jesteś bezpieczny przed tymi błędami.

tekst i varchar mają różne niejawne konwersje typów. Największy wpływ, jaki zauważyłem, to obsługa spacji końcowych. Na przykład ...

select ' '::char = ' '::varchar, ' '::char = ' '::text, ' '::varchar = ' '::text

zwraca, true, false, truea nie true, true, truejak można się spodziewać.

Nieco OT: jeśli używasz Railsów, standardowe formatowanie stron internetowych może być inne. W przypadku formularzy do wprowadzania danych textpola można przewijać, ale pola character varying(Szyny string) są jednowierszowe. Pokaż widoki są tak długo, jak to konieczne.

Dobre wyjaśnienie z http://www.sqlines.com/postgresql/datatypes/text :

Jedyną różnicą między TEKSTEM a VARCHAR (n) jest to, że można ograniczyć maksymalną długość kolumny VARCHAR, na przykład VARCHAR (255) nie pozwala na wstawianie ciągu dłuższego niż 255 znaków.

Zarówno TEKST, jak i VARCHAR mają górny limit na 1 Gb i nie ma między nimi żadnej różnicy w wydajności (zgodnie z dokumentacją PostgreSQL).

character varying(n), varchar(n)- (Oba te same). wartość zostanie obcięta do n znaków bez zgłaszania błędu.

character(n), char(n)- (Oba te same). o stałej długości i będzie podkładał puste miejsca do końca długości.

text- Nieograniczona długość.

Przykład:

Table test:
   a character(7)
   b varchar(7)

insert "ok    " to a
insert "ok    " to b

Otrzymujemy wyniki:

a        | (a)char_length | b     | (b)char_length
----------+----------------+-------+----------------
"ok     "| 7              | "ok"  | 2