Postgres Pro
Downloads
Home   >   mailing lists

Re: Unique UUID value - PostgreSQL 9.2 - Mailing list pgsql-general

From drum.lucas@gmail.com
Subject Re: Unique UUID value - PostgreSQL 9.2
Date
Msg-id CAE_gQfUBE4Hu81Mjv1n8MHHS=YTUn6_BtVSVtS_gt19n+V4RjQ@mail.gmail.com
Whole thread Raw
In response to Re: Unique UUID value - PostgreSQL 9.2  (James Keener <jim@jimkeener.com>)
Responses Re: Unique UUID value - PostgreSQL 9.2  ("drum.lucas@gmail.com" <drum.lucas@gmail.com>)
Re: Unique UUID value - PostgreSQL 9.2  (Brent Wood <Brent.Wood@niwa.co.nz>)
List pgsql-general
Tree view


On 15 March 2016 at 10:46, James Keener <jim@jimkeener.com> 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?

pgsql-general by date:

Previous
From: James Keener
Date:
Subject: Re: Unique UUID value - PostgreSQL 9.2
Next
From: John Lumby
Date:
Subject: Re: how to switch old replication Master to new Standby after promoting old Standby