Как импортировать файл CSV в две связанные таблицы PostgreSQL?
Допустим, у меня есть CSV-файл с двумя столбцами под названием «objects.csv» (первая строка = заголовки) со следующими строками:
colour,object
red,apple
blue,sky
blue,sea
red,plane
green,grass
red,car
red,ship
blue,pool
blue,towel
red,raincoat
green,hat
green,jumper
green,lawnmower
red,strawberry
red,cherry
blue,icicle
У меня есть база данных с двумя связанными таблицами, созданными с помощью следующего сценария SQL:
CREATE TABLE public.colours (
colour_id serial NOT NULL,
colour text NOT NULL,
CONSTRAINT colours_pkey PRIMARY KEY (colour_id)
);
CREATE TABLE public.objects (
object_id serial NOT NULL,
object text NOT NULL,
CONSTRAINT objects_pkey PRIMARY KEY (object_id),
objects_colour_id_fkey integer REFERENCES colours(colour_id)
);
Таблица colours содержит следующие значения:
colour_id,colour
1,red
2,green
3,blue
Мне нужно импортировать файл CSV в базу данных, чтобы значения цвета в первом столбце CSV были «заменены» соответствующими внешними ключами из таблицы цветов, поэтому таблица объектов будет содержать:
1,apple
3,sky
3,sea
1,plane
2,grass
1,car
1,ship
3,pool
3,towel
1,raincoat
2,hat
2,jumper
2,lawnmower
1,strawberry
1,cherry
3,icicle
Он импортирует плоский файл в две нормализованные «связанные» таблицы.
Как мне этого добиться?
Ответы 1
Я бы создал целевую таблицу и использовал команду psql \copy для посадки данных:
CREATE TABLE public.colours (
colour_id serial NOT NULL,
colour text NOT NULL,
CONSTRAINT colours_pkey PRIMARY KEY (colour_id)
);
CREATE TABLE
CREATE TABLE public.objects (
object_id serial NOT NULL,
object text NOT NULL,
CONSTRAINT objects_pkey PRIMARY KEY (object_id),
objects_colour_id_fkey integer REFERENCES colours(colour_id)
);
CREATE TABLE
create table objects_landing (colour text, object text);
CREATE TABLE
\copy objects_landing from 'objects.csv' csv header;
COPY 16
insert into colours (colour)
select distinct colour
from objects_landing
where not exists(
select 1 from colours
where colour = objects_landing.colour);
INSERT 0 3
insert into objects (object, objects_colour_id_fkey)
select distinct l.object, c.colour_id
from objects_landing l
join colours c
on c.colour = l.colour
where not exists (select 1
from objects
where object = l.object
and objects_colour_id_fkey = c.colour_id);
INSERT 0 16
Полученные результаты:
select * from objects;
┌───────────┬────────────┬────────────────────────┐
│ object_id │ object │ objects_colour_id_fkey │
├───────────┼────────────┼────────────────────────┤
│ 1 │ apple │ 3 │
│ 2 │ car │ 3 │
│ 3 │ cherry │ 3 │
│ 4 │ grass │ 1 │
│ 5 │ hat │ 1 │
│ 6 │ icicle │ 2 │
│ 7 │ jumper │ 1 │
│ 8 │ lawnmower │ 1 │
│ 9 │ plane │ 3 │
│ 10 │ pool │ 2 │
│ 11 │ raincoat │ 3 │
│ 12 │ sea │ 2 │
│ 13 │ ship │ 3 │
│ 14 │ sky │ 2 │
│ 15 │ strawberry │ 3 │
│ 16 │ towel │ 2 │
└───────────┴────────────┴────────────────────────┘
(16 rows)
select * from colours;
┌───────────┬────────┐
│ colour_id │ colour │
├───────────┼────────┤
│ 1 │ green │
│ 2 │ blue │
│ 3 │ red │
└───────────┴────────┘
(3 rows)
С такой посадочной таблицей - если у меня есть несколько процессов, которые создают эту таблицу и работают с ней одновременно, безопасно ли это при условии, что каждый из процессов выполняется в отдельной транзакции?
@MichałKlimczak Это должно сработать, но это не мой типичный вариант использования. Если, например, вы выполняете синхронизацию после периода работы без подключения к сети, вам следует использовать удаленный идентификатор или идентификатор устройства, чтобы разделять полученные записи на протяжении всей операции. Большая проблема, которую я вижу, заключается в том, что таблица будет иметь частые удаления, которые приводят к раздуванию без агрессивной очистки. Обычно я усекаю целевые таблицы в своих операциях ETL/ELT.
Не слышал об идее «посадочного стола». Спасибо, попробую.