Re: Unique UUID value - PostgreSQL 9.2

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

> On Mon, Mar 14, 2016 at 4:05 PM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Mon, Mar 14, 2016 at 3:51 PM, drum(dot)lucas(at)gmail(dot)com <
>> drum(dot)lucas(at)gmail(dot)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?