Postgres "invalid input syntax for type oid:"

Question

I am using Postgres 10 on Centos (PostgreSQL 10.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit)

When I execute the SQL statement below I receive the error "invalid input syntax for type oid:"

SELECT persistenceid, process_id, process_id AS case_id, completed_date_time,
       created_date_time::timestamp AT TIME ZONE 'EST' AS created_date_time,
       status, customer_id, request_type AS ticket_type, user_id, requestor_id,
       requester, customer_id, jira_ticket, feedback_type, priority,
       public_request, request_method, title,
       convert_from(lo_get(description::oid),'UTF8') AS description
FROM supportrequest
WHERE (user_id=5 OR requestor_id=5)
  AND customer_id='346361'
ORDER BY created_date_time DESC;

If I execute the statement without the WHERE clause It works fine.

What is the type and content of the "description" column, which you're carrying to oid in the select list? — IMSoP
– IMSoP, Commented Jan 3, 2022 at 17:20
It is a varchar... set by the application. We are just pulling stats from the table so I don't have control to change the type the way I would like to. I don't see any non-numerical characters that might cause it and logs are not printing any additional details — norris-md
– norris-md, Commented Jan 3, 2022 at 17:23

norris-md · Accepted Answer · 2022-01-03 17:29:44Z

1

UPDATE

If I execute

SELECT persistenceid, process_id, process_id AS case_id, completed_date_time,
       created_date_time::timestamp AT TIME ZONE 'EST' AS created_date_time,
       status, customer_id, request_type AS ticket_type, user_id, requestor_id,
       requester, customer_id, jira_ticket, feedback_type, priority,
       public_request, request_method, title,
       CASE
            WHEN TRUE = description ~ '^[0-9\.]+$' THEN convert_from(lo_get(description::oid),'UTF8')
            WHEN FALSE = description ~ '^[0-9\.]+$' THEN description::varchar
           ELSE null END
       AS description
FROM supportrequest
WHERE (user_id=5 OR requestor_id=5)
AND (customer_id='346361')
ORDER BY created_date_time DESC;

it revealed a carriage return as the first character in a now of the field columns for description.

answered Jan 3, 2022 at 17:29

norris-md

3063 silver badges11 bronze badges

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

2 Comments

Pavel Stehule Over a year ago

the CASE looks little bit obscure. You can write just CASE WHEN description ~ '...' THEN ... ELSE ... END. The ELSE null is nonsense.

norris-md Over a year ago

Agree just did a quit job

Collectives™ on Stack Overflow

Postgres "invalid input syntax for type oid:"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related