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

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?

CodePudding user response：

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

CodePudding user response：

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.

TRUE:

FALSE:

`TRUE`:

`FALSE`: