Re: Unique UUID value - PostgreSQL 9.2

On Monday, March 14, 2016, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
wrote:

>
> On 15 March 2016 at 12:05, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com
> <javascript:_e(%7B%7D,'cvml','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
>> <javascript:_e(%7B%7D,'cvml','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
>>> <javascript:_e(%7B%7D,'cvml','drum(dot)lucas(at)gmail(dot)com');> <
>>> drum(dot)lucas(at)gmail(dot)com
>>> <javascript:_e(%7B%7D,'cvml','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?
>
>
Not reading the documentation for functions you've never heard of makes
the list.

David J.