Re: Unique UUID value - PostgreSQL 9.2

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Monday, March 14, 2016, drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> wrote:

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

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

On Mon, Mar 14, 2016 at 3:51 PM, drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> 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?

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

David J.