PostgreSQL: Unterschied zwischen Text und Varchar (Zeichenvariation)

Was ist der Unterschied zwischen dem Datentyp ” text ” und den Datentypen ” character varying ( varchar )?

Laut der Dokumentation

Wenn Zeichenvariable ohne Längenangabe verwendet wird, akzeptiert der Typ Zeichenfolgen beliebiger Größe. Letzteres ist eine PostgreSQL-Erweiterung.

und

Darüber hinaus stellt PostgreSQL den Texttyp zur Verfügung, der Strings beliebiger Länge speichert. Obwohl der Typtext nicht im SQL-Standard enthalten ist, gibt es auch andere SQL-databaseverwaltungssysteme.

Was ist der Unterschied?

Es gibt keinen Unterschied, unter der Haube ist alles varlena ( variable Länge Array ).

Überprüfen Sie diesen Artikel von Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Ein paar Highlights:

Um es zusammenzufassen:

  • char (n) – nimmt zu viel Platz ein, wenn es sich um Werte handelt, die kürzer als n (puffert sie auf n ), und kann zu subtilen Fehlern führen, da nachgestellte Leerzeichen hinzugefügt werden. Außerdem ist es problematisch, das Limit zu ändern
  • varchar (n) – es ist problematisch, das Limit in einer Live-Umgebung zu ändern (erfordert exklusive Sperre, während die Tabelle geändert wird)
  • varchar – genau wie Text
  • Text – für mich ein Gewinner – über (n) Datentypen, weil es ihre Probleme und über Varchar fehlt – weil es einen eindeutigen Namen hat

Der Artikel führt detaillierte Tests durch, um zu zeigen, dass die performance von Einfügungen und Auswahl für alle 4 Datentypen ähnlich ist. Es gibt auch einen detaillierten Blick auf alternative Möglichkeiten, die Länge bei Bedarf zu beschränken. functionsbasierte Einschränkungen oder Domänen bieten den Vorteil einer sofortigen Erhöhung der Längenbeschränkung, und auf der Grundlage, dass die Verringerung einer Stringlängenbeschränkung selten ist, schließt depsz, dass einer von ihnen normalerweise die beste Wahl für eine Längenbeschränkung ist.

Wie ” Character Types ” in der Dokumentation zeigt, sind varchar(n) , char(n) und text alle auf die gleiche Weise gespeichert. Der einzige Unterschied besteht darin, dass zusätzliche Zyklen erforderlich sind, um die Länge zu überprüfen, falls eine gegeben ist, sowie den zusätzlichen Platz und die zusätzliche Zeit, die benötigt werden, wenn für char(n) Padding benötigt wird.

Wenn Sie jedoch nur ein einzelnes Zeichen speichern müssen, gibt es einen leichten performancesvorteil bei der Verwendung des speziellen Typs "char" (behalten Sie die doppelten Anführungszeichen bei – sie sind Teil des Typnamens). Sie erhalten einen schnelleren Zugriff auf das Feld und es gibt keinen Overhead zum Speichern der Länge.

Ich habe gerade eine Tabelle mit 1.000.000 zufälligen Buchstaben aus dem Kleinbuchstaben-Alphabet erstellt. Eine Abfrage, um eine Häufigkeitsverteilung ( select count(*), field ... group by field ) dauert etwa 650 Millisekunden, vs etwa 760 für die gleichen Daten mit einem text .

UPDATING BENCHMARKS FÜR 2016 (S.9.5 +)

Und mit “pure SQL” -Benchmarks (ohne externes Skript)

  1. Verwenden Sie einen String_generator mit UTF8

  2. Haupt-Benchmarks:

    2.1. EINFÜGEN

    2.2. SELECT vergleichen und zählen


 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; 

Bereiten Sie einen spezifischen Test vor (Beispiele)

 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) ); 

Führen Sie einen grundlegenden Test durch:

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

Und andere Tests,

 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; 

... Und benutze EXPLAIN ANALYZE .

AKTUALISIERT 2018 (S. 10)

wenig bearbeiten, um 2018 Ergebnisse hinzuzufügen und Empfehlungen zu verstärken.


Ergebnisse in den Jahren 2016 und 2018

Meine Ergebnisse, nach dem Durchschnitt, in vielen Maschinen und vielen Tests: alle gleich
(statistisch weniger als Standardabweichung).

Empfehlung

  • Benutze text Datentyp,
    Vermeiden Sie varchar(x) weil es manchmal kein Standard ist, zB in CREATE FUNCTION varchar(x) Klauseln varchar(x) varchar(y) .

  • ausdrückliche Grenzen (mit derselben varchar performance!) durch mit CHECK Klausel in der CREATE TABLE
    zB CHECK(char_length(x)< =10) .
    Mit einem vernachlässigbaren performancesverlust in INSERT / UPDATE können Sie auch Bereiche und String-Strukturen steuern
    zB CHECK(char_length(x)>5 AND char_length(x)< =20 AND x LIKE 'Hello%')

Im PostgreSQL-Handbuch

Zwischen diesen drei Typen besteht kein performancesunterschied, abgesehen von erhöhtem Speicherplatz bei der Verwendung des Typs mit Leerzeichen aufgefüllt und einigen zusätzlichen CPU-Zyklen zur Überprüfung der Länge beim Speichern in einer Spalte mit eingeschränkter Länge. Während Zeichen (n) in einigen anderen databasesystemen performancesvorteile hat, gibt es in PostgreSQL keinen solchen Vorteil; Tatsächlich ist der Buchstabe (n) normalerweise der langsamste der drei wegen seiner zusätzlichen Speicherkosten. In den meisten Situationen sollte stattdessen Text oder Zeichenvariation verwendet werden.

Ich benutze normalerweise Text

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

text und varchar haben unterschiedliche implizite Typumwandlungen. Die größte Auswirkung, die ich bemerkt habe, ist die Handhabung von Leerzeichen. Beispielsweise …

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

gibt true, false, true und nicht true, true, true wie Sie vielleicht erwarten.

Meiner Meinung nach hat varchar(n) seine eigenen Vorteile. Ja, sie benutzen alle den gleichen zugrundeliegenden Typ und all das. Aber es sollte darauf hingewiesen werden, dass Indizes in PostgreSQL seine Größenbeschränkung von 2712 Bytes pro Zeile hat.

TL; DR: Wenn Sie einen text ohne Einschränkung verwenden und Indizes für diese Spalten haben, ist es sehr wahrscheinlich, dass Sie dieses Limit für einige Ihrer Spalten erreichen und Fehler erhalten, wenn Sie versuchen, Daten einzufügen, aber varchar(n) . Du kannst es verhindern.

Einige weitere Details: Das Problem hier ist, dass PostgreSQL keine Ausnahmen beim Erstellen von Indizes für den text oder varchar(n) wobei n größer als 2712 ist. Allerdings wird es Fehler geben, wenn ein Datensatz mit komprimierter Größe größer als 2712 ist wird versucht eingefügt zu werden. Das bedeutet, dass Sie 100.000 Zeichenketten, die sich aus sich wiederholenden Zeichen zusammensetzen, leicht einfügen können, da sie weit unter 2712 komprimiert werden. Sie können jedoch möglicherweise keine Zeichenkette mit 4000 Zeichen einfügen, da die komprimierte Größe größer als 2712 Byte ist. Wenn Sie varchar(n) wobei n nicht viel größer als 2712 ist, sind Sie vor diesen Fehlern varchar(n) .

Etwas OT: Wenn Sie Rails verwenden, kann die Standardformatierung von Webseiten unterschiedlich sein. Bei Dateneingabeformularen sind Textfelder scrollbar, character varying (Rails- string ) sind jedoch einzeilig. Zeige Ansichten sind so lang wie nötig.

character varying(n) , varchar(n) – (Beide gleich). Der Wert wird auf n Zeichen gekürzt, ohne dass ein Fehler auftritt.

character(n) , char(n) – (Beide gleich). fester Länge und wird mit Leerzeichen bis zum Ende der Länge aufgefüllt.

text – Unbegrenzte Länge.

Beispiel:

 Table test: a character(7) b varchar(7) insert "ok " to a insert "ok " to b 

Wir bekommen die Ergebnisse:

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