Styjun

How can I tell someone that I want to be his or her friend?

Modeling an IP Address

Infinite Abelian subgroup of infinite non Abelian group example

Where does SFDX store details about scratch orgs?

I'm flying to France today and my passport expires in less than 2 months

Watching something be written to a file live with tail

What does it mean to describe someone as a butt steak?

Why doesn't H₄O²⁺ exist?

What's the point of deactivating Num Lock on login screens?

How much of data wrangling is a data scientist's job?

How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?

Brothers & sisters

Can one be a co-translator of a book, if he does not know the language that the book is translated into?

Fully-Firstable Anagram Sets

In a Spin are Both Wings Stalled?

Took a trip to a parallel universe, need help deciphering

Why is the 'in' operator throwing an error with a string literal instead of logging false?

Why "Having chlorophyll without photosynthesis is actually very dangerous" and "like living with a bomb"?

Is the Joker left-handed?

What do you call someone who asks many questions?

Does a druid starting with a bow start with no arrows?

Did Shadowfax go to Valinor?

Would Slavery Reparations be considered Bills of Attainder and hence Illegal?

Blender 2.8 I can't see vertices, edges or faces in edit mode

Postgres lowercase column and delete duplicates

Insert, on duplicate update in PostgreSQL?How do I lowercase a string in Python?postgres: upgrade a user to be a superuser?How to convert integer to serialsql query Insert Into with subqueryPostgres 9.3 Backup and Restore ErrorCannot insert duplicate key row in object 'dbo.CFD' with unique indexPostgres UPSERT syntax confusionDjango Migration Is FailingChange Index in Postgres

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I have the following table:

Customers
---------
name text
object_id integer
created_time timestamp with time zone 

Indexes:
 "my_index" UNIQUE CONSTRAINT, btree (name, object_id, created_time)

The unique index works fine but then I ended up with duplicate data like:

Name | object_id | created_time
------------------------------------
john | 1 | 2018-02-28 15:42:14.30573+00
JOHN | 1 | 2018-02-28 15:42:14.30573+00

So I tried to lowercase all my data in the name column with:

UPDATE customers SET name=lower(name) WHERE name != LOWER(name);

But this procedure generated an error because now I would be violating the index:

ERROR: duplicate key value violates unique constraint "my_index"
DETAIL: Key (name, object_id, created_time)=(john, 1, 2018-02-28 15:42:14.30573+00) already exists.

What kind of procedure could I use to delete rows that after casting to lowercase generate an index violation ?

postgresql indexing lowercase

share|improve this question

asked Jul 26 '18 at 21:02

PepperoniPizzaPepperoniPizza

3,79143768

add a comment | 

0

I have the following table:

Customers
---------
name text
object_id integer
created_time timestamp with time zone 

Indexes:
 "my_index" UNIQUE CONSTRAINT, btree (name, object_id, created_time)

The unique index works fine but then I ended up with duplicate data like:

Name | object_id | created_time
------------------------------------
john | 1 | 2018-02-28 15:42:14.30573+00
JOHN | 1 | 2018-02-28 15:42:14.30573+00

So I tried to lowercase all my data in the name column with:

UPDATE customers SET name=lower(name) WHERE name != LOWER(name);

But this procedure generated an error because now I would be violating the index:

ERROR: duplicate key value violates unique constraint "my_index"
DETAIL: Key (name, object_id, created_time)=(john, 1, 2018-02-28 15:42:14.30573+00) already exists.

What kind of procedure could I use to delete rows that after casting to lowercase generate an index violation ?

postgresql indexing lowercase

share|improve this question

asked Jul 26 '18 at 21:02

PepperoniPizzaPepperoniPizza

3,79143768

add a comment | 

I have the following table:

Customers
---------
name text
object_id integer
created_time timestamp with time zone 

Indexes:
 "my_index" UNIQUE CONSTRAINT, btree (name, object_id, created_time)

The unique index works fine but then I ended up with duplicate data like:

Name | object_id | created_time
------------------------------------
john | 1 | 2018-02-28 15:42:14.30573+00
JOHN | 1 | 2018-02-28 15:42:14.30573+00

So I tried to lowercase all my data in the name column with:

UPDATE customers SET name=lower(name) WHERE name != LOWER(name);

But this procedure generated an error because now I would be violating the index:

ERROR: duplicate key value violates unique constraint "my_index"
DETAIL: Key (name, object_id, created_time)=(john, 1, 2018-02-28 15:42:14.30573+00) already exists.

What kind of procedure could I use to delete rows that after casting to lowercase generate an index violation ?

postgresql indexing lowercase

share|improve this question

asked Jul 26 '18 at 21:02

PepperoniPizzaPepperoniPizza

3,79143768

Customers
---------
name text
object_id integer
created_time timestamp with time zone 

Indexes:
 "my_index" UNIQUE CONSTRAINT, btree (name, object_id, created_time)

Name | object_id | created_time
------------------------------------
john | 1 | 2018-02-28 15:42:14.30573+00
JOHN | 1 | 2018-02-28 15:42:14.30573+00

UPDATE customers SET name=lower(name) WHERE name != LOWER(name);

ERROR: duplicate key value violates unique constraint "my_index"
DETAIL: Key (name, object_id, created_time)=(john, 1, 2018-02-28 15:42:14.30573+00) already exists.

share|improve this question

asked Jul 26 '18 at 21:02

PepperoniPizzaPepperoniPizza

3,79143768

share|improve this question

asked Jul 26 '18 at 21:02

PepperoniPizzaPepperoniPizza

3,79143768

asked Jul 26 '18 at 21:02

PepperoniPizzaPepperoniPizza

3,79143768

asked Jul 26 '18 at 21:02

PepperoniPizzaPepperoniPizza

3,79143768

1 Answer
1

active

oldest

votes

3

If you have 'JOHN' and 'John' in you table but not 'john' it gets messy. here's one solution.

insert into customers
 select distinct lower("name") ,object_id,created_time from customers
 where name <> lower(name)
 and not (lower("name") ,object_id,created_time)
 in (select * from customers);

delete from customers where name <> lower(name);

after that consider:

alter table customers alter column name type citext;

share|improve this answer

edited Mar 21 at 21:53

answered Jul 26 '18 at 21:29

JasenJasen

8,34711938

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f51547446%2fpostgres-lowercase-column-and-delete-duplicates%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

If you have 'JOHN' and 'John' in you table but not 'john' it gets messy. here's one solution.

insert into customers
 select distinct lower("name") ,object_id,created_time from customers
 where name <> lower(name)
 and not (lower("name") ,object_id,created_time)
 in (select * from customers);

delete from customers where name <> lower(name);

after that consider:

alter table customers alter column name type citext;

share|improve this answer

edited Mar 21 at 21:53

answered Jul 26 '18 at 21:29

JasenJasen

8,34711938

add a comment | 

3

If you have 'JOHN' and 'John' in you table but not 'john' it gets messy. here's one solution.

insert into customers
 select distinct lower("name") ,object_id,created_time from customers
 where name <> lower(name)
 and not (lower("name") ,object_id,created_time)
 in (select * from customers);

delete from customers where name <> lower(name);

after that consider:

alter table customers alter column name type citext;

share|improve this answer

edited Mar 21 at 21:53

answered Jul 26 '18 at 21:29

JasenJasen

8,34711938

add a comment | 

If you have 'JOHN' and 'John' in you table but not 'john' it gets messy. here's one solution.

insert into customers
 select distinct lower("name") ,object_id,created_time from customers
 where name <> lower(name)
 and not (lower("name") ,object_id,created_time)
 in (select * from customers);

delete from customers where name <> lower(name);

after that consider:

alter table customers alter column name type citext;

share|improve this answer

edited Mar 21 at 21:53

answered Jul 26 '18 at 21:29

JasenJasen

8,34711938

insert into customers
 select distinct lower("name") ,object_id,created_time from customers
 where name <> lower(name)
 and not (lower("name") ,object_id,created_time)
 in (select * from customers);

delete from customers where name <> lower(name);

alter table customers alter column name type citext;

share|improve this answer

edited Mar 21 at 21:53

answered Jul 26 '18 at 21:29

JasenJasen

8,34711938

answered Jul 26 '18 at 21:29

JasenJasen

8,34711938

answered Jul 26 '18 at 21:29

JasenJasen

8,34711938