Question 90591 in PostgreSQL Question posted in
The official documentation can be found here.

How to insert a boolean value to a table in PostgreSQL?

I come from MySQL to PostgreSQL then, I created test table with BOOLEAN state column in PostgreSQL as shown below:

CREATE TABLE test (
  state BOOLEAN -- Here
);

But, I couldn’t insert TRUE with 1 and FALSE with 0 to test table as shown below even though the SQL queries below work in MySQL:

INSERT INTO test VALUES (1);

INSERT INTO test VALUES (0);

Then, I got the error below:

ERROR:  column "state" is of type boolean but expression is of type integer

So, how to insert a boolean value to a table?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    You can insert TRUE with '1' and FALSE with '0' in PostgreSQL as shown below:

    INSERT INTO test VALUES ('1');

    INSERT INTO test VALUES ('0');

    Then, t which is TRUE and f which is FALSE are inserted to test table as shown below:

    postgres=# SELECT * FROM test;
 state
-------
 t
 f
(2 rows)

    In addtion, these SQL queries below also work to insert TRUE and FALSE to test table as shown below:

    TRUE:

    INSERT INTO test VALUES (tRuE);

    INSERT INTO test VALUES ('TrUe');

    INSERT INTO test VALUES ('T');

    INSERT INTO test VALUES ('t');

    INSERT INTO test VALUES ('YeS');

    INSERT INTO test VALUES ('Y');

    INSERT INTO test VALUES ('y');

    INSERT INTO test VALUES ('oN');

    FALSE:

    INSERT INTO test VALUES (fAlSe);

    INSERT INTO test VALUES ('FaLsE');

    INSERT INTO test VALUES ('F');

    INSERT INTO test VALUES ('f');

    INSERT INTO test VALUES ('No');

    INSERT INTO test VALUES ('N');

    INSERT INTO test VALUES ('n');

    INSERT INTO test VALUES ('oFf');

  2. 0

    The standard way to insert boolean values in PostgreSQL is to use the literal boolean values true or false or any expression that evaluates to a boolean.

    For example:

    create table test (
  state boolean
);

insert into test (state) values (true);
insert into test (state) values (false);
insert into test (state) values (3 * 5 > 10);    

select * from test;

    Returns:

    state
-----
t
f
t

    See running example in db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search