Postgresql - changer la taille d'une colonne de varchar
j'ai une question à propos de la commande ALTER TABLE sur une très grande table (presque 30 millions de lignes).
Une de ses colonnes est un varchar(255) et je voudrais le redimensionner à un varchar(40) .
Fondamentalement, 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 ma table ne soit plus lisible pendant la commande ALTER TABLE. Est-il une façon plus intelligente? Peut-être ajouter une nouvelle colonne, copie valeurs de l'ancienne colonne, laisser tomber l'ancienne colonne et finalement renommer la nouvelle?
tout indice sera grandement apprécié! Merci d'avance,
Note: J'utilise PostgreSQL 9.0.
8 réponses
il y a une description de la façon de faire ceci à redimensionner une colonne dans une table PostgreSQL sans changer les données . Tu dois pirater les données du catalogue de la base de données. La seule façon de le faire officiellement est avec ALTER TABLE, et comme vous avez noté que le changement se verrouillera et réécrira la table entière pendant qu'elle est en cours d'exécution.
assurez-vous de lire la section types de caractères du docs avant de changer ceci. Toutes sortes de cas bizarres pour être au courant d'ici. Le contrôle de longueur est effectué lorsque les valeurs sont stockées dans les lignes. Si vous Hacker une limite inférieure là-dedans, cela ne réduira pas la taille des valeurs existantes du tout. Il serait sage de faire un scan sur toute la table à la recherche de lignes où la longueur du champ est >40 caractères après avoir fait le changement. Vous aurez besoin de comprendre comment les tronquer manuellement--donc vous êtes de retour quelques serrures juste sur les plus grandes--parce que si quelqu'un essaie de mettre à jour quelque chose sur cette rangée il va de la rejeter comme trop grand maintenant, au point où il va stocker la nouvelle version de la ligne. Hilarité s'ensuit pour l'utilisateur.
VARCHAR est un type terrible qui existe à PostgreSQL seulement pour se conformer à sa partie terrible associée de la norme SQL. Si vous ne vous souciez pas de la compatibilité multi-bases de données, pensez à stocker vos données sous forme de texte et ajouter une contrainte pour en limiter la longueur. Les contraintes que vous pouvez changer autour sans ce problème de verrouillage/réécriture de table, et ils peuvent faire plus le contrôle d'intégrité ne se limite pas au contrôle de faible longueur.
dans PostgreSQL 9.1 il y a un moyen plus facile
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, je suis probablement en retard à la fête, mais...
PAS BESOIN DE REDIMENSIONNER LA COLONNE DANS VOTRE CAS!
Postgres, contrairement à d'autres bases de données, est assez intelligent pour utiliser juste assez d'espace pour s'adapter à la chaîne (même en utilisant la compression pour des 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'utilisation de l'espace sera de 40 octets + 1 octet de overhead.
L'exigence de stockage pour une courte chaîne (jusqu'à 126 octets) est de 1 octet plus la corde réelle, qui inclut le rembourrage de l'espace dans le cas de caractère. Les chaînes plus longues ont 4 octets de overhead au lieu de 1. Les longues chaînes sont compressées automatiquement par le système, exigence physique sur le disque pourrait être moins. Les valeurs très longues sont également stockées dans les tableaux de référence de sorte qu'ils n'interfèrent pas avec rapide accès à des valeurs de colonne plus courtes.
( http://www.postgresql.org/docs/9.0/interactive/datatype-character.html )
la spécification de taille dans VARCHAR n'est utilisée que pour vérifier la taille des valeurs qui sont insérées, elle n'affecte pas la disposition du disque. En fait, VARCHAR et les champs de texte sont stockés de la même manière dans Postgres .
je faisais face au même problème en essayant de tronquer un VARCHAR de 32 à 8 et obtenir le ERROR: value too long for type character varying(8) . Je veux rester aussi proche de SQL que possible parce que j'utilise une structure faite par moi-même comme JPA que nous pourrions avoir à passer à différents SGBD selon les choix du client (PostgreSQL étant la valeur par défaut). Donc, je ne veux pas utiliser l'astuce de modifier les tables Système.
j'ai terminé en utilisant le USING déclaration dans le ALTER TABLE :
ALTER TABLE "MY_TABLE" ALTER COLUMN "MyColumn" TYPE varchar(8)
USING substr("MyColumn", 1, 8)
comme @raylu l'a noté, ALTER acquiert une serrure exclusive sur la table de sorte que toutes les autres opérations seront retardées jusqu'à ce qu'elles soient terminées.
Voici le cache de la page décrite par Greg Smith. Dans le cas où cela meurt aussi bien, l'alter déclaration ressemble à ceci:
UPDATE pg_attribute SET atttypmod = 35+4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';
où votre table est TABLE1, la colonne est COL1 et vous voulez la mettre à 35 caractères (le +4 est nécessaire pour les besoins d'héritage selon le lien, peut-être le overhead mentionné par A. H. dans les commentaires).
si vous mettez les modifier dans une transaction le tableau ne doit pas être verrouillé:
BEGIN;
ALTER TABLE "public"."mytable" ALTER COLUMN "mycolumn" TYPE varchar(40);
COMMIT;
cela a fonctionné pour moi flambant rapide, quelques secondes sur une table avec plus de 400k rangs.
ajout d'une nouvelle colonne et remplacement d'une nouvelle avec l'ancienne travaillée pour moi, sur redshift postgresql, référez 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;
j'ai trouvé un moyen très simple de changer la taille c'est à dire l'annotation @Size(min = 1, max = 50) qui fait partie de" importer javax.validation.les contraintes" c'est à dire "import javax.validation.contraintes.Taille; "
@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),
.....
)