CODEWITHSUNDEEP
postgresql
Jane_Tandert
Jane_Tandert

Reputation: 17

How to add data from a column from another table in postgresql?

I am a postgresql newbie and need to do the following:

I have table1: id1 integer, column1 timestamp, column2 timestamp, column3 timestamp.

I have table2: id2 integer, column1 varchar, column2 numeric, column3 timestamp.

I would like to append data from table2.column3 to table1.column1, while the table1.id1 (primary key) should autoincrement. The corresponding fields in table1.column2 should get the timestamp of the actual time. Table1.column3 should stay empty.
There is no common field linking the tables. Further, the table2.column3 contains multiple entries with the same value. However, there should not be duplicate values in table1.column1.

I did a lot of research online, but did not find something similar.

I tried: 

Insert into table1 (id1,column1,column2)
values ((id1).nextval),(select column3 from table2),now());

However it gave me an syntax error at the select command and I am sure there are other problems, too.

Can anyone help,please? I do not have any clue.

Janet

Upvotes: 1

Views: 2842

Answers (1)

pokoli
pokoli

Reputation: 1065

You can combine a insert with a select query (which will insert one record for each result of the select query). On your example it will be: 

INSERT INTO table1 (column1,column2)
SELECT column3, NOW() from table2

NOTE: I omitted the id column, as it has a default value of auto increment will be filled by default.

Upvotes: 1

Related Questions