(7.1) BIT datatype
Some SQL92 functionality is missing from the BIT and VARBIT types.
It should be possible to enter hexadecimal values as:
B'[<bit>...]'[{<separator>...'[<bit...]'}...]
X'[<hexdigit>...]'[{<separator>...'[<hexdigit...]'}...]
(Cannan and Otten: SQL - The Standard Handbook, p.38)
but the hexadeximal form is not accepted.
For example:
bray=# \d junk
Table "junk"
Attribute | Type | Modifier
-----------+--------------+----------
id | character(4) | not null
flag1 | bit(1) |
flags | bit(8) |
Index: junk_pkeybray=# insert into junk values ('BBBB',B'0',X'FF');
ERROR: Attribute 'flags' is of type 'bit' but expression is of type 'int4'
You will need to rewrite or cast the expression
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For a child will be born to us, a son will be given to
us; And the government will rest on His shoulders; And
His name will be called Wonderful Counsellor, Mighty
God, Eternal Father, Prince of Peace."
Isaiah 9:6
Oliver Elphick writes:
Some SQL92 functionality is missing from the BIT and VARBIT types.
It should be possible to enter hexadecimal values as:
B'[<bit>...]'[{<separator>...'[<bit...]'}...]
X'[<hexdigit>...]'[{<separator>...'[<hexdigit...]'}...](Cannan and Otten: SQL - The Standard Handbook, p.38)
but the hexadeximal form is not accepted.
This was omitted because in SQL99 the X'1001' notation also serves as a
binary large object value under certain circumstances. Unfortunately,
it's not exactly known what those circumstances are.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut wrote:
Oliver Elphick writes:
Some SQL92 functionality is missing from the BIT and VARBIT types.
It should be possible to enter hexadecimal values as:
...
This was omitted because in SQL99 the X'1001' notation also serves as a
binary large object value under certain circumstances. Unfortunately,
it's not exactly known what those circumstances are.
It seems a pity that we don't support the SQL92 version at least.
How much more convenient it is to enter X'FE1D' than B'1111111000011101'
(did I get that right?)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"For a child will be born to us, a son will be given to
us; And the government will rest on His shoulders; And
His name will be called Wonderful Counsellor, Mighty
God, Eternal Father, Prince of Peace."
Isaiah 9:6
Import Notes
Reply to msg id not found: MessagefromPeterEisentrautpeter_e@gmx.netofThu21Dec2000231125+0100.Pine.LNX.4.30.0012212306130.775-100000@peter.localdomain | Resolved by subject fallback
Some SQL92 functionality is missing from the BIT and VARBIT types.
It should be possible to enter hexadecimal values as:
B'[<bit>...]'[{<separator>...'[<bit...]'}...]
X'[<hexdigit>...]'[{<separator>...'[<hexdigit...]'}...](Cannan and Otten: SQL - The Standard Handbook, p.38)
but the hexadeximal form is not accepted.
I have been using the BIT and VARBIT types in Postgres 7.0.3 (undocumented I
believe), and I note that the _input_ format is as follows:
update blah set flags='b101001'; -- Binary
update blah set flags='xff45'; -- Hex
But the _output_ format (for varbit) is always:
B'1010110'
Has any of this changed in 7.1?
Chris
"Christopher Kings-Lynne" wrote:
I have been using the BIT and VARBIT types in Postgres 7.0.3 (undocumented I
believe), and I note that the _input_ format is as follows:update blah set flags='b101001'; -- Binary
That is still accepted.
update blah set flags='xff45'; -- Hex
That is not.
But the _output_ format (for varbit) is always:
B'1010110'
bray=# select * from junk;
id | flag1 | flags | flags2
------+-------+----------+--------
AAAA | 1 | 11000101 |
BBBB | 0 | 00111010 |
cccc | 0 | 01101100 | 11001
dddd | 0 | 01100000 |
Has any of this changed in 7.1?
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And there were in the same country shepherds abiding
in the field, keeping watch over their flock by night.
And, lo, the angel of the Lord came upon them, and the
glory of the Lord shone around them; and they were
sore afraid. And the angel said unto them, " Fear not;
for behold I bring you good tidings of great joy which
shall be to all people. For unto you is born this day
in the city of David a Saviour, which is Christ the
Lord." Luke 2:8-11
Christopher Kings-Lynne wrote:
Some SQL92 functionality is missing from the BIT and VARBIT types.
It should be possible to enter hexadecimal values as:
B'[<bit>...]'[{<separator>...'[<bit...]'}...]
X'[<hexdigit>...]'[{<separator>...'[<hexdigit...]'}...](Cannan and Otten: SQL - The Standard Handbook, p.38)
but the hexadeximal form is not accepted.
As Peter noted: the standard does not say whether X'..' should be a
blob, a bit or a varbit type. Converting it into an integer seems to me
to be the least reasonable solution, albeit the historical one, as
larger bitmasks will not fit. With TOAST the bit type can contain quite
large bit strings, so a case could be made for converting to bit
(especially as the blob implementation has reputedly got some problems).
I have been using the BIT and VARBIT types in Postgres 7.0.3 (undocumented I
believe), and I note that the _input_ format is as follows:update blah set flags='b101001'; -- Binary
update blah set flags='xff45'; -- Hex
Yes, that was done due to limitations in the parser. These have been
fixed and this format should not be used any longer.
But the _output_ format (for varbit) is always:
B'1010110'
The SQL standard says nothing about the output of the BIT datatypes. The
C-routines to interpret both the B'..' and X'..' formats, as well as
output routines to generate both are implemented and included. The
problem is that a default had to be chosen, and the B'..' format seemed
more useful for people using small bit masks.
I don't know whether a function was defined to return an X'..' string of
a bit mask. I don't have one of the more recent Postgres snapshots down
at the moment. Peter E. may know, as he did all the integration.
An alternative may be to add a 'SET variable' to psql to govern the
output format, but there seem to be too many of those already.
Adriaan