Re: Insert UUID GEN 4 Value - Mailing list pgsql-general

On 05/31/2018 05:36 AM, C GG wrote:
> 
> On Thu, May 31, 2018 at 12:45 AM, tango ward <tangoward15@gmail.com 
> <mailto:tangoward15@gmail.com>> wrote:
> 
>     On Thu, May 31, 2018 at 12:06 PM, Rob Sargent <robjsargent@gmail.com
>     <mailto:robjsargent@gmail.com>> wrote:
> 
> 
> 
>         On May 30, 2018, at 9:57 PM, tango ward <tangoward15@gmail.com
>         <mailto:tangoward15@gmail.com>> wrote:
> 
>>         On Thu, May 31, 2018 at 11:53 AM, tango ward
>>         <tangoward15@gmail.com <mailto:tangoward15@gmail.com>> wrote:
>>
>>             On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe
>>             <laurenz.albe@cybertec.at
>>             <mailto:laurenz.albe@cybertec.at>> wrote:
>>
>>                 tango ward wrote:
>>                 > I found this: 
>>                 > 
>>                 > CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
>>                 > SELECT uuid_generate_v4();
>>                 > 
>>                 > My problem is I have a table like this:
>>                 > CREATE TABLE enrollmentinfo (
>>                 >     id integer NOT NULL,
>>                 >     created timestamp with time zone NOT NULL,
>>                 >     modified timestamp with time zone NOT NULL,
>>                 >     secure_id uuid NOT NULL,
>>                 >     relationship character varying(50) NOT NULL,
>>                 >     tuition_bill character varying(255) NOT NULL
>>                 > );
>>                 > 
>>                 > Now I need to insert data into this table, I just don't know if I can use something like this
>>                 > "INSERT INTO enrollmentinfo (current_timestamp, current_timestamp, uuid_generate_v4(), '', '');
>>                 > I haven't tried this but also not sure if I can call the function inside INSERT.
>>
>>                 Why didn't you try it?
>>
>>                 I see no problem with that.
>>
>>                 Yours,
>>                 Laurenz Albe
>>                 -- 
>>                 Cybertec | https://www.cybertec-postgresql.com
>>
>>                 <https://www.cybertec-postgresql.com>
>>
>>
>>
>>             Okay I will try it. 
>>
>>
>>         When I tried it, I am getting an error: Invalid input syntax
>>         for UUID: uuid_generate_v4(),
>         What do you get from
>         Select uuid_generate_v4();
> 
> 
>     If I try these in psql, CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
>     SELECT uuid_generate_v4();
> 
>     I am getting the generated code but I dunno how to use this in
>     INSERT statement. I am getting an error of invalid input systex for
>     UUID.
> 
> 
> Hello,
> 
> Using INSERT (..) VALUES (..) won't execute functions. It expects 
> literal values. Instead do something like

That is not the case:

https://www.postgresql.org/docs/10/static/sql-insert.html

"VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
...
expression

     An expression or value to assign to the corresponding column.
"


\d ts_tsz_test
                       Table "public.ts_tsz_test"
  Column |            Type             | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
  fld_1  | timestamp without time zone |           |          |
  fld_2  | timestamp with time zone    |           |          |
  fld_3  | integer                     |           |          |

CREATE OR REPLACE FUNCTION public.test_fnc()
  RETURNS integer
  LANGUAGE plpgsql
AS $function$
BEGIN
     RETURN 2;
END;

$function$

insert into ts_tsz_test values (localtimestamp(2), current_timestamp(2), 
test_fnc());
INSERT 0 1

select * from ts_tsz_test ;
          fld_1          |           fld_2           | fld_3
------------------------+---------------------------+-------
  2018-05-31 06:06:39.71 | 2018-05-31 06:06:39.71-07 |     2


My suspicion is it had to do with this from a post upstream:

"When I tried it, I am getting an error: Invalid input syntax for UUID: 
uuid_generate_v4(),"

Namely the output of the function is not meeting the input syntax for 
the uuid field.


> 
> INSERT INTO enrollmentinfo (id, created, modified, secure_id, 
> relationship, tuition_bill) SELECT 
> your_id_returning_function_or_a_literal_value(), current_timestamp, 
> current_timestamp, uuid_generate_v4(), 'some relationship', 'some 
> tuition bill text';
> 
> ...modified with real values, of course...
> 
> That should get you over the hump. Good luck on your project!
> 
> CG
> 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com