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.

@a_horse_with_no_name is there a way I can change it to integer before division ? — kya
– kya, Commented Oct 23, 2015 at 6:11
If it's a number, store it as an integer. Do NOT store numbers in varchar columns. — user330315
– user330315, Commented Oct 23, 2015 at 6:12

score 2 · Accepted Answer · 2015-10-23 06:59:37Z

2

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)

edited Oct 23, 2015 at 6:59

answered Oct 23, 2015 at 6:14

user330315

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

3 Comments

kya Over a year ago

Now I get the following error: ERROR: invalid input syntax for integer: "false"

kya Over a year ago

I just figured it out, the attribute_value doesn't only contain numbers stored as varchar. it also contains true, false, human, etc. So I need to clean up the database so things are stored in a proper way

user330315 Over a year ago

@kya: see my edit. If you know that specific attribute names only contain valid numbers you can deal with that using a CASE statement.

anwaar_hell · Accepted Answer · 2015-10-23 07:03:48Z

1

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%'

edited Oct 23, 2015 at 7:03

answered Oct 23, 2015 at 6:15

anwaar_hell

7865 silver badges26 bronze badges

5 Comments

kya Over a year ago

The cua.attribute_value is out of range for integer data type

anwaar_hell Over a year ago

@ Kya try with bigint

kya Over a year ago

I get the following error: ERROR: invalid input syntax for integer: "false"

anwaar_hell Over a year ago

Might be you have empty string in varchar column which is different from NULL and will not be casted ..so try now..

kya Over a year ago

I figured it out. The column contains True, false, some words, and numbers. So I need to clean it up a bit

Utsav · Accepted Answer · 2015-10-23 06:14:12Z

0

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%'

answered Oct 23, 2015 at 6:14

Utsav

8,1632 gold badges22 silver badges40 bronze badges

Collectives™ on Stack Overflow

SELECT Sub Query PostgreSQL

3 Answers 3

3 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related