node.js - 添加具有随机值的列 Sequelize PostgreSQL

Question

我想添加带有 NOT_NULL 约束的列，这样列将包含随机 默认值 以下是我的代码我该怎么做

up: function (queryInterface, Sequelize, done) {
queryInterface.addColumn(
    {
      tableName: 'someTable',
      schema: 'sometemplate'
    },
    'someColumn', //column name
    { //column date type and constraint
        type: Sequelize.STRING,
        allowNull: false,
        defaultValue: // I want this to random value
    })
    .then(function () { done() })
    .catch(done);
}

Answer 1

PostgreSQL 示例:

CREATE OR REPLACE FUNCTION f_random_text(
    length integer
)
RETURNS text AS
$body$
WITH chars AS (
    SELECT unnest(string_to_array('A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9', ' ')) AS _char
),
charlist AS
(
    SELECT _char FROM chars ORDER BY random() LIMIT $1
)
SELECT string_agg(_char, '')
FROM charlist
;
$body$
LANGUAGE sql;


DROP TABLE IF EXISTS tmp_test;


CREATE TEMPORARY TABLE tmp_test (
    id serial,
    data text default f_random_text(12)
);


INSERT INTO tmp_test
VALUES 
    (DEFAULT, DEFAULT),
    (DEFAULT, DEFAULT)
;


SELECT * FROM tmp_test;

 id |     data
----+--------------
  1 | RYMUJH4E0NIQ
  2 | 7U4029BOKAEJ
(2 rows)

显然你可以做到这一点。 (当然，您也可以添加其他字符，或者也可以使用其他随机字符串生成器 - 例如这样。)引用:https://dba.stackexchange.com/questions/19632/how-to-create-column-in-db-with-default-value-random-string