Move row of a table to an inherited table in PostgreSQL

Question

I have tables created as such:

BEGIN;
CREATE TABLE tag (
  id          SERIAL,
  name        VARCHAR(255)  UNIQUE NOT NULL,
  description VARCHAR(511)  NOT NULL          DEFAULT '',
  PRIMARY KEY(id)
);
CREATE TABLE fandom (
  id            SERIAL,
  name          VARCHAR(255)  UNIQUE NOT NULL,
  link          TEXT          UNIQUE NOT NULL,
  PRIMARY KEY(id)
);
CREATE TABLE character (
  fandom_id INT   REFERENCES fandom(id), -- allowed to be null
  link      TEXT  UNIQUE,
  PRIMARY KEY(id)
) INHERITS (tag);
COMMIT;

Next I do something like

INSERT INTO tag (name) VALUES ('John Doe')

At insertion time I do not know if 'John Doe' is a character, but I can check afterwards. I would like to do something like

BEGIN;
DELETE FROM tag WHERE name='John Doe';
INSERT INTO character (name) VALUES ('John Doe');
COMMIT;

The problem, as I see it, is that the ID is incremented for the second 'John Doe' and now all foreign keys referencing the tag 'John Doe' by ID are off by one, if the deletion even works.

Is there a way to simply move the row of 'John Doe' from 'tag' to 'character'?

I'm a DB noob, so additional notes are welcome in the comments.

Answer 1

Turns out inherited tables don't share UNIQUE modifiers between tables, which means you can first create the new entry in 'characters', then update your foreign keys with the new ID, then delete the old entry in 'tags'.

Btw, UNIQUE modifiers are also not inherited, which means 'name' is not unique anymore in the 'character' table.