How to insert a timestamp string into postgres db with psycopg?

Question

I have a table:

CREATE TABLE TABLE_WITH_A_TIMESTAMP
(
   TIMESTAMP_ID varchar(4),
   TIMESTAMP timestamp
)

and an INSERT command that works with other tools: INSERT INTO "my_schema"."table_with_a_timestamp" (timestamp_id, timestamp) VALUES ('0001', {ts '2020-01-01 00:00:00.001'});

Now I want to use psycopg2 to execute this command on my table.

query = """INSERT INTO "my_schema"."table_with_a_timestamp" (timestamp_id, timestamp) VALUES ('0001', {ts '2020-01-01 00:00:00.001'});"""
cursor.execute(query)

This does not work, the error message is:

syntax error at or near "{"
LINE 1: ...rsion) VALUES ('0001',{ts '2020-...
                                                             ^
: ProgrammingError
Traceback (most recent call last):
  File "/var/task/lambda_function.py", line 35, in lambda_handler
    cursor.execute(query)
psycopg2.ProgrammingError: syntax error at or near "{"
LINE 1: ...rsion) VALUES ('0001',{ts '2020-...

There are similar questions dealing with timestamps as python objects. However I would prefer not to parse the command, convert to a python timestamp and then do string interpolation. Since I'm reading the command from a file anyway I would much prefer to just bring the command in the right format (not necessarily in python). What is the right format for a string command above so that it writes the timestamp to a table?

I've tried: """...,'ts {2020-01-01 00:00:00.001}', ...""" -> invalid input syntax for type timestamp
"""...,"ts {'2020-01-01 00:00:00.001'}", ...""" -> column "{ts '2001-08-13 10:19:47.701'}" does not exist
"""...,ts '2020-01-01 00:00:00.001', ...""" -> type "ts" does not exist

klin · Accepted Answer · 2020-10-02 10:27:41Z

11

This {ts '2020-01-01 00:00:00.001'} is specific for the other tools. Postgres correct syntax is

INSERT INTO "my_schema"."table_with_a_timestamp" (timestamp_id, timestamp) 
VALUES ('0001', timestamp '2020-01-01 00:00:00.001');

though the word timestamp is not necessary in this case, see Db<>Fiddle.

edited Oct 2, 2020 at 10:27

answered Oct 1, 2020 at 20:07

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert a timestamp string into postgres db with psycopg?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related