wake-up-neo.com

Postgresql - Ändert die Größe einer varchar-Spalte

Ich habe eine Frage zum Befehl ALTER TABLE In einer sehr großen Tabelle (fast 30 Millionen Zeilen). Eine seiner Spalten ist eine varchar(255) und ich möchte die Größe in eine varchar(40) ändern. Grundsätzlich möchte ich meine Spalte mit dem folgenden Befehl ändern:

ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(40);

Ich habe kein Problem, wenn der Prozess sehr lang ist, aber es scheint, dass meine Tabelle während des Befehls ALTER TABLE nicht mehr lesbar ist. Gibt es einen intelligenteren Weg? Vielleicht eine neue Spalte hinzufügen, Werte aus der alten Spalte kopieren, die alte Spalte löschen und schließlich die neue umbenennen?

Jeder Hinweis wird sehr geschätzt! Danke im Voraus,

Hinweis: Ich verwende PostgreSQL 9.0.

130
Labynocle

Eine Beschreibung dazu finden Sie unter Ändern der Spaltengröße in einer PostgreSQL-Tabelle, ohne Daten zu ändern . Sie müssen die Daten des Datenbankkatalogs hacken. Die einzige Möglichkeit, dies offiziell zu tun, ist ALTER TABLE. Wie Sie bereits bemerkt haben, wird durch die Änderung die gesamte Tabelle gesperrt und neu geschrieben, während sie ausgeführt wird.

Stellen Sie sicher, dass Sie den Abschnitt Character Types in den Dokumenten gelesen haben, bevor Sie dies ändern. Alle möglichen seltsamen Fälle, die Sie hier beachten sollten. Die Längenprüfung wird durchgeführt, wenn Werte in den Zeilen gespeichert werden. Wenn Sie dort eine Untergrenze hacken, wird dies die Größe der vorhandenen Werte überhaupt nicht reduzieren. Es ist ratsam, die gesamte Tabelle nach Zeilen zu durchsuchen, deren Feldlänge nach der Änderung> 40 Zeichen beträgt. Sie müssen herausfinden, wie Sie diese manuell kürzen können - damit Sie einige Sperren nur für übergroße zurückbekommen -, denn wenn jemand versucht, etwas in dieser Zeile zu aktualisieren, wird es derzeit als zu groß zurückgewiesen Es wird die neue Version der Zeile gespeichert. Heiterkeit entsteht für den Benutzer.

VARCHAR ist ein schrecklicher Typ, der in PostgreSQL nur existiert, um den damit verbundenen schrecklichen Teil des SQL-Standards zu erfüllen. Wenn Sie sich nicht für die Kompatibilität mit mehreren Datenbanken interessieren, sollten Sie Ihre Daten als TEXT speichern und eine Einschränkung hinzufügen, um deren Länge zu begrenzen. Einschränkungen, die Sie ohne dieses Problem mit der Tabellensperre und dem erneuten Schreiben ändern können, können mehr Integritätsprüfungen als nur die Prüfung auf schwache Länge durchführen.

65
Greg Smith

In PostgreSQL 9.1 gibt es einen einfacheren Weg

http://www.postgresql.org/message-id/[email protected]

CREATE TABLE foog(a varchar(10));

ALTER TABLE foog ALTER COLUMN a TYPE varchar(30);

postgres=# \d foog

 Table "public.foog"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 a      | character varying(30) |
77
sir_leslie

Ok, ich bin wahrscheinlich zu spät zur Party, ABER ...

ES GIBT KEINE NOTWENDIGKEIT, DIE SPALTE IN IHREM FALL ZU VERGRÖSSERN!

Postgres ist im Gegensatz zu einigen anderen Datenbanken intelligent genug, um nur genügend Platz für die Zeichenfolge zu verwenden (selbst bei Verwendung der Komprimierung für längere Zeichenfolgen). Dies gilt auch dann, wenn Ihre Spalte als VARCHAR (255) deklariert ist - wenn Sie Zeichenfolgen mit 40 Zeichen speichern In der Spalte beträgt der Speicherplatzbedarf 40 Byte + 1 Byte Overhead.

Die Speicheranforderung für eine kurze Zeichenfolge (bis zu 126 Byte) beträgt 1 Byte zuzüglich der tatsächlichen Zeichenfolge, einschließlich der Leerzeichenauffüllung bei Zeichen. Längere Zeichenfolgen haben 4 Byte Overhead anstelle von 1. Lange Zeichenfolgen werden vom System automatisch komprimiert, sodass die physischen Anforderungen auf der Festplatte möglicherweise geringer sind. Sehr lange Werte werden auch in Hintergrundtabellen gespeichert, damit der schnelle Zugriff auf kürzere Spaltenwerte nicht beeinträchtigt wird.

( http://www.postgresql.org/docs/9.0/interactive/datatype-character.html )

Die Größenangabe in VARCHAR dient nur zur Überprüfung der Größe der eingefügten Werte, sie hat keinen Einfluss auf das Festplattenlayout. Tatsächlich VARCHAR- und TEXT-Felder werden in Postgres auf dieselbe Weise gespeichert .

44
Sergey

Ich hatte das gleiche Problem beim Versuch, ein VARCHAR von 32 auf 8 zu kürzen und die Funktion ERROR: value too long for type character varying(8) zu erhalten. Ich möchte so nah wie möglich an SQL bleiben, da ich eine selbst erstellte JPA-ähnliche Struktur verwende, die wir je nach Kundenwunsch möglicherweise auf ein anderes DBMS umstellen müssen (PostgreSQL ist die Standardstruktur). Daher möchte ich den Trick, Systemtabellen zu ändern, nicht anwenden.

Ich habe die USING -Anweisung in der ALTER TABLE:

ALTER TABLE "MY_TABLE" ALTER COLUMN "MyColumn" TYPE varchar(8)
USING substr("MyColumn", 1, 8)

Wie @raylu bemerkte, erhält ALTER eine exklusive Sperre für die Tabelle, sodass alle anderen Vorgänge verzögert werden, bis sie abgeschlossen sind.

32
Matthieu

Das Hinzufügen einer neuen Spalte und das Ersetzen einer neuen durch eine alte funktionierte für mich bei Redshift postgresql. Weitere Informationen finden Sie unter diesem Link https://Gist.github.com/mmasashi/71074

BEGIN;
LOCK users;
ALTER TABLE users ADD COLUMN name_new varchar(512) DEFAULT NULL;
UPDATE users SET name_new = name;
ALTER TABLE users DROP name;
ALTER TABLE users RENAME name_new TO name;
END;
7
spats

Hier ist der Cache der von Greg Smith beschriebenen Seite. Für den Fall, dass dies ebenfalls stirbt, sieht die alter-Anweisung folgendermaßen aus:

UPDATE pg_attribute SET atttypmod = 35+4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

Wenn Ihre Tabelle TABLE1 ist, ist die Spalte COL1 und Sie möchten sie auf 35 Zeichen setzen (die +4 wird für Legacy-Zwecke gemäß dem Link benötigt, möglicherweise der von A.H. in den Kommentaren erwähnte Overhead).

7
Tom

wenn Sie die Änderung in eine Transaktion einfügen, sollte die Tabelle nicht gesperrt werden:

BEGIN;
  ALTER TABLE "public"."mytable" ALTER COLUMN "mycolumn" TYPE varchar(40);
COMMIT;

das funktionierte für mich blitzschnell, wenige Sekunden auf einem Tisch mit mehr als 400.000 Zeilen.

7
jacktrade

Ich habe eine sehr einfache Möglichkeit gefunden, die Größe zu ändern, d. H. Die Annotation @Size (min = 1, max = 50), die Teil von "import javax.validation.constraints" ist, d. H. "Import javax.validation.constraints.Size;"

@Size(min = 1, max = 50)
private String country;


when executing  this is hibernate you get in pgAdmin III 


CREATE TABLE address
(
.....
  country character varying(50),

.....

)
1
Tito