Обсуждение: Unique UUID value - PostgreSQL 9.2

Hi all,

 

favorite

I've got 2 tables: Temp-Table Table-A Need to copy all the data from Temp-Table to Table-A. But there is a Constraint that does not allow me to have duplicated items. So I need to create a Unique value. Example: Column Code | Column Info | code_67493675936 info_2016 Question: How can I do that using PostgreSQL 9.2?

On Mon, Mar 14, 2016 at 2:13 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:

Hi all,

 

favorite

I've got 2 tables: Temp-Table Table-A Need to copy all the data from Temp-Table to Table-A. But there is a Constraint that does not allow me to have duplicated items. So I need to create a Unique value. Example: Column Code | Column Info | code_67493675936 info_2016 Question: How can I do that using PostgreSQL 9.2?

You might want to try to restate the problem and question.  I'm having a hard time trying to figure out what you want.

Reading your subject line I'll point you to:

http://www.postgresql.org/docs/9.2/interactive/datatype-uuid.html

specifically the extension that is mentioned.

​Usually people figure out ways to accomplish their goals without using UUID though.

David J.

​

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?

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?

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.

I just need to know how can I do all of this

On Mon, Mar 14, 2016 at 3:51 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:

I just need to know how can I do all of this

​You may have missed my prior email.

You cannot COPY directly into the target table.  You must copy to a staging table.  You then insert from the staging table to the target table, listing every single column, and replacing those columns you want to change with some kind of expression.

Basically:

INSERT INTO targettable (col1, col2, col3)

SELECT col1, col2 || '_' || nextval('sequence_name')::text, col3

FROM stagingtable;

On Mon, Mar 14, 2016 at 4:05 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Mon, Mar 14, 2016 at 3:51 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:

I just need to know how can I do all of this

​You may have missed my prior email.

You cannot COPY directly into the target table.  You must copy to a staging table.  You then insert from the staging table to the target table, listing every single column, and replacing those columns you want to change with some kind of expression.

Basically:

INSERT INTO targettable (col1, col2, col3)

SELECT col1, col2 || '_' || nextval('sequence_name')::text, col3

FROM stagingtable;

​In theory an INSERT trigger might work too - but this is likely to be simpler and faster.

David J.

​

 

Not reading the documentation for functions you've never heard of makes the list.

David J.

INSERT INTO junk.test1 (account_id, code, info)
SELECT account_id, uuid_generate_v4(), info
FROM junk.test2;

It works but I get data like:

abc77f31-0ee6-44fd-b954-08a3a3aa7b28
f307fb42-23e5-4742-ab8f-8ce5c0a8e852

Is it possible to do the same, but with TEXT on the beginning?

Example:

test_32152563bdc4db11aa 

Not reading the documentation for functions you've never heard of makes the list.

David J.

INSERT INTO junk.test1 (account_id, code, info)
SELECT account_id, uuid_generate_v4(), info
FROM junk.test2;

It works but I get data like:

abc77f31-0ee6-44fd-b954-08a3a3aa7b28
f307fb42-23e5-4742-ab8f-8ce5c0a8e852

Is it possible to do the same, but with TEXT on the beginning?

Example:

test_32152563bdc4db11aa 

test_321525694417ad6b5f 

On Tue, Mar 15, 2016 at 2:01 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:

Not reading the documentation for functions you've never heard of makes the list.

David J.

INSERT INTO junk.test1 (account_id, code, info)
SELECT account_id, uuid_generate_v4(), info
FROM junk.test2;

It works but I get data like:

abc77f31-0ee6-44fd-b954-08a3a3aa7b28
f307fb42-23e5-4742-ab8f-8ce5c0a8e852

Is it possible to do the same, but with TEXT on the beginning?

Example:

test_32152563bdc4db11aa 

test_321525694417ad6b5f 

​Yes, it is possible.  Did you even try?  

"test" in that example is called a string.  There are bunch of functions and operators that work with strings.  They are documented here:

http://www.postgresql.org/docs/9.4/static/functions-string.html

You'll find the ones that "concatenate" - which basically is a fancy way to say: "to combine" or "to join together" - to be quite useful when faced with problems of this sort.

David J.

On 16 March 2016 at 10:30, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Tue, Mar 15, 2016 at 2:01 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:

Not reading the documentation for functions you've never heard of makes the list.

David J.

INSERT INTO junk.test1 (account_id, code, info)
SELECT account_id, uuid_generate_v4(), info
FROM junk.test2;

It works but I get data like:

abc77f31-0ee6-44fd-b954-08a3a3aa7b28
f307fb42-23e5-4742-ab8f-8ce5c0a8e852

Is it possible to do the same, but with TEXT on the beginning?

Example:

test_32152563bdc4db11aa 

test_321525694417ad6b5f 

​Yes, it is possible.  Did you even try?  

"test" in that example is called a string.  There are bunch of functions and operators that work with strings.  They are documented here:

http://www.postgresql.org/docs/9.4/static/functions-string.html

You'll find the ones that "concatenate" - which basically is a fancy way to say: "to combine" or "to join together" - to be quite useful when faced with problems of this sort.

David J.

I wouldn't ask if I wouldn't have tested it!

Will have a look.

I wouldn't ask if I wouldn't have tested it!

Will have a look.

​

I didn't asked if you tested what you did post.  I asked if you tried anything else before asking to be fed the answer.  If you did it would be nice to include those other attempts.

David J.

​

 

I wouldn't ask if I wouldn't have tested it!

Will have a look.

​

I didn't asked if you tested what you did post.  I asked if you tried anything else before asking to be fed the answer.  If you did it would be nice to include those other attempts.

David J.

​

 

It's already working:

INSERT INTO junk.wm_260_billables2 (account_id, code, info)
SELECT account_id, 'test_' || uuid_generate_v4(), info
FROM junk.wm_260_billables1;

BUT.. I'm getting a very long UUID - Would like some smaller

test_ea8bacbe-fa3c-4072-b511-643a56feb40e

SELECT ​'test_'​ || substring(uuid_generate_v4()::text, 1, 1);

Or use a sequence, or just compute "ROW_NUMBER() OVER ()" and tack that on.

David J.

​