On 05/31/2018 05:36 AM, C GG wrote:
On Thu, May 31, 2018 at 12:45 AM, tango ward <tangoward15@xxxxxxxxx
<mailto:tangoward15@xxxxxxxxx>> wrote:
On Thu, May 31, 2018 at 12:06 PM, Rob Sargent <robjsargent@xxxxxxxxx
<mailto:robjsargent@xxxxxxxxx>> wrote:
On May 30, 2018, at 9:57 PM, tango ward <tangoward15@xxxxxxxxx
<mailto:tangoward15@xxxxxxxxx>> wrote:
On Thu, May 31, 2018 at 11:53 AM, tango ward
<tangoward15@xxxxxxxxx <mailto:tangoward15@xxxxxxxxx>> wrote:
On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe
<laurenz.albe@xxxxxxxxxxx
<mailto:laurenz.albe@xxxxxxxxxxx>> 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@xxxxxxxxxxx
