Re: Unique UUID value - PostgreSQL 9.2

[Brent Wood <Brent.Wood@xxxxxxxxxx>]

Not best practice but perhaps viable...

In the target table add a serial datatype column as part of the unique constraint.

Do not populate this column explicitly on insert, but have the db do it for you. It will allocate an incremental (unique) value automatically on insert.

But I think your problem is more fundamental - if you genuinely have duplicate values in a column - there should not be a unique constraint on it. If it should be unique, then you should modify your insert data.

Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529

Brent Wood

Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery

+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz

To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.

---

From: pgsql-general-owner@xxxxxxxxxxxxxx <pgsql-general-owner@xxxxxxxxxxxxxx> on behalf of drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx>
Sent: Tuesday, March 15, 2016 10:56 AM
To: James Keener
Cc: David G. Johnston; Postgres General
Subject: Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

 

On 15 March 2016 at 10:46, James Keener <jim@xxxxxxxxxxxxx> wrote:

Is a uuid a valid value in the application making use of the data? Why can't you add the column to table b and then import, or use create the uuid in the import select clause? I'm also having trouble understanding the problem and why you've discounted the options you've not even told us you've considered.

I want to import data from table A to table B, but when doing it the column "code" on table B has to have some unique random data.

I could use UUID like:

insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111'); 

but I'm doing:

INSERT INTO tableb (SELECT * FROM TABLEA)

So, how to use UUID using the SELECT above?

On the target table, I've got a CONSTRAINT:

ALTER TABLE dm.billables
  ADD CONSTRAINT uc_billable_code_unique_per_account UNIQUE("account_id", "code");

So I'm importing a CSV file with repeated values on the field "code"

Example:

'Interpreting Normal/AH'
'Interpreting Normal/AH'
'Interpreting Normal/AH'
'Interpreting Normal/AH'
'Interpreting Normal/AH4'
'Interpreting Normal/AH'
'Interpreting Normal/AH6'
'Interpreting Normal/AH'

So when importing it to the target table I got the error:

ERROR:  duplicate key value violates unique constraint "uc_billable_code_unique_per_account"
DETAIL:  Key ("account_id", "code")=(32152, 'Interpreting Normal/AH') already exists.

Command used to import the values:

INSERT INTO dm.billables (SELECT billable_id, code, info FROM temptable)

OR directly through the CSV file:

COPY dm.billables (code, info, unit_cost, unit_price, account_id) FROM '/var/lib/pgsql/sql/lucas/charge_test.csv' WITH DELIMITER ',' QUOTE '"' CSV HEADER;

So. I determined that to do that without dropping the CONSTRAINT, I'll have to generate a unique but random value to the "code" column.

NOW:

COLUMN CODE                                |       COLUMN INFO

'Interpreting Normal/AH'                            Travel1

'Interpreting Normal/AH1'                            trip2

'Interpreting Normal/AH2'                            test897

'Interpreting Normal/AH3'                            trip11

'Interpreting Normal/AH4'                            trave1

NEW:

COLUMN CODE                                |       COLUMN INFO

code_32152563bdc6453645                            Travel1

code_32152563bdc4566hhh                            trip2

code_32152563b654645uuu                           test897

code_32152563bdc4546uui                            trip11

code_32152563bdc4db11aa                            trave1

How can I do that?