19 июля 2022 СуБД


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

table_name=auth_users

data_base=breys

echo "
begin;

select count(1) from $table_name ;

create  table ttt as select distinct id x,  * from $table_name ;

alter table ttt drop  column x;

truncate table $table_name; 

insert into $table_name (select * from ttt );

drop table ttt ;

select count(1) from $table_name ;

end;
" | psql $data_base

Суть метода такова:

  1. создать копию таблицы содержающую только уникальные по ID записи, для этого используется DISTINCT,
  2. затем нужно удалить сигнальное поле x
  3. почистить целевую таблицу
  4. затем перенести данные из временной таблицы в целевую
  5. удалить временную таблицу

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

for table_name in $(echo '\dt' | psql $data_base | awk '{print $3}'| grep -v ^$|sort ); do  

echo "
begin;

select count(1) from $table_name ;

create  table ttt as select distinct id x,  * from $table_name ;

alter table ttt drop  column x;

truncate table $table_name; 

insert into $table_name (select * from ttt );

drop table ttt ;

select count(1) from $table_name ;

end;
" | psql $data_base; 

done

с помощь транзакций можно предовратить удаление данных в случае проблемного импорта


28 июня 2018 04 марта 2022 СуБД


Есть таблица dispatch_dispatch_form в дублирующимися строками

чтобы быстро почистить таблицу от дублей необходимо:

delete from dispatch_dispatch_form  a using dispatch_dispatch_form b WHERE a.id < b.id AND a.email = b.email ;

вот так всё просто


08 января 2018 30 августа 2021 СуБД Postgresql Mysql | решать тесты


Нет простого способа перекинуть данные из разных СУБД, даже в OpenSource. Но всё же можно и сделать это можно через промежуточный формат CSV следующим образом в терминале Postgresql выгружаем нужную таблицу в файл /tmp/test.csv

Copy (Select * from region) To '/tmp/test.csv' With CSV DELIMITER '|';

Затем, необходимо разрешить Mysql загружать данные из внешних файлов, для этого нужно временно добавить в файл /etc/mysql/my.cnf в секцию добавить параметр secure-file-priv

[mysqld]
#
# * Basic Settings
#
secure-file-priv = ""

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

mv /tmp/test.csv /var/lib/mysql-files/

после это заходим в терминал mysql и загружаем данные из нашего файла

LOAD DATA INFILE '/var/lib/mysql-files/test.csv' INTO TABLE region FIELDS TERMINATED BY '|' ;

всё, теперь можно удалить файл и вернуть настройки Mysql сервера на место


22 апреля 2021 26 августа 2022 СуБД Postgresql psql bash awk for drop table cascade | решать тесты


Скрипт каскадного удаления таблиц в psql
 

for t in $(echo '\dt' | psql $data_base |awk -F\| '{print $2}'); do 
    echo "drop table $t cascade;" ; 
done | psql $data_base

Суть скрипта проста:


30 августа 2021 СуБД select psql concat



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

select order_num, slug  from catalog_humanmenu where tree_id = 6 and is_public= true  and order_num > 0 order by order_num

таким запросом выбираем необходимые строки и поля в исходной базе данных, затем из результатов этого запроса формирует скрипт обновления базы назначений

select concat('update catalog_humanmenu set order_num=',order_num,' where slug=''',slug,''';')  from catalog_humanmenu where tree_id = 6 and is_public= true  and order_num > 0 order by order_num;

теперь переключаем вывод результатов запроса не в терминал, а файл на локальном или удалённом диске

\o ordering-guid.txt

-- затем повторяем генератор SQL скрипта и вывод этой команды сохранится в файле ordering-guid.txt

select concat('update catalog_humanmenu set order_num=',order_num,' where slug=''',slug,''';')  from catalog_humanmenu where tree_id = 6 and is_public= true  and order_num > 0 order by order_num;

-- закрываем SQL сессию

\q

в новообразованном файле ordering-guid.txt необходимо удалить первые и последние строки, а затем выполнить его в базе назначения

psql < ordering-guid.txt

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