Update Postgres SQL table with SERIAL from previous insert

Question

Very new to SQL in general, working on creating 2 Tables, 1 for example representing appliances with a primary key, second representing a microwave for example with its FK referencing the primary tables PK.

I'm using SERIAL as the id for the primary table, but don't know how to update or insert into the second table using that specific generated value from the first.

I've created my tables using PSQL (Postgres15) like so:

CREATE TABLE Appliances (
  id SERIAL NOT NULL,
  field1 integer NOT NULL DEFAULT (0),
  -- 
  PRIMARY KEY (id),
  UNIQUE(id)
);

CREATE TABLE Microwaves (
  id integer NOT NULL,
  field1 integer,
  -- 
  PRIMARY KEY (id),
  FOREIGN KEY (id) REFERENCES Appliances(id)
);

Inserting my first row into the Appliance table:

INSERT INTO Appliances(field1) VALUES(1);

SELECT * FROM Appliances;

Yields:

And a query I found somewhere pulls the current increment of the SERIAL:

SELECT currval(pg_get_serial_sequence('Appliances', 'id'));

Yields:

I'm struggling to determine how to format the INSERT statement, have tried several variations around the below input:

INSERT INTO Microwaves VALUES(SELECT currval(pg_get_serial_sequence('Appliances', 'id'), 1));

Yields:

Appreciate feedback on solving the problem as represented, or a better way to tackle this in general.

score 0 · Answer 1 · answered Oct 23 '22 at 20:16

Okay looks like I stumbled on at least one solution that works in my case as taken from https://stackoverflow.com/a/50004699/3564760

DO $$
DECLARE appliance_id integer;
BEGIN 
  INSERT INTO Appliances(field1) VALUES('appliance2') RETURNING id INTO appliance_id;
  INSERT INTO Microwaves(id, field2) VALUES(appliance_id, 100);
END $$;

Still open to other answers if this isn't ideal.

Update Postgres SQL table with SERIAL from previous insert

1 Answers1