CODEWITHSUNDEEP
databasepostgresql
Jonathan Dantas Alves
Jonathan Dantas Alves

Reputation: 1

How to avoid duplicate records when inserting in PosgreSQL?

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?

Upvotes: 0

Views: 445

Answers (1)

Adelino Silva
Adelino Silva

Reputation: 928

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.

Upvotes: 1

Related Questions