Re: Unique UUID value - PostgreSQL 9.2

Поиск
Список
Период
Сортировка
От drum.lucas@gmail.com
Тема Re: Unique UUID value - PostgreSQL 9.2
Дата
Msg-id CAE_gQfUBE4Hu81Mjv1n8MHHS=YTUn6_BtVSVtS_gt19n+V4RjQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Unique UUID value - PostgreSQL 9.2  (James Keener <jim@jimkeener.com>)
Ответы 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>)
Список pgsql-general
Дерево обсуждения


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 по дате отправления:

Предыдущее
От: James Keener
Дата:
Сообщение: Re: Unique UUID value - PostgreSQL 9.2
Следующее
От: John Lumby
Дата:
Сообщение: Re: how to switch old replication Master to new Standby after promoting old Standby