Как реализовать заполнение таблицы тестовыми данными

Question

Как заполнить таблицу тестовыми данными 5000 строк(данные осознанные именно фамилия, имя, отчество не цифры и не хеш)

CREATE TABLE parents
(
parent_id integer PRIMARY KEY,
last_name varchar,
first_name varchar,
middle_name varchar
);

Answer 1

Не такая и сложная задача.

Код ниже генерирует 5000 уникальных имён, но если убрать ограничения rn <= 2500, то сгенерирует 50*49*50 + 50*49*50 == 245 000. А если сходить по ссылкам и взять больше имён/фамилий, то можно и больше нагенерировать.

with surnames as (
    -- https://www.al.com/news/2019/10/50-most-common-last-names-in-america.html
    select * from
    unnest(
        '{"Smith","Johnson","Williams","Brown","Jones","Garcia","Miller","Davis",
        "Rodriguez","Martinez","Hernandez","Lopez","Gonzalez",
        "Wilson","Anderson","Thomas","Taylor","Moore","Jackson","Martin","Lee",
        "Perez","Thompson","White","Harris","Sanchez","Clark",
        "Ramirez","Lewis","Robinson","Walker","Young","Allen","King",
        "Wright","Scott","Torres","Nguyen","Hill","Flores","Green","Adams",
        "Nelson","Baker","Hall","Rivera","Campbell","Mitchell","Carter","Roberts"}'::text[]
    ) surname
), 
women_names as (
    -- https://www.whattoexpect.com/baby-names/list/top-baby-names-for-girls/
    select * from
    unnest(
        '{"Olivia","Emma","Charlotte","Amelia","Ava","Sophia","Isabella","Mia","Evelyn",
        "Harper","Luna","Camila","Gianna","Elizabeth",
         "Eleanor","Ella","Abigail","Sofia","Avery","Scarlett","Emily","Aria","Penelope",
         "Chloe","Layla","Mila","Nora","Hazel","Madison","Ellie","
         Lily","Nova","Isla","Grace","Violet","Aurora","Riley","Zoey","Willow","Emilia",
         "Stella","Zoe","Victoria","Hannah","Addison",
         "Leah","Lucy","Eliana","Ivy","Everly"}'::text[]
    ) forename
),
men_names as (
    -- https://www.whattoexpect.com/baby-names/list/top-baby-names-for-boys/
    select * from
    unnest(
        '{"Liam","Noah","Oliver","Elijah","James","William","Benjamin","Lucas","Henry",
        "Theodore","Jack","Levi","Alexander","Jackson",
         "Mateo","Daniel","Michael","Mason","Sebastian","Ethan","Logan","Owen","Samuel",
         "Jacob","Asher","Aiden","John","Joseph","Wyatt",
         "David","Leo","Luke","Julian","Hudson","Grayson","Matthew","Ezra","Gabriel",
         "Carter","Isaac","Jayden","Luca","Anthony",
         "Dylan","Lincoln","Thomas","Maverick","Elias","Josiah","Charles"}' ::text[]
    ) forename
),
men as (
  select
    fn.forename as firstName,
    mn.forename as middleName,
    surname,
    row_number() over (order by md5(concat(fn.forename, mn.forename, surname)::bytea)) rn
  from men_names fn 
  inner join men_names mn on mn.forename != fn.forename 
  cross join surnames
),
women as (
  select
    fn.forename as firstName,
    mn.forename as middleName,
    surname,
    row_number() over (order by md5(concat(fn.forename, mn.forename, surname)::bytea)) rn
  from women_names fn 
  inner join women_names mn on mn.forename != fn.forename 
  cross join surnames
)
, people as (
  select rn as parentId, firstName, middleName, surname from men where rn <= 2500 
  union all 
  select rn+2500, firstName, middleName, surname from women where rn <= 2500 
) 
insert into parents (parent_id, first_name, middle_name, last_name) 
select * from people;

С русскими чуть-чуть посложней, потому что и отчества и фамилии зависят от пола

with surnames as (
    select 'Иванов' male, 'Иванова' female
    union select 'Соколовский', 'Соколовская'
    union select 'Окуленко', 'Окуленко'
    -- и ещё 50 своих знакомых
),
women_names as (
    select * from
    unnest(
        -- и ещё 50 своих знакомых
        '{"Екатерина","Юлия"}'::text[]
    ) forename
),
men_names as (
    select 'Иван' forename, 'Иванович' male_patronymic, 'Ивановна' female_patronymic
    union select 'Илья', 'Ильич', 'Ильинишна'
    union select 'Геннадий', 'Геннадиевич', 'Геннадиевна'
    -- и ещё 50 своих знакомых
),
men as (
  select
    fn.forename as firstName,
    pn.male_patronymic as middleName,
    surnames.male as surname,
    row_number() over (order by md5(concat(fn.forename, pn.male_patronymic, surnames.male)::bytea)) rn
  from men_names fn 
  cross join men_names pn
  cross join surnames
),
women as (
  select
    fn.forename as firstName,
    pn.female_patronymic as middleName,
    surnames.female as surname,
    row_number() over (order by md5(concat(fn.forename, pn.female_patronymic, surnames.female)::bytea)) rn
  from women_names fn
  cross join men_names pn
  cross join surnames
)
, people as (
    select rn as parentId, firstName, middleName, surname from men where rn <= 2500
    union all
    select rn+2500, firstName, middleName, surname from women where rn <= 2500
)
insert into parents (parent_id, first_name, middle_name, last_name)
select * from people;

БЛОГ НА HUSL

Как реализовать заполнение таблицы тестовыми данными

Ответы (1 шт):