Search code examples
sqldatabasesubquery

Postgres: Problem INSERTING records if they does not exist already in a table


I've two tables:

Temporal Table: planning.env_seleccion_envase seleccionado_temp (Updates one time every day and by now has 500 records)

Productive Table: planning.env_seleccion_envase_seleccionado (Consolidate the data from all the year and by now has 0 records)

In order to insert just the new records from the temporal table to the productive table I made a query, here is my try:

INSERT INTO planning.env_seleccion_envase_seleccionado(centro, sku, texto_breve_material, movimiento, almacen, cantidad, unidad_medida_base, fecha_contabilizacion, fecha_entrada, sag, uen, drv, cupo, tipo_envase, cupo_agrupado, color_envase, cantidad_abs, fecha_carga) 
    SELECT DISTINCT centro, sku, texto_breve_material, movimiento, almacen, cantidad, unidad_medida_base, fecha_contabilizacion, fecha_entrada, sag, uen, drv, cupo, tipo_envase, cupo_agrupado, color_envase, cantidad_abs, fecha_carga
    FROM planning.env_seleccion_envase_seleccionado
    WHERE NOT EXISTS (
        SELECT 'X' 
        FROM planning.env_seleccion_envase_seleccionado_temp
        WHERE 
            planning.env_seleccion_envase_seleccionado_temp.fecha_carga = 
            planning.env_seleccion_envase_seleccionado.fecha_carga
    );

I'm using just the field FECHA_CARGA as comparative, because this field has the date of the record insert, also I tried to used all the fields from the table in the "Where" section of the query but it did not work either.

In both cases I get the result of ** INSERT 0 0 ** and it just insert nothing

Can you help me guys? Any ideas? Sorry if the solution is bovious but i'm a little bit new


Solution

  • Example:

    insert into
      usuario(id, email)
    select
      nextval('seq_usuario'),
      '[email protected]'
    where
      NOT EXISTS (
        select
          id,
          email
        from
          usuario
        where
          email like '[email protected]'
      );