How to avoid duplicate records when inserting in PosgreSQL?

Question

I have a PostgreSQL database with the following structure:

                        Table "public.dynamic"
   Column    |          Type          | Collation | Nullable | Default
-------------+------------------------+-----------+----------+---------
 report_time | character varying(100) |           | not null |
 ip          | character varying(100) |           | not null |
 hostname    | character varying(100) |           | not null |
 vlan        | character varying(100) |           | not null |
 mac         | character varying(100) |           | not null |
 oficina     | character varying(100) |           | not null |
 status      | integer                |           | not null |

I insert the values as in the example below:

"insert into dynamic (report_time, ip, hostname, vlan, mac, oficina, status) values ( 1649169386, '10.10.0.20', 'server1', 'Servers', '00:00:0c:07:ac:01', 'Brasil', 1);"

I'm going to consume this data from an API to record it in PostgreSQL daily, but I don't want to enter duplicate values, just new records.

It is possible?

Does this answer your question? [How to prevent duplicate insert with insert return in postgres?](https://stackoverflow.com/questions/54544632/how-to-prevent-duplicate-insert-with-insert-return-in-postgres) — Kays Kadhi, Apr 20 '22 at 13:43

score 1 · Answer 1 · answered Apr 20 '22 at 14:39

One option is to create primary key, that is a single field or combination of fields that uniquely defines a record.

Postgres automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

How to avoid duplicate records when inserting in PosgreSQL?

1 Answers1