Insert UUID GEN 4 Value

tango ward wrote:

I would like to know how to properly use uuid_generate_v4 when inserting data into PostgreSQL table.

When I run the command select * from pg_available_extensions;
I can see this uuid-ossp | 1.0 | | generate universally unique identifiers (UUIDs)

I am trying to find a basic and easy to understand documentation of it on how to use
when inserting data because I have a column that is a uuid type.

Are you looking for this?

ALTER TABLE tab ALTER col SET DEFAULT uuid_generate_v1();

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Hi,

thanks.

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.

On Thu, May 31, 2018 at 11:41 AM, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

On Thu, May 31, 2018 at 11:47 AM, tango ward <tangoward15@gmail.com> wrote:

Hi,

thanks.

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.

On Thu, May 31, 2018 at 11:41 AM, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:

tango ward wrote:

I would like to know how to properly use uuid_generate_v4 when

inserting data into PostgreSQL table.

When I run the command select * from pg_available_extensions;
I can see this uuid-ossp | 1.0 |

| generate universally unique identifiers (UUIDs)

I am trying to find a basic and easy to understand documentation of it

on how to use

when inserting data because I have a column that is a uuid type.

Are you looking for this?

ALTER TABLE tab ALTER col SET DEFAULT uuid_generate_v1();

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

sorry, I should be following bottom-post.

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

On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe <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

Okay I will try it.

On Thu, May 31, 2018 at 11:53 AM, tango ward <tangoward15@gmail.com> wrote:

On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe <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

Okay I will try it.

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

On May 30, 2018, at 9:57 PM, tango ward <tangoward15@gmail.com> wrote:

On Thu, May 31, 2018 at 11:53 AM, tango ward <tangoward15@gmail.com> wrote:

On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe <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

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();

On Wednesday, May 30, 2018, tango ward <tangoward15@gmail.com> wrote:

Okay I will try it.

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

Avoid references to "it" and just show the code you tried to run.

David J.

On Thu, May 31, 2018 at 12:18 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wednesday, May 30, 2018, tango ward <tangoward15@gmail.com> wrote:

Okay I will try it.

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

Avoid references to "it" and just show the code you tried to run.

David J.

I'm testing this code:

curr_pgsql.execute('''CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; ''')
curr_pgsql.execute('''
INSERT INTO enrollmentinfo (
created, modified,
secure_id,
relationship,
tuition_bill,
cashier_name,

cashier_phone_number
)
VALUES (current_timestamp, current_timestamp,
uuid_generate_v4(), '', '', '',)
''')

On Thu, May 31, 2018 at 12:06 PM, Rob Sargent <robjsargent@gmail.com> wrote:

On May 30, 2018, at 9:57 PM, tango ward <tangoward15@gmail.com> wrote:

On Thu, May 31, 2018 at 11:53 AM, tango ward <tangoward15@gmail.com>
wrote:

On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe <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

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.

On Thu, May 31, 2018 at 12:45 AM, tango ward <tangoward15@gmail.com> wrote:

On Thu, May 31, 2018 at 12:06 PM, Rob Sargent <robjsargent@gmail.com>
wrote:

On May 30, 2018, at 9:57 PM, tango ward <tangoward15@gmail.com> wrote:

On Thu, May 31, 2018 at 11:53 AM, tango ward <tangoward15@gmail.com>
wrote:

On Thu, May 31, 2018 at 11:51 AM, Laurenz Albe <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

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

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

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&gt;

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

On 05/30/2018 09:45 PM, tango ward wrote:

On Thu, May 31, 2018 at 12:06 PM, Rob Sargent <robjsargent@gmail.com
<mailto:robjsargent@gmail.com>> wrote:

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.

So please show what:

SELECT uuid_generate_v4();

is actually returning.

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

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(),"

Yeah. We haven't seen the actual query yet, but I'm betting the OP
really wrote

INSERT ... , 'uuid_generate_v4()', ...

not

INSERT ... , uuid_generate_v4(), ...

There might be some layers of client-side parameter mangling obscuring
what's actually happening.

regards, tom lane

Sent from Yahoo Mail on Android

On Wed, May 30, 2018 at 8:25 PM, tango ward<tangoward15@gmail.com> wrote: Hi,
I would like to know how to properly use uuid_generate_v4 when inserting data into PostgreSQL table.
When I run the command select * from pg_available_extensions;I can see this uuid-ossp          | aqaaQaaaaaaaaAaaaaaaaaaaaaaaaaaaaaaaa             |                   | generate universally unique identifiers (UUIDs)

I am trying to find a basic and easy to understand documentation of it on how to use when inserting data AAAAAAAAAAaaaaa I have a column that is a uuid type.
ZzzzzzzzzzzsssSSaaAaaaaaa@aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaAaaaaaaaaaaaAaAaaAaaaaaaaaaa@aaaaaaaaaaaaaaaaaaaaaaaaaaaaaAAAAAAAAAASThanks,J

On 05/31/2018 06:39 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

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(),"

Yeah. We haven't seen the actual query yet, but I'm betting the OP
really wrote

INSERT ... , 'uuid_generate_v4()', ...

not

INSERT ... , uuid_generate_v4(), ...

There might be some layers of client-side parameter mangling obscuring
what's actually happening.

I would tend to agree:

\d uuid_test
Table "public.uuid_test"
Column | Type | Collation | Nullable | Default
----------+------+-----------+----------+---------
uuid_fld | uuid |

insert into uuid_test values (uuid_generate_v4());
INSERT 0 1

select * from uuid_test ;
uuid_fld
--------------------------------------
f4bc853b-0c05-437b-904a-825cc6d3ea7b

In Python(using psycopg2) which is what the OP is using;

cur.execute("INSERT INTO uuid_test values(uuid_generate_v4())")

con.commit()

cur.execute("SELECT uuid_fld FROM uuid_test")

rs = cur.fetchall()

rs
[('f4bc853b-0c05-437b-904a-825cc6d3ea7b',),
('9e55f5ea-c504-4847-8674-05202ec499c6',)]

From this post:

/messages/by-id/CAA6wQLLVpjtxAhPy3K1f63WtvqjK4QrzBLKjeh0PDUOyCvbT2Q@mail.gmail.com

the OP seems to be on the right track. So something else is happening.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

On 05/30/2018 09:32 PM, tango ward wrote:

I'm testing this code:

curr_pgsql.execute('''CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; ''')
curr_pgsql.execute('''
                    INSERT INTO enrollmentinfo (
                                                         created, modified,
                                                         secure_id,
                                                         relationship,
                                                         tuition_bill,
                                                         cashier_name,

cashier_phone_number
                                                         )
                    VALUES (current_timestamp, current_timestamp,
                            uuid_generate_v4(), '', '', '',)
                                                         ''')

I just noticed something, the fields you show above do not match the
description of the table you showed here:

/messages/by-id/CAA6wQLJr6fjzx+ZK6N4yiViZYdFuGGkMT27zsp-mR1SjAzbGEg@mail.gmail.com

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
);

So which is correct?

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, May 30, 2018 at 9:32 PM, tango ward <tangoward15@gmail.com> wrote:

On Thu, May 31, 2018 at 12:18 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wednesday, May 30, 2018, tango ward <tangoward15@gmail.com> wrote:

Okay I will try it.

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

Avoid references to "it" and just show the code you tried to run.

David J.

I'm testing this code:

curr_pgsql.execute('''CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; ''')
curr_pgsql.execute('''
INSERT INTO enrollmentinfo (
created,
modified,
secure_id,
relationship,
tuition_bill,
cashier_name,

cashier_phone_number
)
VALUES (current_timestamp, current_timestamp,
uuid_generate_v4(), '', '', '',)
''')

​Executing this query is psql does not give the error​: ​Invalid input
syntax for UUID: uuid_generate_v4(),

This query is almost correct except you have a trailing comma in your
VALUES statement that is going to provoke: syntax error near "," (i.e., you
haven't provided a value for cashier_phone_number)

The query, which you have not yet shown, that provoked the "Invalid input
syntax for UUID: uuid_generate_v4()" must have had the function call
specified within single quotes in order for the server to think it was a
literal value instead of an expression to be evaluated.

David J.

On Thu, May 31, 2018 at 12:32 PM, tango ward <tangoward15@gmail.com> wrote:

On Thu, May 31, 2018 at 12:18 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wednesday, May 30, 2018, tango ward <tangoward15@gmail.com> wrote:

Okay I will try it.

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

Avoid references to "it" and just show the code you tried to run.

David J.

I'm testing this code:

curr_pgsql.execute('''CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; ''')
curr_pgsql.execute('''
INSERT INTO enrollmentinfo (
created,
modified,
secure_id,
relationship,
tuition_bill,
cashier_name,

cashier_phone_number
)
VALUES (current_timestamp, current_timestamp,
uuid_generate_v4(), '', '', '',)
''')

curr_pgsql.execute('''CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; ''')
curr_pgsql.execute('''
INSERT INTO enrollmentinfo (
created, modified,
secure_id,
relationship,
tuition_bill,
cashier_name,

cashier_phone_number
)
VALUES (current_timestamp, current_timestamp,
uuid_generate_v4(), '', '', '',)
''')

Hi, this is the query that I am trying to run. I'll try your suggestions
when I arrived in the office.