Как добавлять данные в sql

Как вставлять данные в базы SQL

Операция вставки данных в реляционных БД — одна из наиболее востребованных, без которой невозможна нормальная работа с таблицами. В статье расскажем, как это делать средствами реляционных СУБД, работающих с языком SQL. Возьмем MySQL, наиболее популярную СУБД из тех, которые работают с SQL.

В качестве примера здесь и далее будем рассматривать две таблицы: ведущих европейских футбольных клубов (с указанием их национальной принадлежности, года основания, количества выигранных чемпионатов, кубков страны и европейских трофеев) и ряда ведущих стран мира (со столицами и населением в млн человек). Изначально таблицы (назовем их Clubs и Countries ) будут пустыми: заполнены будут только названия столбцов. Наша задача — наполнить их при помощи различных инструкций, чтобы они выглядели примерно так:

Итак, сначала нам понадобится создать базу данных и две таблицы. Делается это следующим образом. Подключаемся к серверу MySQL (вместо xxx.xxx.xxx.xxx введите нужный IP-адрес):

mysql -u root -h xxx.xxx.xxx.xxx -p

CREATE DATABASE TestDB;

И проверьте, что новая база данных создана успешно, инструкцией:

show databases;

Наша база данных TestDB должна быть в списке. Далее нам нужно выдать доступы к ней пользователям. Допустим, у нас уже есть пользователь test_user , выдадим ему доступ:

GRANT ALL PRIVILEGES ON TestDB.* TO 'test_user'@'%' WITH GRANT OPTION;

Теперь можно приступать к созданию таблиц. Сделаем это сначала для клубов:

CREATE TABLE Clubs 
( 
Club VARCHAR(64) NOT NULL, 
Country VARCHAR(32), 
Year INT, 
Champs INT, 
Cups INT, 
Eurocups INT
);

Это значит, что для первых двух столбцов мы задали строковые значения, причем длина данных в каждой ячейке не может превышать 64 и 32 символа соответственно, а ячейки в столбце Club при добавлении данных не могут быть пустыми ( NOT NULL ). Для оставшихся 4 столбцов мы предусмотрели целочисленные значения ( INT ). Теперь по тому же образцу создаем вторую таблицу:

CREATE TABLE Countries 
( 
Country VARCHAR(32) NOT NULL, 
Capital VARCHAR(32), 
Population INT
);

Вот и всё, наши таблицы созданы, можно приступать к заполнению.

Инструкция INSERT INTO

Инструкция INSERT INTO позволяет вставить данные в таблицу SQL , но они вставляются по порядку, поэтому вы должны знать, в каком порядке идут столбцы в таблице. Вставим данные в первые строчки наших таблиц:

INSERT INTO Clubs VALUES("Real Madrid", "Spain", 1902, 35, 19, 21);

INSERT INTO Countries VALUES("Russia", "Moscow", 147);

Здесь следует обратить внимание, что должны быть указаны значения абсолютно всех столбцов, поэтому, например, такие записи уже будут ошибочными, и значения будут перепутаны (мы не указали год основания клуба и столицу государства):

INSERT INTO Clubs VALUES("Real Madrid", "Spain", 35, 19, 21);

INSERT INTO Countries VALUES("Russia", 147);

Инструкция INSERT INTO со списком столбцов

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

INSERT INTO Clubs(Club, Country, Champs, Cups, Eurocups) VALUES("Barcelona", "Spain", 26, 31, 18);

INSERT INTO Countries(Country, Capital) VALUES("USA", "Washington");

Мы пропустили год основания клуба в первом случае (столбец Year ) и населения во втором (столбец Population ), но никакой ошибки не будет, эти поля просто останутся пустыми. И их несложно будет заполнить позже.

Инструкция INSERT INTO для одновременной вставки

Добавлять данные построчно не всегда удобно. Поэтому разберемся, как вставить данные сразу в несколько столбцов SQL . Это делает следующая инструкция:

INSERT INTO Clubs(Club, Country, Eurocups)
VALUES
("Real Madrid", "Spain", 21),
("Barcelona", "Spain", 18),
("Milan", "Italy", 14),
("Juventus", "Italy", 8),
("Bavaria", "Germany", 10);

INSERT INTO Countries(Country, Population)
VALUES
("Russia", 147),
("USA", 336),
("China", 1427),
("India", 1435),
("Brazil", 218);

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

Инструкция SET

В сочетании с INSERT INTO инструкция SET позволяет вставить в таблицу одну запись:

INSERT INTO Clubs SET
Club="Milan",
Country="Italy",
Year=1899,
Champs=19,
Cups=5,
Eurocups=14;

INSERT INTO Countries SET
Country="China",
Capital="Beijing",
Population=1427;

Но в этом и ее недостаток, так как таким образом невозможно вставить данные нескольких записей в таблицу SQL : для этого подойдет предыдущая инструкция.

Как вставить данные из другой таблицы SQL

Допустим у нас есть другие таблицы с теми же столбцами, и мы хотим дополнить наши. В этом нам поможет инструкция SELECT в сочетании с уже знакомой INSERT :

INSERT INTO Clubs(Club, Country, Year, Champs, Cups, Eurocups)
SELECT Club, Country, Year, Champs, Cups, Eurocups
FROM Clubs2;

INSERT INTO Countries(Country, Capital, Population)
SELECT Country, Capital, Population
FROM Countries2;

Инструкция IGNORE для избегания ошибок

Чтобы MySQL не прекращал работу при попытке добавления ошибочных значений, используется инструкция IGNORE . Например, мы установили ограничение уникальности для столбца Club с тем, чтобы каждое название клуба было уникальным. Для нашей таблицы добавлять такое ограничение вручную не потребуется, так как в MySQL первое значение в таблице всегда должно быть уникальным. И если у нас уже есть такая строка:

Следующая инструкция вызовет ошибку:

INSERT INTO Clubs VALUES("Real Madrid", "Spain", 1902, 35, 19, 21);

У нас уже есть строка со значением Real Madrid в первом столбце. Но когда мы копируем данные из нескольких таблиц, без дублей скорее всего не обойдется. И чтобы программа игнорировала их и не выдавала при этом ошибки, мы добавляем инструкцию IGNORE :

INSERT IGNORE Clubs(Club, Country, Year, Champs, Cups, Eurocups)
VALUES("Real Madrid", "Spain", 1902, 35, 19, 21);

То же самое со вторым примером. У нас уже есть строка:

INSERT INTO Countries VALUES("Russia", "Moscow", 147);

— выдаст нам ошибку. Поэтому и здесь воспользуемся инструкцией IGNORE :

INSERT IGNORE Countries(Country, Capital, Population)
VALUES("Russia", "Moscow", 147);

Программа просто проигнорирует эту строку и продолжит выполнение без выдачи ошибки.

Инструкция LOAD DATA для загрузки из текстового файла

Допустим, у нас уже создана пустая таблица Clubs с соответствующими столбцами, и нам нужно ее наполнить информацией из текстового файла. Инструкция LOAD DATA позволит сделать это, однако предварительно понадобится подготовить текстовые данные в файле. Открываем наш файл (допустим, Clubs.txt ) текстовым редактором и форматируем данные следующим образом, разделяя значения символом табуляции:

'Real Madrid' 'Spain' '1902' '35' '19' '21'
'Barcelona' 'Spain' '1899' '26' '31' '18' 
'Milan' 'Italy' '1899' '19' '5' '14'
'Juventus' 'Italy' '1897' '36' '14' '8'
'Bavaria' 'Germany' '1900' '32' '20' '10'

Такой SQL-запрос вставит данные в таблицу , расположив их по корректным столбцам. А что если у нас нет значений для конкретных столбцов? Допустим, мы не знаем годы основания клубов. В этом случае запись будет такой:

'Real Madrid' 'Spain' '\N' '35' '19' '21'
'Barcelona' 'Spain' '\N' '26' '31' '18' 
'Milan' 'Italy' '\N' '19' '5' '14'
'Juventus' 'Italy' '\N' '36' '14' '8'
'Bavaria' 'Germany' '\N' '32' '20' '10'

‘\N’ означает, что эта ячейка в таблице останется пустой. Теперь осталось только загрузить данные в SQL, но сначала включим работу с локальными файлами так:

set global local_infile=true;
exit
mysql --local_infile=1 -u test_user -h xxx.xxx.xxx.xxx -p

Загружаем данные, что в Linux сделает команда:

LOAD DATA LOCAL INFILE '/your_directory/Clubs.txt' INTO TABLE Clubs;

А в Windows это делается так:

LOAD DATA LOCAL INFILE '/your_directory/Clubs.txt'' INTO TABLE Clubs LINES TERMINATED BY '\r\n';

Но бывает так, что система не реагирует на эти инструкции: в этом случае вам потребуется включить работу с локальными файлами в MySQL, что подробно описано в официальном руководстве . Если кратко, то значение unsigned int в опции MYSQL_OPT_LOCAL_INFILE , относящейся к настройкам mysql_options() , должно быть ненулевым.

А у нас на этом всё: теперь вы знаете, как вставлять данные в базы SQL при помощи нескольких инструкций. Просто выбирайте наиболее подходящую инструкцию для конкретного случая, и вы избежите проблем при копировании.

Добавление записи в две связанные таблицы

Объясните пожалуйста, как добавить запись в SQL БД в две таблицы со связью «один-к-одному»? Очевидный вариант — добавить INSERT ‘ом в одну таблицу, получить SELECT ‘ом primary key , добавить INSERT ‘ом в другую. Но он выглядит, мягко говоря, не очень оптимально — аж три запроса. А если добавятся ещё связанные таблицы.

Отслеживать
1,695 2 2 золотых знака 23 23 серебряных знака 47 47 бронзовых знаков
задан 12 июл 2013 в 11:01
381 1 1 золотой знак 3 3 серебряных знака 14 14 бронзовых знаков

Не уверен, как в других СуБД, но в postgreSQL, например, имеется ключевое слово RETURNING, которое избавит вас от промежуточного SELECT’a. А INSERT’ов должно быть столько, сколько и таблиц.

12 июл 2013 в 11:10
У меня MariaDB, если что.
12 июл 2013 в 11:30

6 ответов 6

Сортировка: Сброс на вариант по умолчанию

INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); # use ID in second table

Отслеживать
ответ дан 4 авг 2016 в 16:08
1,826 3 3 золотых знака 30 30 серебряных знаков 63 63 бронзовых знака
Это только для MySQL!
22 сен 2020 в 5:48

INSERT может вставить данные только в одну таблицу.

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

Отслеживать
ответ дан 12 июл 2013 в 12:33
11.5k 16 16 серебряных знаков 16 16 бронзовых знаков
Извините, а что такое «хп». :\
12 июл 2013 в 18:00
Хранимая процедура.
12 июл 2013 в 18:09
Хранимая Процедура
12 июл 2013 в 18:09

И в чем при использовании ХП будет принципиальное отличие? Просто второй insert с select-ом переместится в серверную часть. Может лучше все же связывать таблицы не по автогенерируемому ключу, а по обычным, поступающим извне данным (общим столбцам в обеих таблицах)?

12 июл 2013 в 18:24

@msi, насчет параметров — это и ежу понятно. По сути ХП просто переносят часть логики обработки данных и непосредственной вычислительной работы в серверную часть. Тут можно выиграть на передаче данных (прежде всего уменьшается латентность), но проиграть как в усложнении общего кода (клиент + SQL), так и в скорости реакции сервера в случае разнообразного потока одновременных запросов. — Но в данном случае мы просто не знаем всех нюансов задачи.

Упрощение управления базой данных SQL с помощью Astera Centerprise

SQL (язык структурированных запросов) — популярный язык запросов к базам данных. Он используется для создания, управления и извлечения данных, хранящихся в реляционном хранилище. система управления базами данных (СУБД), например Microsoft SQL Server. Как транзакционные, так и основные данные хранятся в базе данных SQL, откуда они извлекаются для анализа и составления отчетов.

Управление базой данных SQL можно описать как процесс доступа и управления транзакционными и основными данными. Однако по мере роста размера бизнеса растут и сложность и объем его данных, что делает управление базами данных SQL сложной и трудоемкой задачей для ИТ-команд.

В этом сообщении блога мы рассмотрим ключевые проблемы управления базами данных SQL и то, как Astera Centerprise может помочь вам преодолеть их.

Каковы проблемы управления базами данных SQL?

Вот наиболее распространенные проблемы управления базами данных SQL, с которыми сталкиваются предприятия:

Агрегирование и преобразование данных: Часто предприятиям необходимо агрегировать и трансформировать данные, хранящиеся в базах данных SQL для целей отчетности. Это может быть просто, если у вас есть 1–2 таблицы с небольшим объемом данных. Однако при больших объемах данных агрегирование и преобразование на языке SQL может стать проблемой по двум причинам: во-первых, написание сложных запросов и хранимых процедур, включающих множественные внешние соединения между таблицами и расширенные команды SQL, может оказаться затруднительным. Во-вторых, плохо написанный запрос может привести к проблемам с производительностью (из-за длительного времени выполнения запроса).
Различия в синтаксисе SQL: Каждый инструмент базы данных имеет свой синтаксис и команды SQL, поэтому общий запрос может работать не на всех системах. Это также может вызвать проблемы, если у вас есть хранимые процедуры для доступа к информации или выполнения действий с несколькими базами данных, поскольку хранимые процедуры несовместимы между технологиями. Например, хранимая процедура, написанная на языке программирования T-SQL MS SQL Server, не будет работать в MySQL. В конечном итоге это означает, что вам придется писать разные запросы и хранимые процедуры для доступа к данным и управления ими для каждого из них. технология баз данных что у вас есть, что может оказаться трудоемкой и подверженной ошибкам задачей.
Загрузка данных из внешних источников: Вы можете добавлять данные в базу данных SQL из нескольких источников, таких как форма, связанная система или приложение или внешние источники. Чтобы загрузить данные из этих внешних источников в соответствующий столбец, вашей ИТ-команде придется написать код SQL и отсортировать данные в таблицу базы данных. Часто этот уровень «интеграции» также включает в себя несколько преобразований, таких как преобразование типа загружаемых данных для обеспечения их совместимости со схемой базы данных. Это опять же может быть ошибочной и трудоемкой задачей, требующей регулярного обслуживания и обслуживания (поскольку вам необходимо учитывать любые незначительные изменения в загружаемых данных каждый раз).

Преодоление проблем управления базами данных SQL с помощью Astera Centerprise

Эффективный метод преодоления проблем, которые мы описали ранее, — это использование программного обеспечения для интеграции корпоративных данных, такого как Astera Centerprise. Такие инструменты созданы для того, чтобы помочь вам выполнять рутинные задачи, такие как агрегирование данных, хранящихся в базе данных SQL, или загрузка данных в базу данных SQL без написания какого-либо кода. Centerprise поставляется с широким набором готовых соединителей, преобразований и агрегаций, которые вы можете использовать для быстрого и легкого анализа и извлечения значимой информации из базы данных SQL.

Хорошая часть заключается в том, что весь инструмент не зависит от языка программирования. Таким образом, независимо от того, какую систему управления базами данных вы используете, можно использовать одни и те же агрегаты без необходимости их изменения (в отличие от SQL, где каждая СУБД имеет свой синтаксис).

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

Для иллюстрации приведем пример того, как можно выполнить некоторые сложные задачи управления базами данных с помощью Astera Centerprise:

Загрузка данных в базу данных SQL:

Добавьте соединитель для источника. Это может быть файл, например текстовый файл CSV, Excel или JSON. Вы даже можете напрямую подключиться к облачным поставщикам данных, таким как Salesforce.
Примените соответствующие преобразования к загружаемым данным, чтобы они соответствовали данным в вашей базе данных.
Добавьте коннектор назначения для назначения SQL Server и определите параметры соединения.

Настройка коннектора назначения базы данных SQL Server

4. Сопоставьте поля данных из исходного файла с базой данных SQL. Удобный интерфейс Astera Centerprise позволяет перетаскивать сопоставления из каждого столбца исходных данных в соответствующий столбец в вашей базе данных. Таким образом, каждую строку данных можно перемещать без необходимости переименовывать исходный или целевой столбец.

Поток данных, показывающий интеграцию данных о клиентах из двух разных источников Salesforce и загрузку в SQL Server.

Агрегация и преобразование данных из базы данных SQL:

1. Добавьте исходный соединитель для источника SQL Server и определите параметры подключения.
Настройка подключения к источнику SQL Server

Поток данных, который фильтрует данные о клиентах, присутствующие в базе данных SQL Server.

Это всего лишь два примера того, как вы можете использовать Astera Centerprise упростить управление базами данных SQL и решить некоторые из основных проблем. С помощью этого инструмента вы также можете выполнять множество других задач.

Чтобы узнать больше о Astera Centerprise и как это может помочь вам с управлением базой данных SQL, пожалуйста. Связаться с нашей командой.

Другие статьи по теме
- CRUD API: преимущества и методы обработки данных
- Создайте гибкое хранилище данных с помощью итеративного подхода
- Вызовы API и как они работают? Полное руководство
- Полное руководство по корпоративному хранилищу данных
Как добавить данные в БД только если БД пустая?

Вопрос: как можно доработать эту команду, чтобы добавление произошло только если в таблице ничего нет? Видимо, можно получить COUNT() на SELECT, но как сравнить значение COUNT() с нулем, и если ноль — то выполнить команду? Возможно ли такое сделать только лишь синтаксисом SQL?

Xintrea ★★★★★
24.10.22 12:31:06 MSK
- Ответить на это сообщение
- Ссылка
INSERT ON CONFLICT DO NOTHING , как вариант.

vvn_black ★★★★★
( 24.10.22 12:41:35 MSK )
Последнее исправление: vvn_black 24.10.22 12:42:09 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Показать ответ
- Ссылка
Смотри в сторону merge

crutch_master ★★★★★
( 24.10.22 12:43:20 MSK )
- Ответить на это сообщение
- Ссылка
(внезапно) create table if not exists xxx as select ….

MKuznetsov ★★★★★
( 24.10.22 12:52:18 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от vvn_black 24.10.22 12:41:35 MSK

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

Xintrea ★★★★★
( 24.10.22 13:19:57 MSK ) автор топика
- Ответить на это сообщение
- Показать ответы
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 13:19:57 MSK

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

vvn_black ★★★★★
( 24.10.22 13:20:45 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от MKuznetsov 24.10.22 12:52:18 MSK

create table if not exists xxx as select

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

Xintrea ★★★★★
( 24.10.22 13:21:17 MSK ) автор топика
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от vvn_black 24.10.22 13:20:45 MSK

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

Xintrea ★★★★★
( 24.10.22 13:22:37 MSK ) автор топика
- Ответить на это сообщение
- Показать ответы
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 13:22:37 MSK

theNamelessOne ★★★★★
( 24.10.22 13:22:57 MSK )
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 13:22:37 MSK

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

vvn_black ★★★★★
( 24.10.22 13:23:58 MSK )
- Ответить на это сообщение
- Показать ответы
- Ссылка
Ответ на: комментарий от vvn_black 24.10.22 13:23:58 MSK

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

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

Xintrea ★★★★★
( 24.10.22 13:24:34 MSK ) автор топика
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 13:24:34 MSK

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

vvn_black ★★★★★
( 24.10.22 13:25:41 MSK )
Последнее исправление: vvn_black 24.10.22 13:28:04 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от vvn_black 24.10.22 13:23:58 MSK

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

Xintrea ★★★★★
( 24.10.22 13:27:43 MSK ) автор топика
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 13:27:43 MSK
```
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) 
```
vvn_black ★★★★★
( 24.10.22 13:34:32 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от vvn_black 24.10.22 13:34:32 MSK

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

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

Xintrea ★★★★★
( 24.10.22 13:40:44 MSK ) автор топика
- Ответить на это сообщение
- Показать ответы
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 13:40:44 MSK

vvn_black ★★★★★
( 24.10.22 13:44:38 MSK )
Последнее исправление: vvn_black 24.10.22 13:45:23 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от vvn_black 24.10.22 13:44:38 MSK

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

Xintrea ★★★★★
( 24.10.22 13:47:50 MSK ) автор топика
- Ответить на это сообщение
- Показать ответ
- Ссылка
Insert into blah (pew, pew) select value, value where (select count(*) from blah) = 0;

thegoldone ★
( 24.10.22 13:55:10 MSK )
- Ответить на это сообщение
- Ссылка
Категорически советую не пихать такую логику в БД. Пиши на нормальном языке транзакцию с локом таблицы. Если таблица пустая — вставить, закоммитить. Это же можно написать и как sql процедуру, хотя, если очень надо.

filosofia ☆
( 24.10.22 14:04:00 MSK )
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 13:47:50 MSK

Я не понимаю этого синтаксиса
```
INSERT INTO roles (id, name) SELECT * FROM ( VALUES (1, 'admin'), (2, 'user') ) AS t (id, name) WHERE NOT EXISTS (SELECT * FROM roles); 
```
vvn_black ★★★★★
( 24.10.22 14:15:49 MSK )
Последнее исправление: vvn_black 24.10.22 14:16:51 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от vvn_black 24.10.22 14:15:49 MSK

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; 
```
Xintrea ★★★★★
( 24.10.22 14:39:23 MSK ) автор топика
- Ответить на это сообщение
- Показать ответы
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 14:39:23 MSK

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

goingUp ★★★★★
( 24.10.22 14:44:53 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 13:19:57 MSK

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

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

no-such-file ★★★★★
( 24.10.22 15:09:34 MSK )
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 13:40:44 MSK

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

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

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

no-such-file ★★★★★
( 24.10.22 15:16:44 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от no-such-file 24.10.22 15:16:44 MSK

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

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

Xintrea ★★★★★
( 24.10.22 15:35:22 MSK ) автор топика
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от goingUp 24.10.22 14:44:53 MSK

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

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

Xintrea ★★★★★
( 24.10.22 16:03:57 MSK ) автор топика
- Ответить на это сообщение
- Показать ответы
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 16:03:57 MSK

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

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

vvn_black ★★★★★
( 24.10.22 16:51:52 MSK )
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 16:03:57 MSK

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

theNamelessOne ★★★★★
( 24.10.22 19:00:14 MSK )
- Ответить на это сообщение
- Ссылка
Можно так, но типы и тд указывать не получится.
```
create table if not exists person (last_name, first_name) as values ('Ivanov', 'Ivan'), ('Petrov', 'Petr') 
```
vbr ★★★
( 24.10.22 19:25:41 MSK )
- Ответить на это сообщение
- Ссылка
А вообще я бы сделал на 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$$; 
```
vbr ★★★
( 24.10.22 19:32:27 MSK )
Последнее исправление: vbr 24.10.22 19:33:49 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от vvn_black 24.10.22 13:25:41 MSK

Тогда, принудительный TRUNCATE roles; перед вставкой. Или вообще сначала дропнуть таблицу, потом уже создавать ))
```
// Проверка наличия базы данных: if (mysql_query('DROP DATABASE `xxx`;')) echo 'База существовала'; 
```
(c) допотопный боян

pr849 ★
( 24.10.22 23:19:17 MSK )
Последнее исправление: pr849 24.10.22 23:20:43 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от vbr 24.10.22 19:32:27 MSK

pr849 ★
( 24.10.22 23:20:07 MSK )
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 14:39:23 MSK

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

Eshkin_kot ★★
( 25.10.22 00:46:56 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от Eshkin_kot 25.10.22 00:46:56 MSK

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

Xintrea ★★★★★
( 25.10.22 08:47:42 MSK ) автор топика
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Xintrea 24.10.22 14:39:23 MSK

Похоже, что для того, чтобы индексы и всякие ограничения не терялись, надо вместо:
```
create table if not exists roles as table temp_roles; 
```
Сделать так:
```
CREATE TABLE IF NOT EXISTS roles (LIKE temp_roles INCLUDING ALL); 
```
Народ еще говорит, то таким образом индексы будут «шариться» между таблицами. Но ежели копирование идет с временной таблицы, то по идее индекс расшарен не будет?

Xintrea ★★★★★
( 25.10.22 09:43:08 MSK ) автор топика
- Ответить на это сообщение
- Показать ответы
- Ссылка
Ответ на: комментарий от Xintrea 25.10.22 09:43:08 MSK

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

Не, 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); 
```
Eshkin_kot ★★
( 25.10.22 13:21:41 MSK )
- Ответить на это сообщение
- Показать ответы
- Ссылка
Ответ на: комментарий от Eshkin_kot 25.10.22 13:21:41 MSK

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

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

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

Xintrea ★★★★★
( 25.10.22 13:27:43 MSK ) автор топика
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от Xintrea 25.10.22 13:27:43 MSK

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

Eshkin_kot ★★
( 25.10.22 13:38:30 MSK )
Последнее исправление: Eshkin_kot 25.10.22 13:40:02 MSK (всего исправлений: 1)
- Ответить на это сообщение
- Показать ответ
- Ссылка
есть такое слово миграции

lovesan ★★
( 25.10.22 15:11:49 MSK )
- Ответить на это сообщение
- Показать ответ
- Ссылка
Ответ на: комментарий от lovesan 25.10.22 15:11:49 MSK

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

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

Xintrea ★★★★★
( 25.10.22 16:14:22 MSK ) автор топика
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Eshkin_kot 25.10.22 13:38:30 MSK

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

Xintrea ★★★★★
( 25.10.22 16:14:50 MSK ) автор топика
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Xintrea 25.10.22 09:43:08 MSK

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

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

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

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

Xintrea ★★★★★
( 26.10.22 15:05:53 MSK ) автор топика
- Ответить на это сообщение
- Ссылка
Ответ на: комментарий от Eshkin_kot 25.10.22 13:21:41 MSK

В общем, в итоге вот так получилось правильно:
```
-- Создается таблица если она еще не существует 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); 
```
Если алиас существует только в пределах одной команды, то, видимо, можно не придумывать уникальное имя, а писать AS t. Но пока с этим не разобрался.

Xintrea ★★★★★
( 26.10.22 15:40:21 MSK ) автор топика
- Ответить на это сообщение
- Ссылка
Читать:
Как почистить дроссельную заслонку на ваз 2112 16 клапанов