nombre - Postgresql-cambia el tamaño de una columna varchar

insertar columna en postgresql

Question

Tengo una pregunta sobre el comando ALTER TABLE en una tabla realmente grande (casi 30 millones de filas). Una de sus columnas es varchar(255) y me gustaría cambiar su tamaño a varchar(40) . Básicamente, me gustaría cambiar mi columna ejecutando el siguiente comando:

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

No tengo ningún problema si el proceso es muy largo, pero parece que mi tabla no es más legible durante el comando ALTER TABLE. ¿Hay una manera más inteligente? ¿Tal vez agregar una nueva columna, copiar los valores de la columna anterior, soltar la columna anterior y, finalmente, cambiar el nombre de la nueva?

¡Cualquier pista será muy apreciada! Gracias por adelantado,

Nota: uso PostgreSQL 9.0.

Answer 1

Agregar una nueva columna y reemplazar una nueva con la antigua funcionó para mí, en redshift postgresql, consulte este enlace para obtener más información 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;

Answer 2

Aquí está el caché de la página descrita por Greg Smith. En caso de que también muera, la declaración alter se ve así:

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

Donde su tabla es TABLA1, la columna es COL1 y desea establecerla en 35 caracteres (el +4 es necesario para fines heredados de acuerdo con el enlace, posiblemente la tara referida por AH en los comentarios).

Answer 3

En PostgreSQL 9.1 hay una manera más fácil

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

Answer 4

Estaba enfrentando el mismo problema al intentar truncar un VARCHAR de 32 a 8 y obtener el ERROR: value too long for type character varying(8) . Quiero estar lo más cerca posible de SQL porque estoy usando una estructura tipo JPA hecha a sí misma que podría tener que cambiar a diferentes DBMS de acuerdo con las elecciones del cliente (PostgreSQL es el predeterminado). Por lo tanto, no quiero usar el truco de alterar las tablas del sistema.

Terminé usando la instrucción USING en ALTER TABLE :

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

Realmente no he considerado las implicaciones si otro programa está accediendo a la tabla o si la tabla está indexada. ¿Asumo que Postgres hace ese tipo de automáticamente?

Answer 5

Hay una descripción de cómo hacer esto en Redimensionar una columna en una tabla de PostgreSQL sin cambiar los datos . Tienes que hackear los datos del catálogo de la base de datos. La única manera de hacerlo oficialmente es con ALTER TABLE, y como ha notado, el cambio bloqueará y reescribirá toda la tabla mientras se está ejecutando.

Asegúrese de leer la sección Tipos de caracteres de los documentos antes de cambiar esto. Todo tipo de casos extraños para tener en cuenta aquí. La verificación de longitud se realiza cuando los valores se almacenan en las filas. Si hackea un límite inferior allí, eso no reducirá el tamaño de los valores existentes en absoluto. Sería bueno hacer un escaneo sobre toda la tabla buscando filas donde la longitud del campo sea> 40 caracteres después de hacer el cambio. Tendrás que averiguar cómo truncarlos manualmente, de modo que estás retrocediendo algunos bloqueos en los de mayor tamaño, porque si alguien intenta actualizar algo en esa fila, lo rechazará como demasiado grande ahora, en el punto va a almacenar la nueva versión de la fila. Hilarity sigue para el usuario.

VARCHAR es un tipo terrible que existe en PostgreSQL solo para cumplir con su terrible parte asociada del estándar SQL. Si no le importa la compatibilidad de bases de datos múltiples, considere almacenar sus datos como TEXTO y agregue una restricción para limitar su longitud. Restricciones que puede cambiar sin este problema de bloqueo / reescritura de tabla, y pueden hacer más comprobaciones de integridad que solo la verificación de longitud débil.

Answer 6

He encontrado una manera muy fácil de cambiar el tamaño, es decir, la anotación @Size (min = 1, max = 50) que es parte de "import javax.validation.constraints", es decir, "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),
.....
)

Answer 7

Ok, probablemente llegue tarde a la fiesta, PERO ...

¡NO HAY NECESIDAD PARA REDIMENSIONAR LA COLUMNA EN SU CASO!

Postgres, a diferencia de otras bases de datos, es lo suficientemente inteligente como para usar solo el espacio suficiente para ajustarse a la cadena (incluso usando compresión para cadenas más largas), incluso si su columna está declarada como VARCHAR (255) - si almacena cadenas de 40 caracteres en la columna, el uso del espacio será de 40 bytes + 1 byte de sobrecarga.

El requisito de almacenamiento para una cadena corta (hasta 126 bytes) es de 1 byte más la cadena real, que incluye el relleno de espacio en el caso del carácter. Las cadenas más largas tienen 4 bytes de sobrecarga en lugar de 1. Las cadenas largas son comprimidas por el sistema automáticamente, por lo que el requisito físico en el disco puede ser menor. Los valores muy largos también se almacenan en tablas de fondo para que no interfieran con el acceso rápido a valores de columna más cortos.

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

La especificación de tamaño en VARCHAR solo se usa para verificar el tamaño de los valores que se insertan, no afecta el diseño del disco. De hecho, los campos VARCHAR y TEXT se almacenan de la misma manera en Postgres .

Answer 8

si coloca el alter en una transacción, la tabla no se debe bloquear:

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

esto funcionó para mí ardiendo rápidamente, unos segundos en una mesa con más de 400k filas.