Postgresql – How to create column in DB with default value random string

default valuepostgresqlrandom

Can I create column in DB table (PostgreSQL) which have default value random string, and how ?

If is not possible, please let me know that.

Best Answer

PostgreSQL example:

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)

Apparently you can do this. (Of course, you can add other characters as well, or use other random string generator as well - like this, for example.)

Related Solutions

Postgresql – How to use a default value in a Select query in PostgreSQL

SELECT coalesce(MAX(post_id),0) AS max_id FROM my_table WHERE org_id = 3

SELECT case count(*) when 0 then 0 else MAX(post_id) end AS max_id
FROM my_table 
WHERE org_id = 3;

if you want max(post_id) to be null when there is 1 row but post_id is null

_dbfiddle

Oracle random number as default column value

Trigger is the only way to do it.

Virtual column is different from what you want, and that will not accept DBMS_RANDOM.RANDOM, because that is not deterministic:

Column Expressions

...it can contain only the following forms of expression:

Columns of the subject table — the table being created, altered, or indexed

Constants (strings or numbers)

Deterministic functions — either SQL built-in functions or user-defined functions

The syntax of default values for columns is similar to your original statement, but that does not accept DBMS_RANDOM.RANDOM because PL/SQL is not allowed there:

Restrictions on Default Column Values

Default column values are subject to the following restrictions:

A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

The expression can be of any form except a scalar subquery expression.

Best Answer

Related Solutions

Postgresql – How to use a default value in a Select query in PostgreSQL

Oracle random number as default column value

Related Question