i am trying to insert multiple records got from join table to another table user_to_property. In user_to_property table user_to_property_id is primary, not null it is not autoincrement. So i am trying to add user_to_property_id manually by increment of 1.

WITH selectedData  AS 
    (   -- selection of the data that needs to be inserted
            SELECT t2.user_id as userId
FROM property_lines t1
INNER JOIN user t2 ON t1.account_id = t2.account_id
    )
INSERT INTO user_to_property (user_to_property_id, user_id, property_id, created_date)
VALUES ((SELECT MAX( user_to_property_id ) 1 FROM user_to_property),(SELECT 
        selectedData.userId
        FROM selectedData),3,now());

above gives me:ERROR: more than one row returned by a subquery used as an expression

how to insert multiple records to table from join of other tables? where user_to_property table contains unique record for the same user-id and property_id there should be only 1 record.

CodePudding user response：

Typically for Insert you use either values or select. The structure values( select...) often (generally?) just causes more trouble than it worth, and it is never necessary. You can always select a constant or an expression. In this case convert to just select. For generating your ID get the max value from your table and then just add the row_number that you are inserting: (see demo)

insert into user_to_property(user_to_property_id  
                            , user_id  
                            , property_id   
                            , created 
                            ) 
with start_with(current_max_id) as 
    ( select max(user_to_property_id) from user_to_property )
select current_max_id   id_incr, user_id, 3, now()  
  from (
        select t2.user_id, row_number() over() id_incr
          from property_lines t1
          join users t2 on t1.account_id = t2.account_id
       ) js
  join start_with on true; 

A couple notes:

1. DO NOT use user for table name, or any other object name. It is a documented reserved word by both Postgres and SQL standard (and has been since Postgres v7.1 and the SQL 92 Standard at lest).
2. You really should create another column or change the column type user_to_property_id to auto-generated. Using Max() 1, or anything based on that idea, is a virtual guarantee you will generate duplicate keys. Much to the amusement of users and developers alike. What happens in an MVCC when 2 users run the query concurrently.