postgres length large columns autoincrement string postgresql text types varchar

string - length - postgresql varchar vs text



PostgreSQL: Diferencia entre texto y varchar(caracteres que varían) (8)

¿Cuál es la diferencia entre el tipo de datos de text y los tipos de datos que character varying el character varying ( varchar )?

Según la documentación.

Si se usa la variación de caracteres sin el especificador de longitud, el tipo acepta cadenas de cualquier tamaño. La última es una extensión de PostgreSQL.

y

Además, PostgreSQL proporciona el tipo de texto, que almacena cadenas de cualquier longitud. Aunque el texto de tipo no está en el estándar SQL, varios otros sistemas de administración de bases de datos SQL también lo tienen.

Entonces, ¿cuál es la diferencia?

ACTUALIZACIÓN DE PUNTOS DE REFERENCIA PARA 2016 (pg9.5 +)

Y utilizando puntos de referencia de "SQL puro" (sin ningún script externo)

  1. usar cualquier string_generator con UTF8

  2. principales puntos de referencia:

    2.1. INSERTAR

    2.2. SELECCIONAR comparando y contando

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;

Preparar prueba específica (ejemplos)

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

Realizar una prueba básica:

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

Y otras pruebas,

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;

... Y use EXPLAIN ANALYZE .

ACTUALIZADO DE NUEVO 2018 (pg10)

Pequeña edición para agregar los resultados de 2018 y reforzar las recomendaciones.

Resultados en 2016 y 2018.

Mis resultados, después de la media, en muchas máquinas y muchas pruebas: todos iguales
(estadísticamente menos que la desviación estándar).

Recomendación

  • Utilice el tipo de datos de text ,
    evite el varchar(x) antiguo varchar(x) porque a veces no es un estándar, por ejemplo, en las cláusulas CREATE FUNCTION varchar(x) ≠ varchar(y) .

  • límites expresos (¡con el mismo rendimiento varchar !) con la cláusula CHECK en CREATE TABLE
    por ejemplo, CHECK(char_length(x)<=10) .
    Con una pérdida insignificante de rendimiento en INSERTAR / ACTUALIZAR, también puede controlar los rangos y la estructura de la cadena
    por ejemplo, CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE ''Hello%'')

Algo OT: si está utilizando Rails, el formato estándar de las páginas web puede ser diferente. Para los formularios de entrada de datos, text cuadros de text son desplazables, pero los cuadros de character varying ( string Rails) son de una línea. Mostrar vistas son tan largas como sea necesario.

Como indican los " Tipos de caracteres " en la documentación, varchar(n) , char(n) y el text se almacenan de la misma manera. La única diferencia es que se necesitan ciclos adicionales para verificar la longitud, si se da uno, y el espacio y el tiempo adicionales requeridos si se necesita relleno para char(n) .

Sin embargo, cuando solo necesita almacenar un solo carácter, hay una ligera ventaja de rendimiento al usar el tipo especial "char" (mantenga las comillas dobles, son parte del nombre del tipo). Obtendrá un acceso más rápido al campo y no hay gastos generales para almacenar la longitud.

Acabo de hacer una tabla de 1,000,000 de "char" azar elegidos del alfabeto en minúsculas. Una consulta para obtener una distribución de frecuencia ( select count(*), field ... group by field ) toma aproximadamente 650 milisegundos, frente a aproximadamente 760 en los mismos datos usando un campo de text .

En el manual de PostgreSQL.

No hay diferencia de rendimiento entre estos tres tipos, aparte del aumento de espacio de almacenamiento cuando se usa el tipo de relleno en blanco, y algunos ciclos de CPU adicionales para verificar la longitud al almacenar en una columna de longitud limitada. Si bien el carácter (n) tiene ventajas de rendimiento en algunos otros sistemas de bases de datos, no existe tal ventaja en PostgreSQL; de hecho, el carácter (n) suele ser el más lento de los tres debido a sus costos de almacenamiento adicionales. En la mayoría de las situaciones, se debe utilizar texto o caracteres que varíen.

Suelo usar texto

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

En mi opinión, varchar(n) tiene sus propias ventajas. Sí, todos usan el mismo tipo subyacente y todo eso. Pero, debe señalarse que los índices en PostgreSQL tienen su límite de tamaño de 2712 bytes por fila.

TL; DR: si usa el tipo de text sin una restricción y tiene índices en estas columnas, es muy posible que alcance este límite para algunas de sus columnas y obtenga un error cuando intente insertar datos pero con el uso de varchar(n) , Usted puede prevenirlo.

Algunos detalles más: el problema aquí es que PostgreSQL no da ninguna excepción al crear índices para el tipo de text o varchar(n) donde n es mayor que 2712. Sin embargo, dará un error cuando un registro con un tamaño comprimido mayor que 2712 Se intenta insertar. Esto significa que puede insertar 100.000 caracteres de cadena que se compone de caracteres repetitivos fácilmente porque se comprimirá muy por debajo de 2712, pero es posible que no pueda insertar una cadena con 4000 caracteres porque el tamaño comprimido es mayor que 2712 bytes. Usando varchar(n) donde n no es mucho mayor que 2712, estás a salvo de estos errores.

No hay diferencia, bajo el capó es todo varlena ( matriz de longitud variable ).

Consulte este artículo en Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Un par de destacados:

Para resumir todo esto:

  • char (n): ocupa demasiado espacio cuando se trata de valores más cortos que n (los rellena con n ), y puede provocar errores sutiles debido a la adición de espacios al final, además es problemático cambiar el límite
  • varchar (n): es problemático cambiar el límite en el entorno real (requiere bloqueo exclusivo al modificar la tabla)
  • varchar - al igual que el texto
  • texto - para mí un ganador - sobre (n) tipos de datos porque carece de problemas, y sobre varchar - porque tiene un nombre distinto

El artículo realiza pruebas detalladas para demostrar que el rendimiento de las inserciones y las selecciones para los 4 tipos de datos son similares. También analiza detalladamente formas alternativas para restringir la longitud cuando sea necesario. Las restricciones o dominios basados ​​en funciones proporcionan la ventaja de un aumento instantáneo de la restricción de longitud, y sobre la base de que es raro disminuir una restricción de longitud de cadena, depesz concluye que una de ellas suele ser la mejor opción para un límite de longitud.

text y varchar tienen diferentes conversiones de tipo implícitas. El mayor impacto que he notado es el manejo de los espacios finales. Por ejemplo ...

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

devuelve true, false, true y no true, true, true como podría esperarse.

character varying(n) , varchar(n) - (ambos son iguales). el valor se truncará en n caracteres sin generar un error.

character(n) , char(n) - (Ambos son iguales). de longitud fija y se rellenará con espacios en blanco hasta el final de la longitud.

text - longitud ilimitada.

Ejemplo:

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

Obtenemos los resultados:

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