Re: Insert UUID GEN 4 Value

On 05/31/2018 05:36 AM, C GG wrote:
>
> On Thu, May 31, 2018 at 12:45 AM, tango ward <tangoward15(at)gmail(dot)com
> <mailto:tangoward15(at)gmail(dot)com>> wrote:
>
> On Thu, May 31, 2018 at 12:06 PM, Rob Sargent <robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)com>> wrote:
>
>
>
> On May 30, 2018, at 9:57 PM, tango ward <tangoward15(at)gmail(dot)com
> <mailto:tangoward15(at)gmail(dot)com>> wrote:
>
>> On Thu, May 31, 2018 at 11:53 AM, tango ward
>> <tangoward15(at)gmail(dot)com <mailto:tangoward15(at)gmail(dot)com>> wrote:
>>
>> On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe
>> <laurenz(dot)albe(at)cybertec(dot)at
>> <mailto:laurenz(dot)albe(at)cybertec(dot)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(dot)klaver(at)aklaver(dot)com