Re: Unique UUID value - PostgreSQL 9.2

On 15 March 2016 at 10:46, James Keener <jim(at)jimkeener(dot)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?