In PostgreSQL, I have two tables
CREATE TABLE A (
id int PRIMARY KEY,
name varchar
);
CREATE TABLE B (
id int PRIMARY KEY,
a A,
type varchar
);
As you can see, one of the Table B's attribute type is A.
First, I insert a row in Table A with the following query
INSERT INTO A(id, name) VALUES(3241,'Item1');
Then, I need to insert a new row in Table B. How can I select a row in Table A when inserting a new row in Table B, to be used as attribute a value?
Firstly, it's not a good idea. If you want to combine data from the two tables you should use regular reference, like this:
CREATE TABLE B (
id int PRIMARY KEY,
a_id int REFERENCES A(id),
type varchar
);
Still, it's formally possible what you want (however not recommended):
INSERT INTO B(id, A, type)
VALUES (1, (SELECT A FROM A WHERE id = 3241), 'some type');
SELECT * FROM B;
id | a | type
----+--------------+-----------
1 | (3241,Item1) | some type
(1 row)
Try this:-
INSERT INTO B (id,a,type) VALUES
(123456,(SELECT A from A WHERE id=1234656), 'typeName' );
