Question 295696 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres insert into same id

I have this table1:

product amount timestamp_local
Prdct_1 100 2022-03-15
Prdct_2 50
Prdct_3 40 2023-05-05

I want to insert into this table2

product amount timestamp_local
Prdct_1 100
Prdct_2 50 2021-12-30
Prdct_3 40

This is my query but it doesn’t work.

insert into table2 (timestamp_local)
select timestamp_local from table1 b
where not exists (select 1 from table2 where id = b.id);

How could I solve it? Thanks for your answers.

Tags:

3

Answers


  1. 0 
    INSERT INTO table2 (product, amount, timestamp)
SELECT
  product,
  amount,
  COALESCE(timestamp, '2021-12-30') AS timestamp
FROM table1;
    Login or Signup to reply.
  2. 0

    It appears that certain important components are missing from your query, including the product and amount columns from the INSERT and SELECT clauses.
    In order to add entries from table 1 to table 2, you can try the following query, which pulls entries from Table 1 and inserts them into Table 2 only in the case that Table 2 does not have a matching  product and amount combination. 

    INSERT INTO table2 (product, amount, timestamp)
SELECT product, amount, timestamp
FROM table1 a
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 b
    WHERE a.product = b.product AND a.amount = b.amount
);

    Hope it’s helpful 🙂

    Login or Signup to reply.
  3. 0

    You can Do a so called UPSERT.

    insert into table2 (id,product,     amount,     timestamp_local)
select id, product,     amount,
CASE WHEN   timestamp_local IS NULL  then
'2021-12-30' ELSE NULL END timestamp_local 
from table1 b
ON CONFLICT(id) DO NOTHING;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search