How do I ALTER a PostgreSQL table and make a column unique?
I figured it out from the PostgreSQL docs, the exact syntax is:
ALTER TABLE the_table ADD CONSTRAINT constraint_name UNIQUE (thecolumn);
Thanks Fred.
Can I add a UNIQUE constraint to a PostgreSQL table, after it's already created?
psql
's inline help:
\h ALTER TABLE
Also documented in the postgres docs (an excellent resource, plus easy to read, too).
ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE (columns);
In PostgreSQL, add a column with unique values according to specific column ordering / sorting
One approach is to add the id
column, set the value and then convert it to an identity. You have to be careful, because you want the numbering to start at 15 (for your sample data):
alter table db1 add column id int not null default 0;
update db1
set id = d.new_id
from (select db1.*, db1.ctid, row_number() over (order by member, service_date) as new_id
from db1
) d
where db1.ctid = d.ctid;
alter table db1 alter column id drop default;
alter table db1 alter column id add generated always as identity (start with 15) ;
alter table db1 add primary key (id);
Here is a db<>fiddle.
Postgresql alter table column type to unique not null
You cannot create 2 constraints with one single statement. And you have to use PostgreSQL syntax.
alter table users alter column email set not null;
alter table users add constraint email_unique unique (email);
Altering Postgres table to have unique with constraint when duplicates records alredy exist
I fail to see the purpose of the locks or of the two delete statements, but to execute the triggers right away, run this before the ALTER TABLE
:
SET CONSTRAINTS ALL IMMEDIATE;
Is it possible to add a column with unique constraint in one go?
You need a second ADD
option:
ALTER TABLE corporates
ADD COLUMN email varchar(100) NOT NULL,
ADD CONSTRAINT corporates_email_key UNIQUE (email);
In Postgresql, force unique on combination of two columns
CREATE TABLE someTable (
id serial PRIMARY KEY,
col1 int NOT NULL,
col2 int NOT NULL,
UNIQUE (col1, col2)
)
autoincrement
is not postgresql. You want a integer primary key generated always as identity
(or serial
if you use PG 9 or lower. serial
was soft-deprecated in PG 10).
If col1
and col2
make a unique and can't be null then they make a good primary key:
CREATE TABLE someTable (
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY (col1, col2)
)
Alter Unique Constraint in PSQL
[How can I] Locate this constraint with psql
We can get the same error message if we violate a primary key constraint.
# alter table addresses add constraint addresses_pk primary key (id);
ALTER TABLE
# insert into addresses values (1, 2, 4, 4);
ERROR: duplicate key value violates unique constraint "addresses_pk"
DETAIL: Key (id)=(1) already exists.
#
Try searching the information schema where constraint_type = 'PRIMARY KEY'
.
Note that we don't have to give a primary key constraint a name, as Postgres will generate a default of <table_name>_pkey
. So for this to be the solution in your case it means whoever created the primary key gave it an explicit name of address_uniq
, which would be confusing.
So a more likely possibility is that you have a unique index on those columns. Indexes don't show up in the information schema. You can check like this:
select * from pg_indexes where tablename = 'addresses';
[How can I] Alter/Update this constraint, and add 1 more column to it
If your problem is an index then do this:
# drop index address_uniq;
DROP INDEX
# create unique index address_uniq on addresses (address, hostname, coin_id);
CREATE INDEX
#
If it turns out it is a primary key constraint it's a similar process:
# alter table addresses drop constraint address_uniq;
ALTER TABLE
# alter table addresses add constraint address_uniq primary key (address, hostname,coin_id);
ALTER TABLE
#
Alter column to make it a derived column
You can try to use Generated Columns
If this column exists in your table, we might need to drop it before creating it.
Due to the document might not support ALTER COLUMN
with GENERATED
ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
ALTER TABLE Payment DROP COLUMN Taxes;
ALTER TABLE Payment
ADD Taxes [type of Taxes] GENERATED ALWAYS AS (0.12 * PaySlip) stored;
sqlfiddle
Related Topics
Can SQL Server Express Localdb Be Connected to Remotely
SQL Keep Getting Error with on Update Cascade
What Are the Benefits of Using Database Cursor
What Is a Simple and Efficient Way to Find Rows with Time-Interval Overlaps in SQL
Add Business Days to Date in SQL Without Loops
SQL Poor Stored Procedure Execution Plan Performance - Parameter Sniffing
Improving Performance of Cluster Index Guid Primary Key
Access - Compare Two Tables and Update or Insert Data in First Table
Display Names of All Constraints for a Table in Oracle SQL
Core Data Performance with Single Parent Entity
How to Delete in Ms Access When Using Join'S
Count the Null Columns in a Row in SQL
How to Store Array or Multiple Values in One Column
Divide by Zero/Null Workaround in Ssrs 2008 Report
SQL to Add Column with Default Value - Access 2003
Regular Expression to Match Common SQL Syntax
How to Convert a "Legacy" Left Outer Join Statement in Oracle