1-- 2-- BIT types 3-- 4-- 5-- Build tables for testing 6-- 7CREATE TABLE BIT_TABLE(b BIT(11)); 8INSERT INTO BIT_TABLE VALUES (B'10'); -- too short 9ERROR: bit string length 2 does not match type bit(11) 10INSERT INTO BIT_TABLE VALUES (B'00000000000'); 11INSERT INTO BIT_TABLE VALUES (B'11011000000'); 12INSERT INTO BIT_TABLE VALUES (B'01010101010'); 13INSERT INTO BIT_TABLE VALUES (B'101011111010'); -- too long 14ERROR: bit string length 12 does not match type bit(11) 15--INSERT INTO BIT_TABLE VALUES ('X554'); 16--INSERT INTO BIT_TABLE VALUES ('X555'); 17SELECT * FROM BIT_TABLE; 18 b 19------------- 20 00000000000 21 11011000000 22 01010101010 23(3 rows) 24 25CREATE TABLE VARBIT_TABLE(v BIT VARYING(11)); 26INSERT INTO VARBIT_TABLE VALUES (B''); 27INSERT INTO VARBIT_TABLE VALUES (B'0'); 28INSERT INTO VARBIT_TABLE VALUES (B'010101'); 29INSERT INTO VARBIT_TABLE VALUES (B'01010101010'); 30INSERT INTO VARBIT_TABLE VALUES (B'101011111010'); -- too long 31ERROR: bit string too long for type bit varying(11) 32--INSERT INTO VARBIT_TABLE VALUES ('X554'); 33--INSERT INTO VARBIT_TABLE VALUES ('X555'); 34SELECT * FROM VARBIT_TABLE; 35 v 36------------- 37 38 0 39 010101 40 01010101010 41(4 rows) 42 43-- Concatenation 44SELECT v, b, (v || b) AS concat 45 FROM BIT_TABLE, VARBIT_TABLE 46 ORDER BY 3; 47 v | b | concat 48-------------+-------------+------------------------ 49 | 00000000000 | 00000000000 50 0 | 00000000000 | 000000000000 51 0 | 01010101010 | 001010101010 52 010101 | 00000000000 | 01010100000000000 53 | 01010101010 | 01010101010 54 01010101010 | 00000000000 | 0101010101000000000000 55 01010101010 | 01010101010 | 0101010101001010101010 56 010101 | 01010101010 | 01010101010101010 57 01010101010 | 11011000000 | 0101010101011011000000 58 010101 | 11011000000 | 01010111011000000 59 0 | 11011000000 | 011011000000 60 | 11011000000 | 11011000000 61(12 rows) 62 63-- Length 64SELECT b, length(b) AS lb 65 FROM BIT_TABLE; 66 b | lb 67-------------+---- 68 00000000000 | 11 69 11011000000 | 11 70 01010101010 | 11 71(3 rows) 72 73SELECT v, length(v) AS lv 74 FROM VARBIT_TABLE; 75 v | lv 76-------------+---- 77 | 0 78 0 | 1 79 010101 | 6 80 01010101010 | 11 81(4 rows) 82 83-- Substring 84SELECT b, 85 SUBSTRING(b FROM 2 FOR 4) AS sub_2_4, 86 SUBSTRING(b FROM 7 FOR 13) AS sub_7_13, 87 SUBSTRING(b FROM 6) AS sub_6 88 FROM BIT_TABLE; 89 b | sub_2_4 | sub_7_13 | sub_6 90-------------+---------+----------+-------- 91 00000000000 | 0000 | 00000 | 000000 92 11011000000 | 1011 | 00000 | 000000 93 01010101010 | 1010 | 01010 | 101010 94(3 rows) 95 96SELECT v, 97 SUBSTRING(v FROM 2 FOR 4) AS sub_2_4, 98 SUBSTRING(v FROM 7 FOR 13) AS sub_7_13, 99 SUBSTRING(v FROM 6) AS sub_6 100 FROM VARBIT_TABLE; 101 v | sub_2_4 | sub_7_13 | sub_6 102-------------+---------+----------+-------- 103 | | | 104 0 | | | 105 010101 | 1010 | | 1 106 01010101010 | 1010 | 01010 | 101010 107(4 rows) 108 109-- test overflow cases 110SELECT SUBSTRING('01010101'::bit(8) FROM 2 FOR 2147483646) AS "1010101"; 111 1010101 112--------- 113 1010101 114(1 row) 115 116SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR 2147483646) AS "01010101"; 117 01010101 118---------- 119 01010101 120(1 row) 121 122SELECT SUBSTRING('01010101'::bit(8) FROM -10 FOR -2147483646) AS "error"; 123ERROR: negative substring length not allowed 124SELECT SUBSTRING('01010101'::varbit FROM 2 FOR 2147483646) AS "1010101"; 125 1010101 126--------- 127 1010101 128(1 row) 129 130SELECT SUBSTRING('01010101'::varbit FROM -10 FOR 2147483646) AS "01010101"; 131 01010101 132---------- 133 01010101 134(1 row) 135 136SELECT SUBSTRING('01010101'::varbit FROM -10 FOR -2147483646) AS "error"; 137ERROR: negative substring length not allowed 138--- Bit operations 139DROP TABLE varbit_table; 140CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16)); 141COPY varbit_table FROM stdin; 142SELECT a, b, ~a AS "~ a", a & b AS "a & b", 143 a | b AS "a | b", a # b AS "a # b" FROM varbit_table; 144 a | b | ~ a | a & b | a | b | a # b 145------------------+------------------+------------------+------------------+------------------+------------------ 146 00001111 | 00010000 | 11110000 | 00000000 | 00011111 | 00011111 147 00011111 | 00010001 | 11100000 | 00010001 | 00011111 | 00001110 148 00101111 | 00010010 | 11010000 | 00000010 | 00111111 | 00111101 149 00111111 | 00010011 | 11000000 | 00010011 | 00111111 | 00101100 150 10001111 | 00000100 | 01110000 | 00000100 | 10001111 | 10001011 151 0000000000001111 | 0000000000010000 | 1111111111110000 | 0000000000000000 | 0000000000011111 | 0000000000011111 152 0000000100100011 | 1111111111111111 | 1111111011011100 | 0000000100100011 | 1111111111111111 | 1111111011011100 153 0010010001101000 | 0010010001101000 | 1101101110010111 | 0010010001101000 | 0010010001101000 | 0000000000000000 154 1111101001010000 | 0000010110101111 | 0000010110101111 | 0000000000000000 | 1111111111111111 | 1111111111111111 155 0001001000110100 | 1111111111110101 | 1110110111001011 | 0001001000110100 | 1111111111110101 | 1110110111000001 156(10 rows) 157 158SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b", 159 a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM varbit_table; 160 a | b | a<b | a<=b | a=b | a>=b | a>b | a<>b 161------------------+------------------+-----+------+-----+------+-----+------ 162 00001111 | 00010000 | t | t | f | f | f | t 163 00011111 | 00010001 | f | f | f | t | t | t 164 00101111 | 00010010 | f | f | f | t | t | t 165 00111111 | 00010011 | f | f | f | t | t | t 166 10001111 | 00000100 | f | f | f | t | t | t 167 0000000000001111 | 0000000000010000 | t | t | f | f | f | t 168 0000000100100011 | 1111111111111111 | t | t | f | f | f | t 169 0010010001101000 | 0010010001101000 | f | t | t | t | f | f 170 1111101001010000 | 0000010110101111 | f | f | f | t | t | t 171 0001001000110100 | 1111111111110101 | t | t | f | f | f | t 172(10 rows) 173 174SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM varbit_table; 175 a | a<<4 | b | b>>2 176------------------+------------------+------------------+------------------ 177 00001111 | 11110000 | 00010000 | 00000100 178 00011111 | 11110000 | 00010001 | 00000100 179 00101111 | 11110000 | 00010010 | 00000100 180 00111111 | 11110000 | 00010011 | 00000100 181 10001111 | 11110000 | 00000100 | 00000001 182 0000000000001111 | 0000000011110000 | 0000000000010000 | 0000000000000100 183 0000000100100011 | 0001001000110000 | 1111111111111111 | 0011111111111111 184 0010010001101000 | 0100011010000000 | 0010010001101000 | 0000100100011010 185 1111101001010000 | 1010010100000000 | 0000010110101111 | 0000000101101011 186 0001001000110100 | 0010001101000000 | 1111111111110101 | 0011111111111101 187(10 rows) 188 189DROP TABLE varbit_table; 190--- Bit operations 191DROP TABLE bit_table; 192CREATE TABLE bit_table (a BIT(16), b BIT(16)); 193COPY bit_table FROM stdin; 194SELECT a,b,~a AS "~ a",a & b AS "a & b", 195 a|b AS "a | b", a # b AS "a # b" FROM bit_table; 196 a | b | ~ a | a & b | a | b | a # b 197------------------+------------------+------------------+------------------+------------------+------------------ 198 0000111100000000 | 0001000000000000 | 1111000011111111 | 0000000000000000 | 0001111100000000 | 0001111100000000 199 0001111100000000 | 0001000100000000 | 1110000011111111 | 0001000100000000 | 0001111100000000 | 0000111000000000 200 0010111100000000 | 0001001000000000 | 1101000011111111 | 0000001000000000 | 0011111100000000 | 0011110100000000 201 0011111100000000 | 0001001100000000 | 1100000011111111 | 0001001100000000 | 0011111100000000 | 0010110000000000 202 1000111100000000 | 0000010000000000 | 0111000011111111 | 0000010000000000 | 1000111100000000 | 1000101100000000 203 0000000000001111 | 0000000000010000 | 1111111111110000 | 0000000000000000 | 0000000000011111 | 0000000000011111 204 0000000100100011 | 1111111111111111 | 1111111011011100 | 0000000100100011 | 1111111111111111 | 1111111011011100 205 0010010001101000 | 0010010001101000 | 1101101110010111 | 0010010001101000 | 0010010001101000 | 0000000000000000 206 1111101001010000 | 0000010110101111 | 0000010110101111 | 0000000000000000 | 1111111111111111 | 1111111111111111 207 0001001000110100 | 1111111111110101 | 1110110111001011 | 0001001000110100 | 1111111111110101 | 1110110111000001 208(10 rows) 209 210SELECT a,b,a<b AS "a<b",a<=b AS "a<=b",a=b AS "a=b", 211 a>=b AS "a>=b",a>b AS "a>b",a<>b AS "a<>b" FROM bit_table; 212 a | b | a<b | a<=b | a=b | a>=b | a>b | a<>b 213------------------+------------------+-----+------+-----+------+-----+------ 214 0000111100000000 | 0001000000000000 | t | t | f | f | f | t 215 0001111100000000 | 0001000100000000 | f | f | f | t | t | t 216 0010111100000000 | 0001001000000000 | f | f | f | t | t | t 217 0011111100000000 | 0001001100000000 | f | f | f | t | t | t 218 1000111100000000 | 0000010000000000 | f | f | f | t | t | t 219 0000000000001111 | 0000000000010000 | t | t | f | f | f | t 220 0000000100100011 | 1111111111111111 | t | t | f | f | f | t 221 0010010001101000 | 0010010001101000 | f | t | t | t | f | f 222 1111101001010000 | 0000010110101111 | f | f | f | t | t | t 223 0001001000110100 | 1111111111110101 | t | t | f | f | f | t 224(10 rows) 225 226SELECT a,a<<4 AS "a<<4",b,b>>2 AS "b>>2" FROM bit_table; 227 a | a<<4 | b | b>>2 228------------------+------------------+------------------+------------------ 229 0000111100000000 | 1111000000000000 | 0001000000000000 | 0000010000000000 230 0001111100000000 | 1111000000000000 | 0001000100000000 | 0000010001000000 231 0010111100000000 | 1111000000000000 | 0001001000000000 | 0000010010000000 232 0011111100000000 | 1111000000000000 | 0001001100000000 | 0000010011000000 233 1000111100000000 | 1111000000000000 | 0000010000000000 | 0000000100000000 234 0000000000001111 | 0000000011110000 | 0000000000010000 | 0000000000000100 235 0000000100100011 | 0001001000110000 | 1111111111111111 | 0011111111111111 236 0010010001101000 | 0100011010000000 | 0010010001101000 | 0000100100011010 237 1111101001010000 | 1010010100000000 | 0000010110101111 | 0000000101101011 238 0001001000110100 | 0010001101000000 | 1111111111110101 | 0011111111111101 239(10 rows) 240 241DROP TABLE bit_table; 242-- The following should fail 243select B'001' & B'10'; 244ERROR: cannot AND bit strings of different sizes 245select B'0111' | B'011'; 246ERROR: cannot OR bit strings of different sizes 247select B'0010' # B'011101'; 248ERROR: cannot XOR bit strings of different sizes 249-- More position tests, checking all the boundary cases 250SELECT POSITION(B'1010' IN B'0000101'); -- 0 251 position 252---------- 253 0 254(1 row) 255 256SELECT POSITION(B'1010' IN B'00001010'); -- 5 257 position 258---------- 259 5 260(1 row) 261 262SELECT POSITION(B'1010' IN B'00000101'); -- 0 263 position 264---------- 265 0 266(1 row) 267 268SELECT POSITION(B'1010' IN B'000001010'); -- 6 269 position 270---------- 271 6 272(1 row) 273 274SELECT POSITION(B'' IN B'00001010'); -- 1 275 position 276---------- 277 1 278(1 row) 279 280SELECT POSITION(B'0' IN B''); -- 0 281 position 282---------- 283 0 284(1 row) 285 286SELECT POSITION(B'' IN B''); -- 0 287 position 288---------- 289 0 290(1 row) 291 292SELECT POSITION(B'101101' IN B'001011011011011000'); -- 3 293 position 294---------- 295 3 296(1 row) 297 298SELECT POSITION(B'10110110' IN B'001011011011010'); -- 3 299 position 300---------- 301 3 302(1 row) 303 304SELECT POSITION(B'1011011011011' IN B'001011011011011'); -- 3 305 position 306---------- 307 3 308(1 row) 309 310SELECT POSITION(B'1011011011011' IN B'00001011011011011'); -- 5 311 position 312---------- 313 5 314(1 row) 315 316SELECT POSITION(B'11101011' IN B'11101011'); -- 1 317 position 318---------- 319 1 320(1 row) 321 322SELECT POSITION(B'11101011' IN B'011101011'); -- 2 323 position 324---------- 325 2 326(1 row) 327 328SELECT POSITION(B'11101011' IN B'00011101011'); -- 4 329 position 330---------- 331 4 332(1 row) 333 334SELECT POSITION(B'11101011' IN B'0000011101011'); -- 6 335 position 336---------- 337 6 338(1 row) 339 340SELECT POSITION(B'111010110' IN B'111010110'); -- 1 341 position 342---------- 343 1 344(1 row) 345 346SELECT POSITION(B'111010110' IN B'0111010110'); -- 2 347 position 348---------- 349 2 350(1 row) 351 352SELECT POSITION(B'111010110' IN B'000111010110'); -- 4 353 position 354---------- 355 4 356(1 row) 357 358SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6 359 position 360---------- 361 6 362(1 row) 363 364SELECT POSITION(B'111010110' IN B'11101011'); -- 0 365 position 366---------- 367 0 368(1 row) 369 370SELECT POSITION(B'111010110' IN B'011101011'); -- 0 371 position 372---------- 373 0 374(1 row) 375 376SELECT POSITION(B'111010110' IN B'00011101011'); -- 0 377 position 378---------- 379 0 380(1 row) 381 382SELECT POSITION(B'111010110' IN B'0000011101011'); -- 0 383 position 384---------- 385 0 386(1 row) 387 388SELECT POSITION(B'111010110' IN B'111010110'); -- 1 389 position 390---------- 391 1 392(1 row) 393 394SELECT POSITION(B'111010110' IN B'0111010110'); -- 2 395 position 396---------- 397 2 398(1 row) 399 400SELECT POSITION(B'111010110' IN B'000111010110'); -- 4 401 position 402---------- 403 4 404(1 row) 405 406SELECT POSITION(B'111010110' IN B'00000111010110'); -- 6 407 position 408---------- 409 6 410(1 row) 411 412SELECT POSITION(B'111010110' IN B'000001110101111101011'); -- 0 413 position 414---------- 415 0 416(1 row) 417 418SELECT POSITION(B'111010110' IN B'0000001110101111101011'); -- 0 419 position 420---------- 421 0 422(1 row) 423 424SELECT POSITION(B'111010110' IN B'000000001110101111101011'); -- 0 425 position 426---------- 427 0 428(1 row) 429 430SELECT POSITION(B'111010110' IN B'00000000001110101111101011'); -- 0 431 position 432---------- 433 0 434(1 row) 435 436SELECT POSITION(B'111010110' IN B'0000011101011111010110'); -- 14 437 position 438---------- 439 14 440(1 row) 441 442SELECT POSITION(B'111010110' IN B'00000011101011111010110'); -- 15 443 position 444---------- 445 15 446(1 row) 447 448SELECT POSITION(B'111010110' IN B'0000000011101011111010110'); -- 17 449 position 450---------- 451 17 452(1 row) 453 454SELECT POSITION(B'111010110' IN B'000000000011101011111010110'); -- 19 455 position 456---------- 457 19 458(1 row) 459 460SELECT POSITION(B'000000000011101011111010110' IN B'000000000011101011111010110'); -- 1 461 position 462---------- 463 1 464(1 row) 465 466SELECT POSITION(B'00000000011101011111010110' IN B'000000000011101011111010110'); -- 2 467 position 468---------- 469 2 470(1 row) 471 472SELECT POSITION(B'0000000000011101011111010110' IN B'000000000011101011111010110'); -- 0 473 position 474---------- 475 0 476(1 row) 477 478-- Shifting 479CREATE TABLE BIT_SHIFT_TABLE(b BIT(16)); 480INSERT INTO BIT_SHIFT_TABLE VALUES (B'1101100000000000'); 481INSERT INTO BIT_SHIFT_TABLE SELECT b>>1 FROM BIT_SHIFT_TABLE; 482INSERT INTO BIT_SHIFT_TABLE SELECT b>>2 FROM BIT_SHIFT_TABLE; 483INSERT INTO BIT_SHIFT_TABLE SELECT b>>4 FROM BIT_SHIFT_TABLE; 484INSERT INTO BIT_SHIFT_TABLE SELECT b>>8 FROM BIT_SHIFT_TABLE; 485SELECT POSITION(B'1101' IN b), 486 POSITION(B'11011' IN b), 487 b 488 FROM BIT_SHIFT_TABLE ; 489 position | position | b 490----------+----------+------------------ 491 1 | 1 | 1101100000000000 492 2 | 2 | 0110110000000000 493 3 | 3 | 0011011000000000 494 4 | 4 | 0001101100000000 495 5 | 5 | 0000110110000000 496 6 | 6 | 0000011011000000 497 7 | 7 | 0000001101100000 498 8 | 8 | 0000000110110000 499 9 | 9 | 0000000011011000 500 10 | 10 | 0000000001101100 501 11 | 11 | 0000000000110110 502 12 | 12 | 0000000000011011 503 13 | 0 | 0000000000001101 504 0 | 0 | 0000000000000110 505 0 | 0 | 0000000000000011 506 0 | 0 | 0000000000000001 507(16 rows) 508 509SELECT b, b >> 1 AS bsr, b << 1 AS bsl 510 FROM BIT_SHIFT_TABLE ; 511 b | bsr | bsl 512------------------+------------------+------------------ 513 1101100000000000 | 0110110000000000 | 1011000000000000 514 0110110000000000 | 0011011000000000 | 1101100000000000 515 0011011000000000 | 0001101100000000 | 0110110000000000 516 0001101100000000 | 0000110110000000 | 0011011000000000 517 0000110110000000 | 0000011011000000 | 0001101100000000 518 0000011011000000 | 0000001101100000 | 0000110110000000 519 0000001101100000 | 0000000110110000 | 0000011011000000 520 0000000110110000 | 0000000011011000 | 0000001101100000 521 0000000011011000 | 0000000001101100 | 0000000110110000 522 0000000001101100 | 0000000000110110 | 0000000011011000 523 0000000000110110 | 0000000000011011 | 0000000001101100 524 0000000000011011 | 0000000000001101 | 0000000000110110 525 0000000000001101 | 0000000000000110 | 0000000000011010 526 0000000000000110 | 0000000000000011 | 0000000000001100 527 0000000000000011 | 0000000000000001 | 0000000000000110 528 0000000000000001 | 0000000000000000 | 0000000000000010 529(16 rows) 530 531SELECT b, b >> 8 AS bsr8, b << 8 AS bsl8 532 FROM BIT_SHIFT_TABLE ; 533 b | bsr8 | bsl8 534------------------+------------------+------------------ 535 1101100000000000 | 0000000011011000 | 0000000000000000 536 0110110000000000 | 0000000001101100 | 0000000000000000 537 0011011000000000 | 0000000000110110 | 0000000000000000 538 0001101100000000 | 0000000000011011 | 0000000000000000 539 0000110110000000 | 0000000000001101 | 1000000000000000 540 0000011011000000 | 0000000000000110 | 1100000000000000 541 0000001101100000 | 0000000000000011 | 0110000000000000 542 0000000110110000 | 0000000000000001 | 1011000000000000 543 0000000011011000 | 0000000000000000 | 1101100000000000 544 0000000001101100 | 0000000000000000 | 0110110000000000 545 0000000000110110 | 0000000000000000 | 0011011000000000 546 0000000000011011 | 0000000000000000 | 0001101100000000 547 0000000000001101 | 0000000000000000 | 0000110100000000 548 0000000000000110 | 0000000000000000 | 0000011000000000 549 0000000000000011 | 0000000000000000 | 0000001100000000 550 0000000000000001 | 0000000000000000 | 0000000100000000 551(16 rows) 552 553SELECT b::bit(15), b::bit(15) >> 1 AS bsr, b::bit(15) << 1 AS bsl 554 FROM BIT_SHIFT_TABLE ; 555 b | bsr | bsl 556-----------------+-----------------+----------------- 557 110110000000000 | 011011000000000 | 101100000000000 558 011011000000000 | 001101100000000 | 110110000000000 559 001101100000000 | 000110110000000 | 011011000000000 560 000110110000000 | 000011011000000 | 001101100000000 561 000011011000000 | 000001101100000 | 000110110000000 562 000001101100000 | 000000110110000 | 000011011000000 563 000000110110000 | 000000011011000 | 000001101100000 564 000000011011000 | 000000001101100 | 000000110110000 565 000000001101100 | 000000000110110 | 000000011011000 566 000000000110110 | 000000000011011 | 000000001101100 567 000000000011011 | 000000000001101 | 000000000110110 568 000000000001101 | 000000000000110 | 000000000011010 569 000000000000110 | 000000000000011 | 000000000001100 570 000000000000011 | 000000000000001 | 000000000000110 571 000000000000001 | 000000000000000 | 000000000000010 572 000000000000000 | 000000000000000 | 000000000000000 573(16 rows) 574 575SELECT b::bit(15), b::bit(15) >> 8 AS bsr8, b::bit(15) << 8 AS bsl8 576 FROM BIT_SHIFT_TABLE ; 577 b | bsr8 | bsl8 578-----------------+-----------------+----------------- 579 110110000000000 | 000000001101100 | 000000000000000 580 011011000000000 | 000000000110110 | 000000000000000 581 001101100000000 | 000000000011011 | 000000000000000 582 000110110000000 | 000000000001101 | 000000000000000 583 000011011000000 | 000000000000110 | 100000000000000 584 000001101100000 | 000000000000011 | 110000000000000 585 000000110110000 | 000000000000001 | 011000000000000 586 000000011011000 | 000000000000000 | 101100000000000 587 000000001101100 | 000000000000000 | 110110000000000 588 000000000110110 | 000000000000000 | 011011000000000 589 000000000011011 | 000000000000000 | 001101100000000 590 000000000001101 | 000000000000000 | 000110100000000 591 000000000000110 | 000000000000000 | 000011000000000 592 000000000000011 | 000000000000000 | 000001100000000 593 000000000000001 | 000000000000000 | 000000100000000 594 000000000000000 | 000000000000000 | 000000000000000 595(16 rows) 596 597CREATE TABLE VARBIT_SHIFT_TABLE(v BIT VARYING(20)); 598INSERT INTO VARBIT_SHIFT_TABLE VALUES (B'11011'); 599INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0' AS BIT VARYING(6)) >>1 FROM VARBIT_SHIFT_TABLE; 600INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00' AS BIT VARYING(8)) >>2 FROM VARBIT_SHIFT_TABLE; 601INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'0000' AS BIT VARYING(12)) >>4 FROM VARBIT_SHIFT_TABLE; 602INSERT INTO VARBIT_SHIFT_TABLE SELECT CAST(v || B'00000000' AS BIT VARYING(20)) >>8 FROM VARBIT_SHIFT_TABLE; 603SELECT POSITION(B'1101' IN v), 604 POSITION(B'11011' IN v), 605 v 606 FROM VARBIT_SHIFT_TABLE ; 607 position | position | v 608----------+----------+---------------------- 609 1 | 1 | 11011 610 2 | 2 | 011011 611 3 | 3 | 0011011 612 4 | 4 | 00011011 613 5 | 5 | 000011011 614 6 | 6 | 0000011011 615 7 | 7 | 00000011011 616 8 | 8 | 000000011011 617 9 | 9 | 0000000011011 618 10 | 10 | 00000000011011 619 11 | 11 | 000000000011011 620 12 | 12 | 0000000000011011 621 13 | 13 | 00000000000011011 622 14 | 14 | 000000000000011011 623 15 | 15 | 0000000000000011011 624 16 | 16 | 00000000000000011011 625(16 rows) 626 627SELECT v, v >> 1 AS vsr, v << 1 AS vsl 628 FROM VARBIT_SHIFT_TABLE ; 629 v | vsr | vsl 630----------------------+----------------------+---------------------- 631 11011 | 01101 | 10110 632 011011 | 001101 | 110110 633 0011011 | 0001101 | 0110110 634 00011011 | 00001101 | 00110110 635 000011011 | 000001101 | 000110110 636 0000011011 | 0000001101 | 0000110110 637 00000011011 | 00000001101 | 00000110110 638 000000011011 | 000000001101 | 000000110110 639 0000000011011 | 0000000001101 | 0000000110110 640 00000000011011 | 00000000001101 | 00000000110110 641 000000000011011 | 000000000001101 | 000000000110110 642 0000000000011011 | 0000000000001101 | 0000000000110110 643 00000000000011011 | 00000000000001101 | 00000000000110110 644 000000000000011011 | 000000000000001101 | 000000000000110110 645 0000000000000011011 | 0000000000000001101 | 0000000000000110110 646 00000000000000011011 | 00000000000000001101 | 00000000000000110110 647(16 rows) 648 649SELECT v, v >> 8 AS vsr8, v << 8 AS vsl8 650 FROM VARBIT_SHIFT_TABLE ; 651 v | vsr8 | vsl8 652----------------------+----------------------+---------------------- 653 11011 | 00000 | 00000 654 011011 | 000000 | 000000 655 0011011 | 0000000 | 0000000 656 00011011 | 00000000 | 00000000 657 000011011 | 000000000 | 100000000 658 0000011011 | 0000000000 | 1100000000 659 00000011011 | 00000000000 | 01100000000 660 000000011011 | 000000000000 | 101100000000 661 0000000011011 | 0000000000000 | 1101100000000 662 00000000011011 | 00000000000000 | 01101100000000 663 000000000011011 | 000000000000000 | 001101100000000 664 0000000000011011 | 0000000000000000 | 0001101100000000 665 00000000000011011 | 00000000000000000 | 00001101100000000 666 000000000000011011 | 000000000000000000 | 000001101100000000 667 0000000000000011011 | 0000000000000000000 | 0000001101100000000 668 00000000000000011011 | 00000000000000000000 | 00000001101100000000 669(16 rows) 670 671DROP TABLE BIT_SHIFT_TABLE; 672DROP TABLE VARBIT_SHIFT_TABLE; 673-- Get/Set bit 674SELECT get_bit(B'0101011000100', 10); 675 get_bit 676--------- 677 1 678(1 row) 679 680SELECT set_bit(B'0101011000100100', 15, 1); 681 set_bit 682------------------ 683 0101011000100101 684(1 row) 685 686SELECT set_bit(B'0101011000100100', 16, 1); -- fail 687ERROR: bit index 16 out of valid range (0..15) 688-- Overlay 689SELECT overlay(B'0101011100' placing '001' from 2 for 3); 690 overlay 691------------ 692 0001011100 693(1 row) 694 695SELECT overlay(B'0101011100' placing '101' from 6); 696 overlay 697------------ 698 0101010100 699(1 row) 700 701SELECT overlay(B'0101011100' placing '001' from 11); 702 overlay 703--------------- 704 0101011100001 705(1 row) 706 707SELECT overlay(B'0101011100' placing '001' from 20); 708 overlay 709--------------- 710 0101011100001 711(1 row) 712 713-- This table is intentionally left around to exercise pg_dump/pg_upgrade 714CREATE TABLE bit_defaults( 715 b1 bit(4) DEFAULT '1001', 716 b2 bit(4) DEFAULT B'0101', 717 b3 bit varying(5) DEFAULT '1001', 718 b4 bit varying(5) DEFAULT B'0101' 719); 720\d bit_defaults 721 Table "public.bit_defaults" 722 Column | Type | Collation | Nullable | Default 723--------+----------------+-----------+----------+--------------------- 724 b1 | bit(4) | | | '1001'::"bit" 725 b2 | bit(4) | | | '0101'::"bit" 726 b3 | bit varying(5) | | | '1001'::bit varying 727 b4 | bit varying(5) | | | '0101'::"bit" 728 729INSERT INTO bit_defaults DEFAULT VALUES; 730TABLE bit_defaults; 731 b1 | b2 | b3 | b4 732------+------+------+------ 733 1001 | 0101 | 1001 | 0101 734(1 row) 735 736