How to cast only the part of a table using a single SQL command in PostgreSQL

Question

In a PostgreSQL table I have several information stored as text. It depends on the context described by a type column what type of information is stored. The application is prepared to get by only one command the Id's of the row.

I got into trouble when i tried to compare the information (bigint stored as a string) with an external value (e.g. '9' > '11'). When I tried to cast the column, the datatbase return an error (not all values in the column are castable, e.g. datetime or normal text). Also when I try to cast only the result of a query command, I get a cast error.

I get the table with the castable rows by this command:

SELECT information.id as id, item.information::bigint as item
FROM information
INNER JOIN item
ON information.id = item.informationid
WHERE information.type = 'task'

The resulting rows are showing up only text that is castable. When I throw it into another command it results in an error.

SELECT x.id FROM (
    SELECT information.id as id, item.information::bigint as item
    FROM information
    INNER JOIN item
    ON information.id = item.informationid
    WHERE information.type = 'task'
) AS x
WHERE x.item > '0'::bigint

Accroding to the error, the database tried to cast all rows in the table.

I'd avoid that problem completely by having item_int, item_string and so on - one column per data type. — Laurenz Albe
– Laurenz Albe, Commented Mar 18, 2020 at 16:23

COY · Accepted Answer · 2020-03-18 16:01:59Z

2

Technically, this happens because the optimizer thinks WHERE x.item > '0'::bigint is a much more efficient filter than information.type = 'task'. So in the table scan, the WHERE x.item > '0'::bigint condition is chosen to be the predicate. This thinking is not wrong but will make you fall into this seemingly illogical trouble.

The suggestion by Gordon to use CASE WHEN inf.type = 'task' THEN i.information::bigint END can avoid this, but however it may sometimes ruin your idea to put that as a sub-query and require the same condition to be written twice.

A funny trick I tried is to use OUTER APPLY:

SELECT x.* FROM (SELECT 1 AS dummy) dummy
OUTER APPLY (
    SELECT information.id as id, item.information::bigint AS item
    FROM information
    INNER JOIN item
    ON information.id = item.informationid
    WHERE information.type = 'task'
) x
WHERE x.item > '0'::bigint

Sorry that I only verified the SQL Server version of this. I understand PostgreSQL has no OUTER APPLY, but the equivalent should be:

SELECT x.* FROM (SELECT 1 AS dummy) dummy
LEFT JOIN LATERAL (
    SELECT information.id as id, item.information::bigint AS item
    FROM information
    INNER JOIN item
    ON information.id = item.informationid
    WHERE information.type = 'task'
) x ON true
WHERE x.item > '0'::bigint

(reference is this question)

Finally, a more tidy but less flexible method is add the optimizer hint to turn off it to force the optimizer to run the query as how it is written.

edited Mar 18, 2020 at 16:01

answered Mar 18, 2020 at 15:45

COY

7043 silver badges11 bronze badges

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

3 Comments

Abelisto Over a year ago

Another funny trick: in the PostgreSQL (select 1 AS dummy) dummy could be simplified to (select) dummy ;)

COY Over a year ago

Oh, that's great to know! I never used (select)...But I am just keeping it in case some Oracle people need to take it away :P

minmer Over a year ago

For me the answer of @Gordon Linoff and COY are up to now sufficient. Thanks!

Gordon Linoff · Accepted Answer · 2020-03-18 21:54:20Z

1

This is unfortunate. Try using a case expression:

SELECT inf.id as id,
       (CASE WHEN inf.type = 'task' THEN i.information::bigint END) as item
FROM information inf JOIN
     item i
     ON inf.id = i.informationid
WHERE inf.type = 'task';

There is no guarantee that the WHERE filter is applied before the SELECT. However, CASE does guarantee the order of evaluation, so it is safe.

edited Mar 18, 2020 at 21:54

answered Mar 18, 2020 at 15:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

Belayer Over a year ago

The above is equivalent to "select inf.d as id, i,information::bigint as item". This results from thisthe WHERE condition havng eliminated all inf.type values that are not 'task'. Since WHERE is always processed before the SELECT list the WHEN condition is always true by time it is evaluated.

Gordon Linoff Over a year ago

@Belayer . . . There is not a guarantee that where is processed before select -- or, if there is, please point me to the documentation.

Belayer Over a year ago

@GordenLinoff OK, I admit I cannot find any official documentation either Postgres nor SQL Standards (don't buy it). But a couple good 'references' here and here. But I stand on my contention the result with CASE is the same as direct conversion. If the WHERE executes first the the CASE never sees anything else, if it runs after the select list then it discards any where CASE returned null. Unless the row to be discard causes an exception.

Gordon Linoff Over a year ago

@Belayer . . . Not at all. The CASE only evaluates the THEN for the first true WHEN that is encountered. That is how CASE expressions are defined in the SQL standard.

Collectives™ on Stack Overflow

How to cast only the part of a table using a single SQL command in PostgreSQL

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related