-2

Is it possible in Postgres to return a column(Resulting from a subquery) only when a condition matches?

Query:

SELECT
  (SELECT NOW()) AS FIRST,

  (SELECT NOW()) AS SECOND

Outputs:

| first      | second      |
|:-----------|------------:|
| timestamp  | timestamp   |


I would like something along those lines: (Pseudo-code)

IF (variable =! 'something') {
  SELECT FIRST, SECOND
} ELSE {
 SELECT FIRST
}
| first      |
|:-----------|
| timestamp  |

closed as unclear what you're asking by Colin 't Hart, mustaccio, Akina, LowlyDBA, Marcello Miorelli Aug 25 at 8:02

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

In general no. A relation (a table or the result of a query) consists of a header and a set of tuples. Once the header is declared, all tuples must conform to that declaration. That said, null is the bottom of all types, so you can use that instead of a value:

SELECT FIRST, CASE WHEN variable <> 'something' then SECOND else NULL END

NULL is the default for a case, so it can be shortened to:

SELECT FIRST, CASE WHEN variable <> 'something' then SECOND END
1

No, not possible. The number of columns of a query must be known to the database before starting to run the query - essentially after it has been parsed and analyzed. - a-horse-with-no-name

Statement output structure must be deterministic. So the aim which you want is not relational. I think there is some global task, and you decide to solve it by described trick. This solution may be wrong. I'd recommend you to formulate the whole task instead the selected way to solve it. - akina

1

I solved my problem executing an anonymous code block.

-- Make sure nothing exists in the current session
DROP TABLE IF EXISTS temporary_flow;

DO LANGUAGE plpgsql $$ DECLARE
BEGIN
IF 'potato' != 'potato' THEN 
    EXECUTE ' CREATE TEMP TABLE temporary_flow AS SELECT NOW() AS first ';
ELSE 
    EXECUTE ' CREATE TEMP TABLE temporary_flow AS SELECT NOW() AS first, NOW() AS second ';
END IF;
END $$;

-- Return data from the temporary table
SELECT * FROM temporary_flow;

Since this is a very scoped and controlled experiment, with a very small set of data, i won't have a problem with memory overflow from the temporary table.

Not the answer you're looking for? Browse other questions tagged or ask your own question.