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

On 15 March 2016 at 12:05, David G. Johnston <david.g.johnston@gmail.com> wrote:

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.

​

 

Hi David... Thanks for you reply. I haven't seen it before.

So I'm doing:

CREATE EXTENSION "uuid-ossp";

INSERT INTO junk.wm_260_billables2 (account_id, code, info) SELECT account_id, code || '_' || nextval('uuid_generate_v4()')::text, info FROM junk.wm_260_billables1;

Getting the error:

ERROR:  relation "uuid_generate_v4()" does not exist

But the extension is working:

select uuid_generate_v4() as one;
                 one                  
--------------------------------------
 59ad418e-53fa-4725-aadb-8f779c1a12b2
(1 row)

select * from pg_available_extensions;
uuid-ossp              | 1.0             | 1.0               | generate universally unique identifiers (UUIDs) 

Do you know what might I being doing wrong?