SELECT Sub Query PostgreSQL

Question

I am still new to postgres. I would like to have a SELECT statement in the SELECT portion of the query, but right now I am getting an error.

SELECT cu.user_name, cu.created_date, cu.updated_date, cu.email_address,
       cua.attribute_name, cua.attribute_value,
       (select to_char(to_timestamp(cua.attribute_value / 1000), 'yyyy-mm-dd HH24:MI:SS')) AS Issue_Update
FROM cwd_user cu
INNER JOIN cwd_user_attribute cua ON cu.id = cua.user_id
WHERE cu.user_name LIKE 'perter%'

I am getting the following error:

ERROR: operator does not exist: character varying / integer LINE 3: (select to_char(to_timestamp(cua.attribute_value / 1000), '... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

Apparenlty cua.attribute_value is defined as varchar. The error message is telling you that you can not divide a string by a number.

You need to convert (cast) the varchar to an integer. And you don't need the select at all.

This is the workaround for your current design:

SELECT cu.user_name, 
       cu.created_date, 
       cu.updated_date, 
       cu.email_address, 
       cua.attribute_name, 
       cua.attribute_value,
       to_char(to_timestamp(cua.attribute_value::bigint / 1000), 'yyyy-mm-dd HH24:MI:SS') AS Issue_Update
FROM cwd_user cu
  JOIN cwd_user_attribute cua ON cu.id = cua.user_id
WHERE cu.user_name LIKE 'perter%';

::bigint casts the string to an integer value. It's the Postgres specific syntax for the ANSI SQL cast(... as bigint) operator. See the manual for details.

But this will fail if cua.attribute_value contains values that can not be converted to an integer (an empty string '' would already break this).

The correct solution is to store numbers in integer columns. Do not store numbers as varchar

---

attribute_name and attribute_value sound very much like the (anti) pattern called "Entity-Attribute-Value".

If you are sure that the timestamp information is correct for attributes with a specific name you can do something like this to avoid casting errors:

       CASE WHEN cua.attribute_name = 'timestamp' THEN
         to_char(to_timestamp(cua.attribute_value::bigint / 1000), 'yyyy-mm-dd HH24:MI:SS') 
       END AS Issue_Update

This will return NULL for all rows where attribute_name is not 'timestamp' and the formatted timestamp for those that are. But again this will only work if the values for that attribute are valid numbers (and of course you need to adjust the comparison with the string literal 'timestamp' to use the correct attribute name)

Answer 2

SELECT cu.user_name, cu.created_date, cu.updated_date, 
cu.email_address, cua.attribute_name, cua.attribute_value,
case when (attribute_value like '%True%' or attribute_value like '%False%') then cast(NULL as bigint)
     else CAST(nullif(cua.attribute_value, '') AS bigint)
     end filter_attribute_value,
(select to_char(to_timestamp(
filter_attribute_value / 1000), 'yyyy-mm-dd HH24:MI:SS')) AS Issue_Update
FROM cwd_user cu
INNER JOIN cwd_user_attribute cua ON cu.id = cua.user_id
WHERE cu.user_name LIKE 'perter%'

Answer 3

Try this. But I am not sure if you can convert it to timestamp. You might get another error while converting to timestamp. Anyways give it a try.

    SELECT cu.user_name, cu.created_date, cu.updated_date, 
    cu.email_address, cua.attribute_name, cua.attribute_value,
    (select to_char(to_timestamp(cast(cua.attribute_value as double precision) / 1000), 'yyyy-mm-dd HH24:MI:SS')) AS Issue_Update
    FROM cwd_user cu
    INNER JOIN cwd_user_attribute cua ON cu.id = cua.user_id
    WHERE cu.user_name LIKE 'perter%'