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

Question

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?

Answer 1

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');