Postgresql - changer la taille d'une colonne varchar pour une longueur inférieure

Question

J'ai une question sur le ALTER TABLE sur une très grande table (près de 30 millions de lignes). Une de ses colonnes est un varchar(255) et je voudrais le redimensionner à un varchar(40) . En fait, je voudrais changer ma colonne en exécutant la commande suivante :

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

Je n'ai aucun problème si le processus est très long, mais il semble que mon tableau ne soit plus lisible pendant le processus. ALTER TABLE commande. Y a-t-il un moyen plus intelligent ? Peut-être ajouter une nouvelle colonne, copier les valeurs de l'ancienne colonne, supprimer l'ancienne colonne et enfin renommer la nouvelle ?

Nota: J'utilise PostgreSQL 9.0.

Answer 1

Vous trouverez une description de la manière de procéder à l'adresse suivante Redimensionner une colonne dans une table PostgreSQL sans modifier les données . Vous devez pirater les données du catalogue de la base de données. La seule façon de le faire officiellement est d'utiliser ALTER TABLE, et comme vous l'avez noté, cette modification verrouillera et réécrira la table entière pendant son exécution.

Assurez-vous de lire le Types de caractères de la documentation avant de modifier ce point. Il existe toutes sortes de cas étranges dont il faut tenir compte. La vérification de la longueur est effectuée lorsque les valeurs sont stockées dans les rangées. Si vous introduisez une limite inférieure, cela ne réduira pas du tout la taille des valeurs existantes. Il serait judicieux d'effectuer un balayage de l'ensemble de la table pour rechercher les lignes où la longueur du champ est supérieure à 40 caractères après avoir effectué la modification. Vous devrez trouver un moyen de les tronquer manuellement - de manière à récupérer des verrous uniquement sur les lignes trop grandes - parce que si quelqu'un essaie de mettre à jour quelque chose sur cette ligne, le système va la rejeter comme étant trop grande, au moment où il va stocker la nouvelle version de la ligne. L'hilarité s'ensuit pour l'utilisateur.

VARCHAR est un type terrible qui n'existe dans PostgreSQL que pour se conformer à la partie terrible du standard SQL qui lui est associée. Si vous ne vous souciez pas de la compatibilité multi-bases de données, pensez à stocker vos données en tant que TEXT et ajoutez une contrainte pour limiter leur longueur. Les contraintes peuvent être modifiées sans ce problème de verrouillage/réécriture de table, et elles peuvent faire plus de vérification d'intégrité que la simple vérification de la longueur.

Answer 2

Ok, je suis probablement en retard pour la fête, MAIS...

IL N'EST PAS NÉCESSAIRE DE REDIMENSIONNER LA COLONNE DANS VOTRE CAS !

Postgres, contrairement à d'autres bases de données, est suffisamment intelligent pour utiliser juste assez d'espace pour la chaîne (même en utilisant la compression pour les chaînes plus longues), donc même si votre colonne est déclarée comme VARCHAR(255) - si vous stockez des chaînes de 40 caractères dans la colonne, l'espace utilisé sera de 40 octets + 1 octet de surcharge.

Le besoin en mémoire pour une chaîne courte (jusqu'à 126 octets) est de 1 octet plus la chaîne de caractères proprement dite, qui comprend l'espace de remplissage dans le cas de de caractère. Les chaînes plus longues ont 4 octets de surcharge au lieu de 1. Les chaînes plus longues sont automatiquement compressées par le système, de sorte que la besoin physique sur le disque peut être moindre. Les valeurs très longues sont également également stockées dans des tables d'arrière-plan afin de ne pas interférer avec les l'accès rapide aux valeurs plus courtes des colonnes.

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

La spécification de la taille en VARCHAR est uniquement utilisée pour vérifier la taille des valeurs qui sont insérées, elle n'affecte pas la disposition du disque. En effet, Les champs VARCHAR et TEXT sont stockés de la même manière dans Postgres. .

Answer 3

J'ai rencontré le même problème en essayant de tronquer un VARCHAR de 32 à 8 et j'ai obtenu le message suivant ERROR: value too long for type character varying(8) . Je veux rester aussi proche de SQL que possible parce que j'utilise une structure JPA que nous avons créée nous-mêmes et que nous pourrions être amenés à changer de SGBD en fonction des choix du client (PostgreSQL étant le SGBD par défaut). Par conséquent, je ne veux pas utiliser l'astuce consistant à modifier les tables du système.

J'ai fini par utiliser le USING dans le ALTER TABLE :

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

Comme l'a noté @raylu, ALTER acquiert un verrou exclusif sur la table, de sorte que toutes les autres opérations seront retardées jusqu'à ce qu'elle soit terminée.

Answer 4

Ajouter une nouvelle colonne et remplacer la nouvelle par l'ancienne a fonctionné pour moi, sur redshift postgresql, référez-vous à ce lien pour plus de détails. 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 5

Voici le cache de la page décrite par Greg Smith. Dans le cas où cela meurt aussi, la déclaration alter ressemble à ceci :

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

Votre table est TABLE1, la colonne est COL1 et vous voulez la définir à 35 caractères (le +4 est nécessaire à des fins patrimoniales selon le lien, peut-être l'overhead mentionné par A.H. dans les commentaires).