Calculé / calculé / virtual / dérivés de colonnes dans PostgreSQL

N'support de PostgreSQL calculé /colonnes calculées, comme MS SQL Server? Je ne trouve rien dans la doc, mais comme cette fonctionnalité est incluse dans de nombreux autres Sgbd, j'ai pensé que j'ai peut-être raté quelque chose.

Par exemple: http://msdn.microsoft.com/en-us/library/ms191250.aspx

  • À l'aide de la face latérale de la sous-requête expression (Postgres fonction), vous pouvez facilement ajouter d'autres colonnes pour chaque ligne.
InformationsquelleAutor Mike Chamberlain | 2011-11-23
  1. 95

    Jusqu'à Postgres 11 généré colonnes ne sont pas prises en charge telles que définies dans la norme SQL et mis en œuvre par certains SGBD: DB2, MySQL et Oracle. Ni le semblable "colonnes calculées" de SQL Server.

    STORED généré colonnes sont introduits avec Postgres 12. Exemple Trivial:

    CREATE TABLE tbl (
  int1    int
, int2    int
, product bigint GENERATED ALWAYS AS (int1 * int2) STORED
);

    db<>violon ici

    VIRTUAL généré des colonnes peut venir avec la prochaine itération. Connexes:

    Jusqu'alors, vous pouvez émuler VIRTUAL généré colonnes avec un fonction à l'aide de attribut notation (tbl.col) que ressemble et fonctionne comme un virtuel généré colonne. C'est un peu une syntaxe bizarrerie qui existe dans Postgres pour des raisons historiques, et qui fit l'affaire. Cette réponse a exemples de code:

    L'expression (ressemblant à une colonne) n'est pas inclus dans un SELECT * FROM tbl, cependant. Vous avez toujours de liste explicitement.

    Peuvent également être pris en charge avec un correspondant l'expression de l'indice de - à condition que la fonction est IMMUTABLE. Comme:

    CREATE FUNCTION col(tbl) ... AS ...  -- your computed expression here
CREATE INDEX ON tbl(col(tbl));

    Alternatives

    Alternativement, vous pouvez implémenter des fonctionnalités similaires avec un VUE, éventuellement couplé avec l'expression d'index. Puis SELECT * peut inclure l'généré colonne.

    "Persistantes" (STORED) les colonnes calculées peuvent être mis en œuvre avec les déclencheurs dans une fonctionnellement identiques.

    Les vues matérialisées sont étroitement liés concept, mis en œuvre depuis Postgresql 9.3.

    Dans les versions antérieures, on peut gérer MVs manuellement.

    • Selon la quantité de données que vous êtes en train de charger à la fois.. déclencheur peut ralentir les choses de façon drastique. Souhaitez mai à envisager des mises à jour à la place.
    • Ces solutions sont assez inutile (sans d'énormes modifications de code à une base de code avec pas de cas de test) lors de la migration d'oracle vers postgresql. Existe-il des solutions de la migration point de vue ?
    • Veuillez poser votre question dans la question. Les commentaires ne sont pas le lieu. Vous pouvez toujours associer à cette question pour le contexte (et ajouter un commentaire ici pour obtenir mon attention et lien à la question connexe).
    • Fait. Juste ne pense pas que cela justifie une nouvelle question. stackoverflow.com/questions/39824244/... Cheers
    • La fonctionnalité est en cours de développement: commitfest.postgresql.org/16/1443
    • il y a donc plusieurs façons de faire quelque chose de si simple et commune dans Postgres, quelqu'un peut expliquer comment aller sur la sélection de l'une des façons?
    • Dépend des détails de votre configuration et de vos exigences. Vous pouvez poser une nouvelle question avec les informations nécessaires.
    • Peut-être mettre à jour la réponse à mentionner PostgreSQL 12 Beta1 ?
    • Il a été déjà mentionné. J'ai ajouté un exemple de code maintenant.

    InformationsquelleAutor Erwin Brandstetter
  2. 25

    OUI, vous pouvez!! La solution doit être facile, sûr, performant et...

    Je suis nouveau sur postgresql, mais il semble que vous pouvez créer des colonnes calculées à l'aide d'un l'expression de l'indice de, jumelé avec un vue (la vue est facultatif, mais fait rend la vie un peu plus facile).

    Suppose que mon calcul est md5(some_string_field), puis-je créer l'index comme: 

    CREATE INDEX some_string_field_md5_index ON some_table(MD5(some_string_field));

    Maintenant, toutes les requêtes qui agissent sur MD5(some_string_field) utilisera l'index plutôt que le calcul à partir de zéro. Par exemple:

    SELECT MAX(some_field) FROM some_table GROUP BY MD5(some_string_field);

    Vous pouvez le vérifier avec expliquer.

    Toutefois, à ce stade, vous êtes en s'appuyant sur les utilisateurs de la table savoir exactement comment construire la colonne. Pour rendre la vie plus facile, vous pouvez créer un VIEW sur une version augmentée de la table d'origine, en ajoutant à la valeur calculée dans une nouvelle colonne:

    CREATE VIEW some_table_augmented AS 
   SELECT *, MD5(some_string_field) as some_string_field_md5 from some_table;

    Maintenant toutes les requêtes à l'aide de some_table_augmented sera en mesure d'utiliser some_string_field_md5 sans se soucier de la façon dont il fonctionne..ils ont juste obtenir de bonnes performances. La vue n'est pas la copie de données à partir de la table d'origine, donc c'est une bonne mémoire-sage ainsi que de performance. Notez toutefois que vous ne pouvez pas mettre à jour/insérer une vue, uniquement dans la table source, mais si vous voulez vraiment, je crois que vous pouvez rediriger les insertions et mises à jour de la table source à l'aide de les règles (j'ai peut-être tort sur ce dernier point que je n'ai jamais essayé moi-même).

    Edit: il semble que si la requête implique des indices de compétition, l'organisateur de moteur peut parfois ne pas utiliser l'expression de l'index à tous. Le choix semble être données à charge.

    • Pourriez-vous veuillez expliquer ou donner un exemple de if the query involves competing indices?
    InformationsquelleAutor dan-man
  3. 12

    Une façon de le faire est avec un déclencheur!

    CREATE TABLE computed(
    one SERIAL,
    two INT NOT NULL
);

CREATE OR REPLACE FUNCTION computed_two_trg()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $BODY$
BEGIN
    NEW.two = NEW.one * 2;

    RETURN NEW;
END
$BODY$;

CREATE TRIGGER computed_500
BEFORE INSERT OR UPDATE
ON computed
FOR EACH ROW
EXECUTE PROCEDURE computed_two_trg();

    Le trigger est déclenché avant que la ligne est insérée ou mis à jour. Il modifie le champ que nous voulons calculer de NEW enregistrer et ensuite, il retourne cet enregistrement.

    • Quand le déclencheur acutally feu ? J'ai couru le dessus et cela a fait insert into computed values(1, 2); insert into computed values(4, 8); commit; select * from computed; et il revient tout juste :1 2 4 8
    • essayez insert into computed(one) values(1); insert into computed(one) values(4); commit; select * from computed; la valeur de la two colonne sera calculé automatiquement!
    InformationsquelleAutor Elmer
  4. 4

    PostgreSQL 12 prend en charge générée colonnes:

    PostgreSQL 12 Beta 1 De Sortir!

    Généré Colonnes

    PostgreSQL 12 permet de créer des colonnes de calcul de leurs valeurs avec une expression à l'aide du contenu des autres colonnes. Cette fonctionnalité fournit stockées généré colonnes, qui sont calculés sur les plaquettes et les mises à jour et sont enregistrés sur le disque. Virtuel généré colonnes, qui sont calculés uniquement lorsqu'une colonne est lu dans le cadre d'une requête, ne sont pas encore mis en œuvre.

    Généré Colonnes

    Un générés colonne est une colonne spéciale qui est toujours calculé à partir d'autres colonnes. Ainsi, il est pour les colonnes de ce point de vue est pour les tables. 

    CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm * 2.54) STORED
);

    db<>le violon de démonstration

    InformationsquelleAutor Lukasz Szozda
  5. 0

    J'ai un code qui fonctionne et d'utiliser le terme calculé, je ne suis pas sur postgresSQL pur tho nous courons sur PADB

    ici est de savoir comment il est utilisé

    create table some_table as
    select  category, 
            txn_type,
            indiv_id, 
            accum_trip_flag,
            max(first_true_origin) as true_origin,
            max(first_true_dest ) as true_destination,
            max(id) as id,
            count(id) as tkts_cnt,
            (case when calculated tkts_cnt=1 then 1 else 0 end) as one_way
    from some_rando_table
    group by 1,2,3,4    ;
    • Qu'est-ce que PADB exactement?
    • ParAccel Base de données Analytique est vieux mais sympa...en.wikipedia.org/wiki/ParAccel
    • Mais comment est-il lié à une question sur Postgres? Certes, il y a beaucoup de DBs, avec l'appui des colonnes calculées.
    • ah désolé, je ne voulais pas le temps de se remettre en contexte.... PADB est postgress à base!
    InformationsquelleAutor Wired604
  6. -2

    Bien, ne sais pas si c'est ce que Tu veux dire mais Posgres normalement soutien de "dummy" ETL syntaxe.
    J'ai créé une colonne vide dans le tableau et le remplir par calculés dossiers selon les valeurs dans la ligne.

    UPDATE table01
SET column03 = column01*column02; /*e.g. for multiplication of 2 values*/
    1. Il est donc factice je soupçonne que c'est pas ce que Vous cherchez.
    2. Évidemment, il n'est pas dynamique, vous l'exécuter une fois. Mais aucun obstacle pour arriver à déclencher.
    InformationsquelleAutor ďobo
  7. -5

    Une solution légère et avec la contrainte de Vérification:

    CREATE TABLE example (
    discriminator INTEGER DEFAULT 0 NOT NULL CHECK (discriminator = 0)
);
    • Comment est-ce lié à la notion d'une colonne calculée? Pourriez-vous expliquer?
    • D'accord, c'est pas directement lié. Mais c'est une substitution pour un cas simple quand vous avez juste besoin de faire quelque chose comme field as 1 persisted.
    • Une description aurait été en effet de nice. Je pense que cette réponse est que si le calcul peut être fait avec la clause de défaut, alors vous pouvez utiliser une valeur par défaut et une contrainte de vérification pour empêcher quiconque de modifier la valeur.
    • Bradbury: d'Accord, mais qui ne fonctionne que pour l'insertion. Il ne fonctionnera pas si une personne à la charge de la colonne s'est mis à jour.
    InformationsquelleAutor cinereo