PostgreSQL – Why Use ‘t’ and ‘f’ Instead of TRUE and FALSE

datatypespostgresqlpsql

Why does PostgreSQL return 't' and 'f' instead of TRUE and FALSE?

The documentation itself advises us to use TRUE and FALSE when inserting boolean values or making comparisons, but returns a different set of values when selecting the value from the database.

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

Is there a reason for this behavior? Have the PostgreSQL commmunity ever tried to change that?

Best Answer

PostgreSQL does not return these instead of boolean values. It is some clients (for example, psql and pgAdminIII) which represents TRUE with t and FALSE with f - try the same query in another client and you will see something else. See, for example, what DBVisualizer gives you:

I guess the reason for showing t and f is simply sparing space in a command-line client that lacks horizontal scrolling abilities.

Note: I am in no way affiliated to DbVis Software.

Related Solutions

PostgreSQL: DBLink weird permission/connection error

Here is what I think is happening. I don't think it is a bug.

You have a view. That view is somewhat complex but it is a dynamically rewritten query.
The planner is pretty smart. It will ignore UNION clauses where the search criteria will not produce an output result.
You are not executing what you think you are. (EXPLAIN ANALYSE should show you this)

My recommendations:

Wrap the whole result set in a plpgsql function. Wrap that function in the view. OR
Use a WITH clause. I am less sure about this one. I think the with clause might result in running the whole thing consistently but not 100% sure, and it is not guaranteed in future versions. Therefore wrapping in a function is probably better.

I think what the planner is seeing is:

"Oh we don't need to execute the first and third portions of the view because they are not in the output! Let's skip and only run the second portion!" This is a feature, not a bug (and it is what allows table partitioning to be useful).

Postgresql – Basic trigger in PL/pgSQL, to flip a boolean field if another field has text

Your trigger function can be improved:

CREATE OR REPLACE FUNCTION trg_insup_bef_recipe()
  RETURNS TRIGGER AS
$func$
BEGIN
IF TG_TABLE_NAME = 'recipe_' THEN
   IF TG_OP IN ('INSERT', 'UPDATE') THEN
      NEW.is_baked_   := NEW.baking_instructions_ <> '';
      NEW.is_roasted_ := NEW.roasting_instructions_ <> '';
   END IF;
   RETURN NEW;
ELSE
    RAISE EXCEPTION 'This trigger is being used on the wrong table.';
END IF;
END
$func$ LANGUAGE plpgsql;

Major points

Do not use a separate code block without need. Removed the spurious BEGIN .. END.
NEW.baking_instructions_ <> '' is a 100% identical drop-in replacement for
length( NEW.baking_instructions_ ) > 0. Just shorter and faster.
The plpgsql assignment operator is :=, not =.
Removed multiple parentheses to demonstrate they are just noise.

Simplify

Given the trigger definition in your answer (which should be in your question), you can further simplify the trigger function.

If your trigger function is not intended to be generic but for a certain trigger on a certain table (which is the normal case) I suggest to reflect that in the function name instead of adding code and error messages to the function body. Much cheaper and cleaner (IMHO), but that's a matter of taste and style:

CREATE OR REPLACE FUNCTION trg_insup_bef_recipe()
  RETURNS TRIGGER AS
$func$
BEGIN
NEW.is_baked_   := NEW.baking_instructions_ <> '';
NEW.is_roasted_ := NEW.roasting_instructions_ <> '';
END
$func$ LANGUAGE plpgsql;

And since this trigger only has work to do when any of the involved columns have been changed, you only need to trigger it in those cases:

CREATE TRIGGER insup_bef_recipe
BEFORE INSERT
OR UPDATE OF is_baked_, baking_instructions_, is_roasted_, roasting_instructions_
ON recipe_
FOR EACH ROW EXECUTE PROCEDURE trg_insup_bef_recipe();

Simplify further

Do you really need those flags materialized as additional columns? Could be completely (and more reliably) replaced with simple expressions in your queries:

baking_instructions_ <> '' AS is_baked_

Then you don't need a trigger at all. You could create a view with that expression. Or even "generated field". Detailed instructions:
Store common query as column? Computed / calculated columns in PostgreSQL