How to transform a file into INSERT with foreign keys?

Question

Coming from my question here, the answers were valid but I tried to transform MySQL to PostgreSQL. This is not the best way to do what I need.

I have a file with more than 600 000 objects:

paper
pen
whatever
...

All those objects are listed in a "cyclic" colors, and I know what the cycle look like but it's not in the file itself, so, to give you an example (it's much longer than the example, but I hope you get the idea):

- black + red
- black + blue
- black + yellow
- white + red
- white + blue
- white + yellow
- black + red
- black + blue
- ....

I've separated those colors into a table wisely named "colors", and I've created another table named "group_colors", which is a many_to_many relationship with colors. The objects will have a one_to_one relationship with group_colors

I want to generate my own script, and only one script that:

• creates tables colors, group_colors and objects
• creates INSERT statements for colors
• based on those previous INSERT statements use the ID's of colors to creates INSERT statements for group_colors
• from now on, creates INSERT statements for objects
• based on the ID's of group_colors and ID's of just inserted object, creates INSERT statements for objects_group_colors

This script should starts like that:

DROP TABLE IF EXISTS color;
CREATE TABLE color (
   id AUTOINC,
   description VARCHAR(50)
)
DROP TABLE IF EXISTS group_colors;
CREATE TABLE group_colors(
   id AUTOINC,
   description VARCHAR(50)
)
DROP TABLE IF EXISTS group_colors_color;
CREATE TABLE group_colors_color(
   group_colors_id INT,
   color_id INT,
   group_colors_id INT,
   CONSTRAINT color_id NOT NULL FOREIGN KEY REFERENCES color(id),
   CONSTRAINT group_colors_id NOT NULL FOREIGN KEY REFERENCES group_colors(id)
)
DROP TABLE IF EXISTS obj;
CREATE TABLE obj(
   id AUTOINC,
   description VARCHAR(50),
   group_colors_id INT,
   CONSTRAINT group_colors_id NOT NULL FOREIGN KEY REFERENCES group_colors(id)
)

And so on. The problem is not in creating table, it's in how to INSERT VALUES and re-use the ID's of those values later on (very very late (>600 000 rows)), and more than once.

How would you do?

Answer 1

It seems like you want to feed all that data as a big static file to the psql command line interpreter.

So you might want to use the \gset command to get back at the values inserted previously and load them into psql variables (see psql manpage), which can then be reinjected into other statements further in the script.

Example:

create table ref (id serial primary key, name text);
create table points_to_ref(fk int references ref(id));
insert into ref(name) values('foobar');
-- more queries here...
-- now get back at what we inserted first
select id as id_xyz from ref where name='foobar';
\gset
insert into points_to_ref values (:id_xyz);

The point of \gset is to create a psql variable from each column returned by the last SQL statement. Then you can reinject them with the :varname syntax. Make sure they're quoted properly, as explained in the doc. In the above example, :id_xyz contains a simple integer, so no quoting is necessary.

The server never sees these variables. It's a textual replacement that happens client-side. It's a quite different way than the accepted answer in the question you linked: PostgreSQL: a valid variable assignation sample? It seems to me that it's closer to your MySQL script.

Also \gset is relatively new in psql (9.3 I think), make sure you don't use an older version.