Як скопіювати дані з однієї таблиці в іншу (щоб оновився) у PostgreSQL?

Як скопіювати дані з однієї таблиці в іншу при цьому, коли виявиться вже існуючі дані, їх не скопіював, а просто пропустив. Наприклад: у мене є бойова таблиця (всередині є стара база) і тестова таблиця. Є нова база рахунків. Я спочатку заливаю до тестової таблиці. а потім від тестової в бойову скопіюю (зараз намагаюся це зробити, але отримую помилку вже існує). Ось мій скрипт:

insert into cu(****,****,****) select ******* from test_cu

1 відповідь 1

У вас заголовок питання розходиться з описом. Все-таки треба оновлювати чи пропускати?

Пропускати рядки з конфліктами з унікальних обмежень просто. Починаючи з postgresql 9.5, є додатковий вираз on conflict в синтаксисі insert

insert into cu(****,****,****) select ******* від test_cu on conflict do nothing;

І все. Якщо у вас postgresql більш старої версії - раджу планувати оновлення. Версія 9.4 добігає кінця свого терміну підтримки (за планом останній випуск виправлень помилок буде 13 лютого 2020 року), старіші не підтримуються вже. Втім, якщо не критична конкурентність, то можна викрутитися малими силами:

insert in cu(****,****,****) select ******* from test_cu where not exists (select null from cu where cu.pk_field = test_cu.pk_field)

Якщо необхідно оновлювати - це питання, що і як саме необхідно оновлювати. Можна додавати записи і оновлювати дублікати: це знову ж таки on conflict але вже do update дію. Можливо вам потрібно оновлювати тільки збігаються рядків - тоді на допомогу синтаксис update .. from , який може оновлювати таблицю, використовуючи деяку вибірку даних.

SQL-Ex blog

У цій статті ми обговоримо функціональність копіювання бази даних PostgreSQL, яке є одним з найважливіших і найчастіше використовуваних на практиці дій. Є два варіанти оператора 'COPY', копіювання бази даних та копіювання таблиці. Ми обговоримо обидва ці варіанти.

Спочатку ми обговоримо процес копіювання бази даних PostgreSQL на тому сервері або з одного сервера на інший, а потім поговоримо про різні способи копіювання існуючої таблиці в нову таблицю на PostgreSQL.

Копіювання бази даних в межах одного сервера

Іноді потрібно створити точну копію існуючої бази даних на тому ж сервері з метою розробки та тестування. PostgreSQL допомагає досягти цього за допомогою оператора CREATE DATABASE, як показано нижче:

Базовий синтаксис:

CREATE DATABASE target_database  
WITH TEMPLATE source_database;

Цей оператор копіює source_database у target_database. Для пояснення сценарію давайте розглянемо наведений нижче запит.

CREATE DATABASE demo2  
WITH TEMPLATE demo;

Цей запит дає вказівку ядру PostgreSQL створити нову базу даних під назвою 'demo2', використовуючи шаблон існуючої бази даних 'demo'. База даних копіюється із збереженням внутрішньої структури; це означає, що копіюються всі схеми та їх таблиці та інші об'єкти, які стають доступними у новій базі даних.

Малюнки нижче дають докладне пояснення цього сценарію.

До копіювання бази даних 'demo'. База даних 'demo' містить кілька схем, що показано вище першому малюнку.

Після виконання запиту копіювання створюється база даних 'demo2' тієї самої структури.

Копіювання бази даних з одного сервера на інший

Нижче пояснюється найбільш ефективний спосіб копіювання бази даних із одного сервера на інший. Процедура складається із 4 кроків. Давайте розглянемо ці кроки та відповідні їм команди.

1. Виконання дампа вихідної бази даних файл SQL.

pg_dump -U postgres -d sourcedb -f sourcedb.sql

2. Копіювання файлу на цільовий сервер за допомогою putty або будь-якого іншого процесу передачі файлів.

3. Створення нової бази даних цільовому сервері.

CREATE DATABASE targetdb;

4. Відновлення файлу дампа на цільовому сервері.

psql -U postgres -d targetdb -f sourcedb.sql

PostgreSQL - копіювання таблиці

Функціональність копіювання таблиці допомагає скопіювати існуючу таблицю з її записами. Давайте розглянемо базовий синтаксис всіх можливих способів зробити це:

Копіювання таблиці з усіма даними

CREATE TABLE new_table AS TABLE existing_table;

приклад

Малюнок показує стан бази даних. Потім ми виконаємо наступний запит копіювання, щоб створити нову таблицю із існуючої таблиці books.

CREATE TABLE books2 AS TABLE books;

На цьому малюнку ми можемо побачити нову таблицю з ім'ям books2, яка має таку саму структуру і той самий набір даних.

Копіювання таблиці без даних

Також є можливість скопіювати лише структуру, але не дані. Цей сценарій особливо важливий для тестування програми у різних середовищах.

CREATE TABLE new_table AS TABLE existing_table  
WITH NO DATA;

Давайте створимо нову таблицю з назвою books3 з існуючої таблиці books для кращого розуміння.

CREATE TABLE books3 AS TABLE books  
WITH NO DATA;

Тут важливо відзначити відповідь ядра PostgreSQL після створення таблиці. У попередньому сценарії відповіддю був текст SELECT 2', який говорить про те, що в нову таблицю було вставлено 2 рядки, а в цьому прикладі відповідь системи відрізняється - це текст 'CREATE TABLE AS', що означає створення таблиці без даних.

Копіювання таблиці з частиною даних
У цьому останньому сценарії ми побачимо, як можна створити нову таблицю з наявної частини даних. Тут внутрішній запит використовується разом із пропозицією WHERE для отримання бажаного результату.

CREATE TABLE new_table AS  
SELECT * FROM existing_table WHERE condition;

приклад

CREATE TABLE books4 AS  
SELECT * FROM books WHERE /> Як пояснювалося вище, ми бачимо, що була створена таблиця тільки з одним рядком даних на основі вхідних даних у пропозиції WHERE.  

Зворотні посилання

Немає зворотних посилань

Коментарі

Показувати коментарі Як список | Деревоподібною структурою

Автор не дозволив коментувати цей запис

Copy PostgreSQL

Копіювання баз даних або таблиць у PostgreSQL можна виконати з використанням кількох методів та інструментів. Ось кілька прикладів:

1. Копіювання бази даних за допомогою команди pg_dump:

bash pg_dump -U username -W -h host_name -p port_number source_database > target_database

У цьому прикладі ми використовуємо команду `pg_dump` із зазначенням імені користувача (`-U`), імені хоста (`-h`), номери порту (`-p`) та імені вихідної бази даних (`source_database`). Потім ми перенаправляємо виведення команди до файлу з ім'ям цільової бази даних (`target_database`).

2. Копіювання таблиці в іншу базу даних за допомогою команди pg_dump та pg_restore:

bash pg_dump -U username -W -h host_name -p port_number -t source_table source_database > table_dump.sql pg_restore -U username -W -h host_name -p port_number -d target_database table_dump.sql

У цьому прикладі ми спочатку використовуємо команду `pg_dump` із зазначенням імені користувача (`-U`), імені хоста (`-h`), номери порту (`-p`), імені вихідної таблиці (`-t`) та імені вихідної бази даних (`source_database`). Потім ми перенаправляємо виведення команди у файл `table_dump.sql`. Потім ми використовуємо команду `pg_restore` для відновлення таблиці з файлу до іншої бази даних (`target_database`).

3. Копіювання таблиці через SQL-запити:

SQL CREATE TABLE target_table AS SELECT * FROM source_table;

У цьому прикладі ми використовуємо команду SQL для створення нової таблиці `target_table` та копіювання даних з вихідної таблиці `source_table`.

Однак, важливо відзначити, що ці методи мають деякі обмеження, наприклад, вони не копіюватимуть індекси, обмеження, права доступу та інші властивості об'єктів. Щоб повністю виконати копіювання баз даних або таблиць, рекомендується використовувати спеціалізовані інструменти, такі як `pg_basebackup`, `pg_restore` або інструменти реплікації, які надаються PostgreSQL.