PostgreSQL/Data Type/Boolean — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 10:14, 26 мая 2010

boolean value "t"

postgres=#
postgres=#
postgres=# CREATE TABLE myTable (isbn text, in_stock boolean);
CREATE TABLE
postgres=#
postgres=# INSERT INTO myTable VALUES ("0385121679", true);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("039480001X", "t");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("044100590X", "true");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451198492", false);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0394900014", "0");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0441172717", "1");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451160916");
INSERT 0 1
postgres=#
postgres=# select * from myTable;
    isbn    | in_stock
------------+----------
 0385121679 | t
 039480001X | t
 044100590X | t
 0451198492 | f
 0394900014 | f
 0441172717 | t
 0451160916 |
(7 rows)
postgres=#
postgres=# SELECT * FROM myTable WHERE in_stock != "t";
    isbn    | in_stock
------------+----------
 0451198492 | f
 0394900014 | f
(2 rows)
postgres=#
postgres=# drop table myTable;
DROP TABLE
postgres=#



Checking Boolean values: yes

postgres=#
postgres=# CREATE TABLE myTable (isbn text, in_stock boolean);
CREATE TABLE
postgres=#
postgres=# INSERT INTO myTable VALUES ("0385121679", true);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("039480001X", "t");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("044100590X", "true");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451198492", false);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0394900014", "0");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0441172717", "1");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451160916");
INSERT 0 1
postgres=#
postgres=# -- Checking Boolean values
postgres=#
postgres=# SELECT * FROM myTable WHERE in_stock = "yes";
    isbn    | in_stock
------------+----------
 0385121679 | t
 039480001X | t
 044100590X | t
 0441172717 | t
(4 rows)
postgres=#
postgres=#
postgres=# drop table myTable;
DROP TABLE
postgres=#
postgres=#



Checking for "false" Boolean values

postgres=#
postgres=#
postgres=# CREATE TABLE myTable (isbn text, in_stock boolean);
CREATE TABLE
postgres=#
postgres=# INSERT INTO myTable VALUES ("0385121679", true);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("039480001X", "t");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("044100590X", "true");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451198492", false);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0394900014", "0");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0441172717", "1");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451160916");
INSERT 0 1
postgres=#
postgres=# select * from myTable;
    isbn    | in_stock
------------+----------
 0385121679 | t
 039480001X | t
 044100590X | t
 0451198492 | f
 0394900014 | f
 0441172717 | t
 0451160916 |
(7 rows)
postgres=#
postgres=# -- Checking for "false" Boolean values
postgres=#
postgres=# SELECT * FROM myTable WHERE in_stock = "no";
    isbn    | in_stock
------------+----------
 0451198492 | f
 0394900014 | f
(2 rows)
postgres=#
postgres=#
postgres=#
postgres=# drop table myTable;
DROP TABLE
postgres=#
postgres=#



Compare the value of a boolean field against any of the values

postgres=#
postgres=#
postgres=# CREATE TABLE myTable (isbn text, in_stock boolean);
CREATE TABLE
postgres=#
postgres=# INSERT INTO myTable VALUES ("0385121679", true);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("039480001X", "t");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("044100590X", "true");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451198492", false);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0394900014", "0");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0441172717", "1");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451160916");
INSERT 0 1
postgres=#
postgres=# select * from myTable;
    isbn    | in_stock
------------+----------
 0385121679 | t
 039480001X | t
 044100590X | t
 0451198492 | f
 0394900014 | f
 0441172717 | t
 0451160916 |
(7 rows)
postgres=#
postgres=# -- Compare the value of a boolean field against any of the values
postgres=#
postgres=# SELECT * FROM myTable WHERE NOT in_stock;
    isbn    | in_stock
------------+----------
 0451198492 | f
 0394900014 | f
(2 rows)
postgres=#
postgres=# drop table myTable;
DROP TABLE
postgres=#



Implying Boolean "true"

postgres=#
postgres=# CREATE TABLE myTable (isbn text, in_stock boolean);
CREATE TABLE
postgres=#
postgres=# INSERT INTO myTable VALUES ("0385121679", true);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("039480001X", "t");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("044100590X", "true");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451198492", false);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0394900014", "0");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0441172717", "1");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451160916");
INSERT 0 1
postgres=#
postgres=# -- Implying Boolean "true"
postgres=#
postgres=# SELECT * FROM myTable WHERE in_stock;
    isbn    | in_stock
------------+----------
 0385121679 | t
 039480001X | t
 044100590X | t
 0441172717 | t
(4 rows)
postgres=#
postgres=# drop table myTable;
DROP TABLE
postgres=#



NOT for boolean value

postgres=#
postgres=#
postgres=# CREATE TABLE myTable (isbn text, in_stock boolean);
CREATE TABLE
postgres=#
postgres=# INSERT INTO myTable VALUES ("0385121679", true);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("039480001X", "t");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("044100590X", "true");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451198492", false);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0394900014", "0");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0441172717", "1");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451160916");
INSERT 0 1
postgres=#
postgres=# select * from myTable;
    isbn    | in_stock
------------+----------
 0385121679 | t
 039480001X | t
 044100590X | t
 0451198492 | f
 0394900014 | f
 0441172717 | t
 0451160916 |
(7 rows)
postgres=#
postgres=#
postgres=# SELECT * FROM myTable WHERE NOT in_stock;
    isbn    | in_stock
------------+----------
 0451198492 | f
 0394900014 | f
(2 rows)
postgres=#
postgres=#
postgres=#
postgres=# drop table myTable;
DROP TABLE



NULL will not register as either true or false

postgres=#
postgres=#
postgres=# CREATE TABLE myTable (isbn text, in_stock boolean);
CREATE TABLE
postgres=#
postgres=# INSERT INTO myTable VALUES ("0385121679", true);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("039480001X", "t");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("044100590X", "true");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451198492", false);
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0394900014", "0");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0441172717", "1");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES ("0451160916");
INSERT 0 1
postgres=#
postgres=# select * from myTable;
    isbn    | in_stock
------------+----------
 0385121679 | t
 039480001X | t
 044100590X | t
 0451198492 | f
 0394900014 | f
 0441172717 | t
 0451160916 |
(7 rows)
postgres=#
postgres=# -- NULL will not register as either true or false
postgres=#
postgres=# SELECT * FROM myTable WHERE in_stock IS NULL;
    isbn    | in_stock
------------+----------
 0451160916 |
(1 row)
postgres=#
postgres=#
postgres=# drop table myTable;
DROP TABLE
postgres=#



The difference between true and "true"

postgres=#
postgres=# -- The difference between true and "true"
postgres=#
postgres=# SELECT true AS boolean_t,
postgres-#      "true" AS string_t,
postgres-#       false AS boolean_f,
postgres-#      "false" AS string_f;
 boolean_t | string_t | boolean_f | string_f
-----------+----------+-----------+----------
 t         | true     | f         | false
(1 row)
postgres=#



Using the boolean type

postgres=#
postgres=#
postgres=# -- Using the boolean type
postgres=#
postgres=# CREATE TABLE test1 (a boolean, b text);
CREATE TABLE
postgres=# INSERT INTO test1 VALUES (TRUE, "sic est");
INSERT 0 1
postgres=# INSERT INTO test1 VALUES (FALSE, "non est");
INSERT 0 1
postgres=#
postgres=# SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est
(2 rows)
postgres=#
postgres=# SELECT * FROM test1 WHERE a;
 a |    b
---+---------
 t | sic est
(1 row)
postgres=#
postgres=# drop table test1;
DROP TABLE
postgres=#
Источник — «http://sqle.ru/index.php?title=PostgreSQL/Data_Type/Boolean&oldid=4743»