Postgresql - zmień rozmiar kolumny varchar na mniejszą długość

Mam pytanie odnośnie ALTER TABLEpolecenia na naprawdę dużym stole (prawie 30 milionów wierszy). Jedna z jego kolumn to a varchar(255)i chciałbym zmienić jej rozmiar na a varchar(40). Zasadniczo chciałbym zmienić moją kolumnę, uruchamiając następujące polecenie:

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

Nie mam problemu, jeśli proces jest bardzo długi, ale wydaje się, że moja tabela nie jest bardziej czytelna podczas polecenia ALTER TABLE. Czy jest mądrzejszy sposób? Może dodać nową kolumnę, skopiować wartości ze starej kolumny, usunąć starą kolumnę i na koniec zmienić nazwę nowej?

Każda wskazówka będzie bardzo mile widziana! Z góry dziękuję,

Uwaga: używam PostgreSQL 9.0.

Opis tego, jak to zrobić, znajduje się w sekcji Zmień rozmiar kolumny w tabeli PostgreSQL bez zmiany danych . Musisz włamać się do danych katalogu bazy danych. Jedynym sposobem, aby to zrobić oficjalnie, jest ALTER TABLE, a jak zauważyłeś, zmiana spowoduje zablokowanie i przepisanie całej tabeli podczas jej działania.

Upewnij się, że przeczytałeś sekcję Typy znaków w dokumentach przed zmianą tego. Różne dziwne przypadki, o których należy tutaj pamiętać. Kontrola długości jest wykonywana, gdy wartości są przechowywane w wierszach. Jeśli zhakujesz tam dolny limit, w ogóle nie zmniejszy to rozmiaru istniejących wartości. Po dokonaniu zmiany warto przeskanować całą tabelę, szukając wierszy, w których długość pola przekracza 40 znaków. Będziesz musiał wymyślić, jak skrócić je ręcznie - więc wróciłeś do niektórych blokad tylko tych dużych rozmiarów - ponieważ jeśli ktoś spróbuje zaktualizować cokolwiek w tym wierszu, odrzuci to teraz jako zbyt duże, w tym momencie przechowuje nową wersję wiersza. Dla użytkownika nastaje wesołość.

VARCHAR to okropny typ, który istnieje w PostgreSQL tylko po to, aby zachować zgodność z powiązaną z nim straszną częścią standardu SQL. Jeśli nie zależy Ci na kompatybilności z wieloma bazami danych, rozważ przechowywanie danych jako TEKST i dodaj ograniczenie, aby ograniczyć jego długość. Ograniczenia, które można zmieniać bez problemu z blokowaniem / przepisywaniem tabeli, i mogą wykonywać więcej sprawdzania integralności niż tylko sprawdzanie słabej długości.

W PostgreSQL 9.1 jest prostszy sposób

http://www.postgresql.org/message-id/162867790801110710g3c686010qcdd852e721e7a559@mail.gmail.com

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

Ok, pewnie spóźniłem się na imprezę, ALE ...

NIE MA POTRZEBY ZMIENIANIA KOLUMNY W TWOIM PRZYPADKU!

Postgres, w przeciwieństwie do niektórych innych baz danych, jest na tyle inteligentny, że używa tylko wystarczającej ilości miejsca, aby dopasować ciąg (nawet używając kompresji dla dłuższych ciągów), więc nawet jeśli twoja kolumna jest zadeklarowana jako VARCHAR (255) - jeśli przechowujesz 40-znakowe ciągi w w kolumnie, użycie miejsca wyniesie 40 bajtów + 1 bajt narzutu.

Wymagane miejsce na przechowywanie krótkiego ciągu (do 126 bajtów) to 1 bajt plus rzeczywisty ciąg, który obejmuje wypełnienie spacjami w przypadku znaku. Dłuższe łańcuchy mają 4 bajty narzutu zamiast 1. Długie łańcuchy są kompresowane przez system automatycznie, więc fizyczne wymagania na dysku mogą być mniejsze. Bardzo długie wartości są również przechowywane w tabelach w tle, aby nie przeszkadzały w szybkim dostępie do krótszych wartości w kolumnach.

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

Specyfikacja rozmiaru w VARCHAR służy tylko do sprawdzania rozmiaru wstawianych wartości, nie wpływa na układ dysku. W rzeczywistości pola VARCHAR i TEXT są przechowywane w ten sam sposób w Postgres .

Miałem ten sam problem, próbując skrócić VARCHAR z 32 do 8 i uzyskać rozszerzenie ERROR: value too long for type character varying(8). Chcę pozostać jak najbliżej SQL, ponieważ używam samodzielnie wykonanej struktury podobnej do JPA, która może być konieczna do przełączenia się na inny DBMS zgodnie z wyborami klienta (domyślnie PostgreSQL). Dlatego nie chcę używać sztuczki polegającej na zmienianiu tabel systemowych.

Skończyłem używając USINGoświadczenia w ALTER TABLE:

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

Jak zauważył @raylu, ALTERuzyskuje wyłączną blokadę stołu, więc wszystkie inne operacje będą opóźnione do zakończenia.

Dodając nową kolumnę i zastępując nową starą, pracowałem dla mnie, na redshift postgresql, zapoznaj się z tym linkiem, aby uzyskać więcej informacji https://gist.github.com/mmasashi/7107430

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;

Oto pamięć podręczna strony opisanej przez Grega Smitha. W przypadku, gdy to również umrze, oświadczenie alter wygląda następująco:

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

Jeśli twoja tabela to TABELA1, kolumna to COL1 i chcesz ustawić ją na 35 znaków (+4 jest potrzebne do starszych celów zgodnie z linkiem, prawdopodobnie narzut, do którego odnosi się AH w komentarzach).

jeśli umieścisz zmianę w transakcji, tabela nie powinna być blokowana:

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

to zadziałało dla mnie błyskawicznie, kilka sekund na stole z ponad 400 tysiącami wierszy.

Znalazłem bardzo łatwy sposób na zmianę rozmiaru, tj. Adnotację @Size (min = 1, max = 50), która jest częścią "importu javax.validation.constraints" tj. "Importu 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),

.....

)

Spróbuj uruchomić następującą tabelę zmian:

ALTER TABLE public.users 
ALTER COLUMN "password" TYPE varchar(300) 
USING "password"::varchar;