Postgresql-изменение размера столбца varchar

у меня есть вопрос о ALTER TABLE команда на очень большой таблице (почти 30 миллионов строк). Один из его столбцов-это varchar(255) и я хотел бы изменить его размер до varchar(40). В принципе, я хотел бы изменить свой столбец, выполнив следующую команду:

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

у меня нет проблем, если процесс очень длинный, но кажется, что моя таблица больше не читается во время команды ALTER TABLE. Есть ли более умный способ? Может быть, Добавить новый столбец, скопировать данные из старого столбца старый столбец и, наконец, переименовать новый?

любая подсказка будет принята с благодарностью! Заранее спасибо,

примечание: Я использую PostgreSQL 9.0.

8 100
postgresql varchar alter-table

8 ответов:

есть описание того, как это сделать в изменить размер столбца в таблице PostgreSQL без изменения данных. Вы должны взломать данные, каталога базы данных. Единственный способ сделать это официально - с помощью ALTER TABLE, и, как вы заметили, это изменение заблокирует и перепишет всю таблицу во время ее работы.

убедитесь, что Вы читаете Типы Символов раздел документов перед изменением этого. Все виды странных случаев, чтобы быть в курсе здесь. Длина проверка выполняется, когда значения хранятся в строках. Если вы взломаете нижний предел там, это не уменьшит размер существующих значений вообще. Было бы разумно сделать сканирование по всей таблице, ища строки, где длина поля составляет >40 символов после внесения изменений. Вам нужно будет выяснить, как усечь их вручную-так что вы вернетесь к некоторым замкам только на негабаритных-потому что если кто-то попытается обновить что-либо в этой строке, он будет отклонять его как слишком большой сейчас, в точка он идет, чтобы сохранить новую версию строки. Веселье следует за пользователем.

VARCHAR-это ужасный тип, который существует в PostgreSQL только для того, чтобы соответствовать связанной с ним ужасной части стандарта SQL. Если вы не заботитесь о совместимости с несколькими базами данных, рассмотрите возможность хранения данных в виде текста и добавьте ограничение для ограничения его длины. Ограничения, которые вы можете изменить без этой проблемы блокировки/перезаписи таблицы, и они могут выполнять больше проверок целостности, чем просто слабые проверка длины.

60

в PostgreSQL 9.1 есть более простой способ

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

ладно, я, наверное, опаздываю на вечеринку, но ... ..

НЕТ НЕОБХОДИМОСТИ ИЗМЕНЯТЬ РАЗМЕР СТОЛБЦА В ВАШЕМ СЛУЧАЕ!

Postgres, в отличие от некоторых других баз данных, достаточно умен, чтобы использовать только достаточно места, чтобы соответствовать строке (даже используя сжатие для более длинных строк), поэтому даже если ваш столбец объявлен как VARCHAR(255) - если вы храните 40-символьные строки в столбце, использование пространства будет 40 байт + 1 байт накладных расходов.

требование к хранению для короткая строка (до 126 байт) 1 байт плюс фактическая строка, которая включает в себя пробел в случае о характере. Более длинные строки имеют 4 байта вместо 1. Длинные строки сжимаются системой автоматически, поэтому физические требования к диску могут быть меньше. Очень длинные значения также хранятся в фоновых таблицах, чтобы они не мешали быстрому доступ к более короткой колонке ценности.

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

спецификация размера в VARCHAR используется только для проверки размера вставляемых значений, это не влияет на макет диска. На самом деле,VARCHAR и текстовые поля хранятся таким же образом в Postgres.

42

я столкнулся с той же проблемой, пытаясь усечь VARCHAR от 32 до 8 и получить ERROR: value too long for type character varying(8). Я хочу оставаться как можно ближе к SQL, потому что я использую самодельную JPA-подобную структуру, которую нам, возможно, придется переключить на разные СУБД в соответствии с выбором клиента (PostgreSQL по умолчанию). Следовательно, я не хочу использовать трюк изменения системных таблиц.

Я закончил с использованием USING заявление в ALTER TABLE:

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

как отметил @raylu, ALTER получает монопольную блокировку на таблице, поэтому все остальные операции будут отложены до ее завершения.

22

здесь кэш страницы, описанной Грегом Смитом. В случае, если это также умирает, оператор alter выглядит следующим образом:

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

где ваша таблица TABLE1, столбец COL1, и вы хотите установить его в 35 символов (+4 требуется для устаревших целей по ссылке, возможно, накладные расходы, на которые ссылается A. H. В комментариях).

7

Если вы помещаете alter В транзакцию, таблица не должна быть заблокирована:

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

это сработало для меня быстро, несколько секунд на столе с более чем 400k строк.

7

добавление нового столбца и замена нового на старый работали для меня, на redshift postgresql, см. Эту ссылку для получения более подробной информации 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;
4

Я нашел очень простой способ изменить размер, т. е. аннотацию @Size(min = 1, max = 50), которая является частью "import javax.утверждение.ограничения " т. е. "импорт пакета javax.утверждение.ограничения.Размер;"

@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