1

I have a requirement to convert date in source table column to a date time in target.Now, this date time column is part of a composite primary key in target, so if there are any duplicate entry then we have to increase the nanosecond by 1. This has to be done in Postgres CTE for DBT query. Also there can be duplicates in source so to achieve unique value we need to add nanosecond while conversion for duplicate rows.

For eg, 2021-07-30 00:00:00.000000 If more than one row for same effective date then increment nanosecond by 1

Update:- postgres version 11.9

Sneha Nadar
  • 65
  • 8
  • Does this answer your question? [Insert, on duplicate update in PostgreSQL?](https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql) – Luuk Sep 19 '21 at 17:22
  • FYI, `2021-07-30-00.00.00.000000` is an invalid format for a datetime/timestamp. It should be something like `2021-07-30 00:00:00.000000`. Are you actually using Postgres 9.4? – Adrian Klaver Sep 19 '21 at 17:23
  • 1
    Also Postgres second resolution only goes done to `microseconds` as you show, not `nanoseconds`. – Adrian Klaver Sep 19 '21 at 17:32
  • What version of Postgres are you using? Also provide some example data from the source and example of how you want it to look in target table. Add the preceding as update to your question. – Adrian Klaver Sep 19 '21 at 20:05

1 Answers1

1

Postgres 9.4 doesn't have on conflict. And Postgres doesn't support "nanosecond" as an interval. But if you won't get conflicts on after incrementing, you can try:

insert into target (dt)
    select (case when t.dt is null then s.dt
                 else s.dt + interval '1 microsecond'
            end)
    from source s left join
         target t
         on s.dt = t.dt;

This problem gets a bit trickier if you have duplicates in the source or if there are conflicts after incrementing. You haven't provided sample data and desired results, so this answers the simplest interpretation of your question,.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786