Как добавить данные в БД только если БД пустая?

INSERT ON CONFLICT DO NOTHING, как вариант.

Смотри в сторону merge

(внезапно) create table if not exists xxx as select ….

INSERT ON CONFLICT DO NOTHING

Я думал про это, но непонятно по какому полю конфликт отслеживать. В примерах обычно написано по уникальному ID, но у меня есть таблицы, в которых даже ID нет.

В примере, очевидно, что это поле name.

create table if not exists xxx as select

А содержимое select откуда брать? Пока только могу придумать что из временной таблицы. Я правильно понимаю?

Но name как поле в структуре неуникально, на нем CONFLICT все равно сработает?

Нет.

поле в структуре неуникально

Сделать уникальным, разве могут быть две одинаковые роли?

Сделать уникальным, разве могут быть две одинаковые роли?

Структура не моя.

Структура не моя.

Тогда, принудительный TRUNCATE roles; перед вставкой. Или вообще сначала дропнуть таблицу, потом уже создавать ))

И что-то я не понимаю как задать уникальность для поля. Надо создавать уникальный индекс?

Как-то так

CREATE TABLE roles (
    id INT PRIMARY KEY,
    name TEXT UNIQUE
);

\d+ roles
                                          Table "public.roles"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer |           | not null |         | plain    |             |              | 
 name   | text    |           |          |         | extended |             |              | 
Indexes:
    "roles_pkey" PRIMARY KEY, btree (id)
    "roles_name_key" UNIQUE CONSTRAINT, btree (name)

Нет, ты мне что-то не то насоватовал.

Задача была - сделать INSERT если база данных пуста. Вместо этого ты говоришь проверять записи на уникальность. База же может быть отредактирована, вместо security можно прописать superuser. И получается что при повторном срабатывании скрипта в базе окажется и security и superuser. А нам этого не нужно.

https://stackoverflow.com/questions/5307164/execute-insert-if-table-is-empty попробуй может сработаети в постгрес

Я не понимаю этого синтаксиса. Надо вначале сделать временную таблицу, о которой не говорят?

Insert into blah (pew, pew) select value, value where (select count(*) from blah) = 0;

Категорически советую не пихать такую логику в БД. Пиши на нормальном языке транзакцию с локом таблицы. Если таблица пустая — вставить, закоммитить. Это же можно написать и как sql процедуру, хотя, если очень надо.

Я не понимаю этого синтаксиса

INSERT INTO roles (id, name)
    SELECT * FROM (
        VALUES (1, 'admin'), (2, 'user')    
    ) AS t (id, name)
    WHERE NOT EXISTS (SELECT * FROM roles);

INSERT INTO roles (id, name)
SELECT * FROM (
VALUES (1, 'admin'), (2, 'user')
) AS t (id, name)
WHERE NOT EXISTS (SELECT * FROM roles);

Но здесь же нет типов полей.

В общем, я решил задачу так:

create temp table temp_roles (
 id serial primary key,
 name varchar(20)
); 

insert into temp_roles (name) values
 ('user'),
 ('security'),
 ('admin');

create table if not exists roles as table temp_roles;

Но здесь же нет типов полей.

Ты же типы задаешь при создании таблицы

есть таблицы, в которых даже ID нет

Напрасно, сам себе создаёшь геморрой.

сделать INSERT если база данных пуста. Вместо этого ты говоришь проверять записи на уникальность

Всё правильно у тебя типичная x/y проблема. Тебе не надо чтобы таблица была пуста, тебе надо чтобы у тебя не было дублей ролей и вставка была идемпотентной (повторяешь сколько угодно раз, результат одинаковый). Логично, что для этого нужен уникальный ключ.

А если дело не в ролях, а тебе просто в принципе нужно засидить данные один раз, то тут могут быть разные варианты. Если речь про постгрес, то можешь просто завернуть создание таблицы и добавление данных в одну транзакцию. Если таблица уже создана, то первый запрос зафейлится и с ним вся транзакция.

Всё правильно у тебя типичная x/y проблема. Тебе не надо чтобы таблица была пуста, тебе надо чтобы у тебя не было дублей ролей и вставка была идемпотентной (повторяешь сколько угодно раз, результат одинаковый). Логично, что для этого нужен уникальный ключ.

Я уже написал: Как добавить данные в БД только если БД пустая? (комментарий).

То есть, нужно чтобы в таблицах были либо заранее заданные данные, либо какие-то другие. Смешивания данных не нужно.

Ты же типы задаешь при создании таблицы

Просто гуру об этом не говорят, а дают куски кода без пояснения как их использовать.

Просто гуру об этом не говорят, а дают куски кода без пояснения как их использовать.

Куски кода с пояснениями стоят от 1000 руб./час.

У постгреса, между прочем, отличная документация.

Можно так, но типы и тд указывать не получится.

create table if not exists person (last_name, first_name)
as values ('Ivanov', 'Ivan'), ('Petrov', 'Petr')

А вообще я бы сделал на plpgsql и не заморачивал бы себе голову.

create table if not exists roles (id serial primary key, name varchar(20)); 

do $$begin
  if not exists (select from roles) then
    insert into roles (name) values ('user'), ('security'), ('admin');
  end if;
end$$;

как-то так.

Тогда, принудительный TRUNCATE roles; перед вставкой. Или вообще сначала дропнуть таблицу, потом уже создавать ))

// Проверка наличия базы данных:
if (mysql_query('DROP DATABASE `xxx`;')) echo 'База существовала';

(c) допотопный боян

я бы сделал на plpgsql

+1.

В общем, я решил задачу так

Так у тебя primary key потерялся.

При копировании структуры таблицы теряется Primary Key?

Похоже, что для того, чтобы индексы и всякие ограничения не терялись, надо вместо:

create table if not exists roles as table temp_roles;

CREATE TABLE IF NOT EXISTS roles (LIKE temp_roles INCLUDING ALL);

индексы будут «шариться» между таблицами

Не, postgres так не умеет :-)

sequence так будет шарится (serial который), да, и после дисконнекта ты потеряешь default у id, но не индекс.

А чем тебе вариант vvn_black не нравится? Он же тебе написал всё:

create table if not exists roles (
 id serial primary key,
 name varchar(20)
);


insert into roles (name) select * from (values
 ('user'),
 ('security'),
 ('admin')
) _
where not exists (select * from roles);

А чем тебе вариант vvn_black не нравится? Он же тебе написал всё

Я не до конца понимаю эту конструкцию, а точнее where not exists (select * from roles);.

Она удовлетворяет вот этому: Как добавить данные в БД только если БД пустая? (комментарий) ?

Смешивания данных не будет?

where not exists (select * from roles)

not exists — это означает «нет ни одной строчки в результате выполнения подзапроса select * from roles»

Смешивания данных не будет?

нет не будет, можно в explain (analyze) посмотреть как он работает:

explain (analyze,verbose) insert into roles (name) select * from (values
 ('user'),
 ('security'),
 ('admin')
) _
where not exists (select * from roles);

   InitPlan 1 (returns $0)
     ->  Seq Scan on roles roles_1  (cost=0.00..19.00 rows=900 width=0) (actual time=0.009..0.009 rows=0 loops=1)

он получил вывод select * from roles и сохранил в $0

         One-Time Filter: (NOT $0)
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=32) (actual time=0.003..0.005 rows=3 loops=1)

дальше посмотрел что $0 пустой (NOT $0 = true) и вернул все 3 строки из VALUES (rows=3) в INSERT

а это когда уже есть данные:

   InitPlan 1 (returns $0)
     ->  Seq Scan on roles roles_1  (cost=0.00..19.00 rows=900 width=0) (actual time=0.010..0.010 rows=1 loops=1)

получил одну строчку, понял что exists уже выполняется и дальше не смотрел

         One-Time Filter: (NOT $0)
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=32) (never executed)

never executed — не стал выполнять VALUES и соответствено INSERT

есть такое слово миграции

Миграции в фреймверках - да.

Миграции в postinst deb-пакета - это нужно быть большим оригиналом.

можно в explain (analyze) посмотреть как он работает:

Благодарю.

Похоже, что для того, чтобы индексы и всякие ограничения не терялись, надо вместо:
create table if not exists roles as table temp_roles;
Сделать так:
CREATE TABLE IF NOT EXISTS roles (LIKE temp_roles INCLUDING ALL);

Нет, так тоже нельзя.

Первый вариант скопирует структуру и данные, но превратит индексные поля в обычные.

Второй вариант скопирует структуру и индексы, но не скопирует данные.

В общем, в итоге вот так получилось правильно:

-- Создается таблица если она еще не существует
CREATE TABLE IF NOT EXISTS roles (
    id SERIAL PRIMARY KEY,   
    name VARCHAR(20)               
);

-- Таблица наполняется только если она только что была создна (и поэтому пустая)
-- Конструкция WHERE NOT EXISTS (SELECT * FROM roles) разрешит выполнение только 
-- если в таблице roles нет ни одной записи
-- Наполняемые данные VALUES для вставки в FROM обязательно должы
-- иметь алиас, и он задается через AS
INSERT INTO roles (name) SELECT * FROM ( VALUES
    ('user'),
    ('security'),
    ('admin') ) AS temp_roles
    WHERE NOT EXISTS (SELECT * FROM roles);