Unique constraint insert prevention I've created a unique constraint to my table containing employee account details in an attempt to stop duplicated data inserts.

ALTER TABLE emp ADD CONSTRAINT uq_ UNIQUE (USERNAME, EMP_NAME);


However my problem comes when it comes inserting the test data:

----this first insert should work fine
INSERT INTO table_name (id, username, emp_name, regdate)
VALUES (1, 'test', 'test1', sysdate);


What I'm trying to prevent is an instance like this were the data about an employee is inserted twice into the table:

INSERT INTO table_name (id, username, emp_name, regdate)
VALUES (2, 'test', 'test2', sysdate);


As at the moment the insert runs and adds to the table which I'm trying to prevent it from doing.

Is there a way to combat this, would a trigger be a better option to use?
Creating a proper unique constraint on your table will prevent duplicates amongst the columns specified.

Your naming of the constraint should be unique to the table itself.  All constraints for a database are listed under the pg_constraint table.

ALTER TABLE emp ADD CONSTRAINT uq_emp_username_emp_name UNIQUE (USERNAME, EMP_NAME);


There are quite a few docs on the web you can find about standardized SQL naming formats.

To do a check against both the USERNAME and EMP_NAME, you would need to specify constraints individually for each.

ALTER TABLE emp ADD CONSTRAINT uq_emp_username UNIQUE (USERNAME);
ALTER TABLE emp ADD CONSTRAINT uq_emp_emp_name UNIQUE (EMP_NAME);


This would prevent employees from creating multiple usernames under the same employee name.

I find that the PostgreSQL official documentation is super concise and well written. 
https://www.postgresql.org/docs/9.5/static/ddl-constraints.html
Just make sure you have your appropriate Postgres version selected.