CODEWITHSUNDEEP
pythonsqlpostgresqlsql-insertpsycopg2
peer
peer

Reputation: 4679

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

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

Upvotes: 2

Views: 11330

Answers (1)

klin
klin

Reputation: 121474

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.

Upvotes: 8

Related Questions