PostgreSQL unique string field

Question

My database is PostgreSQL. The language is Java.
Table name is phrase with column name name.

At any time many users are inserting many rows to this table.
And we need to make sure that a certain field is unique.
And if such a field was found during loading, I want to return the row ID.

I could for example make a field the unique primary key, and when a row id inserted, catch the exception and look up the existing row.
But I think that is a bad idea.

I could just look for that row first and then insert.
But how can we avoid that the concurrent transactions get in each other's way?

And when downloading, is it better to do a batch download, and how do I do that in PostgreSQL? I do not even know.

can use transactions level?

stim644
– stim644

2017-02-08 07:27:25 +00:00
Commented Feb 8, 2017 at 7:27 — stim644
– stim644, Commented Feb 8, 2017 at 7:27

Laurenz Albe · Accepted Answer · 2017-02-08 11:27:05Z

3

You could create a UNIQUE constraint and INSERT ... ON CONFLICT:

CREATE TABLE mytable (
   id integer PRIMARY KEY,
   name text NOT NULL
      CONSTRAINT name_unique UNIQUE
);

INSERT INTO mytable (id, name)
   VALUES (1, 'me');

Now to run a batch INSERT that returns the id of each affecte row, run

INSERT INTO mytable (id, name)
   VALUES (2, 'me'),
          (3, 'new')
ON CONFLICT (name)
   DO UPDATE SET name = EXCLUDED.name
RETURNING id;

The strange UPDATE that does not actually change the row is necessary if you want the id back.

answered Feb 8, 2017 at 11:27

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

stim644 Over a year ago

Will can i use instead string value use string hash code for search? it will speed search?

Laurenz Albe Over a year ago

What is a string hash value?

Farzad Abdolhosseini Over a year ago

I think the question is: how performant is this sort of unique constraint and if it's not, can we hash the string first and then constrain on the hash being unique instead of the string itself. Though the database implementation could do this under the hood anyway.

Laurenz Albe Over a year ago

Using a hash would be slower, because the hash has to be calculated. Besides, there is always the chance of a hash collision, so using a hash in a unique condition is questionable. Only consider such ideas if the strings are really long.

Erich Kitzmueller · Accepted Answer · 2017-02-08 11:11:02Z

1

Instead of catching the exception, you can use the INSERT ... ON CONFLICT DO NOTHING clause available in PostgreSQL. By checking the number of affected rows (returncode of PreparedStatement.executeUpdate), you can detect if there was a conflict.

E.g.

 PreparedStatement pstmt = conn.prepareStatement("insert into x values (?,?) on conflict do nothing");
 pstmt.setInt(1, myId);
 pstmt.setInt(2, myValue);
 int rc = pstmt.executeUpdate();
 if (rc == 0) {
    // fetch the existing row...
 }

answered Feb 8, 2017 at 11:11

Erich Kitzmueller

37k5 gold badges85 silver badges104 bronze badges

2 Comments

stim644 Over a year ago

Will can i use instead string value use string hash code for search? it will speed search?

Erich Kitzmueller Over a year ago

just use the string; since you need a unique index anyway, it will be fast enough

Collectives™ on Stack Overflow

PostgreSQL unique string field

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related