Обсуждение: Constraint: string length must be 32 chars

Hello,

I'm trying to create a table, where md5 strings will serve as primary keys.
So I'd like to add a constraing that the key length should be 32 chars long
(and contain [a-fA-F0-9] only):

create table gps (
id varchar(32) primary key CONSTRAINT char_length(id)==32,
stamp timestamp DEFAULT current_timestamp,
pos point);

But it fails:

ERROR:  syntax error at or near "("
LINE 2: id varchar(32) primary key CONSTRAINT char_length(id)==32,
                                                         ^
Does anybody please know what's wrong here?

Thank you
Alex

On 16/10/2010 17:15, Alexander Farber wrote:
> Hello,
>
> I'm trying to create a table, where md5 strings will serve as primary keys.
> So I'd like to add a constraing that the key length should be 32 chars long
> (and contain [a-fA-F0-9] only):
>
> create table gps (
> id varchar(32) primary key CONSTRAINT char_length(id)==32,
> stamp timestamp DEFAULT current_timestamp,
> pos point);
>
> But it fails:
>
> ERROR:  syntax error at or near "("
> LINE 2: id varchar(32) primary key CONSTRAINT char_length(id)==32,
>                                                           ^
> Does anybody please know what's wrong here?

 From (somewhat hazy) memory, I think the syntax is something like this:

      ... CONSTRAINT length_check CHECK char_length(id) = 32, ...

Note also that the equality operator is a single "=", not "==" as you
have above.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

snake=> create table gps (
id varchar(32) primary key CONSTRAINT id_length char_length(id)=32,
stamp timestamp DEFAULT current_timestamp,
pos point);
ERROR:  syntax error at or near "char_length"
LINE 2: id varchar(32) primary key CONSTRAINT id_length char_length(...
                                                        ^

snake=> create table gps (
id varchar(32) primary key CHECK id_length char_length(id)=32,
stamp timestamp DEFAULT current_timestamp,
pos point);
ERROR:  syntax error at or near "id_length"
LINE 2: id varchar(32) primary key CHECK id_length char_length(id)=3...
                                         ^

snake=> create table gps (
id varchar(32) primary key CHECK char_length(id)=32,
stamp timestamp DEFAULT current_timestamp,
pos point);
ERROR:  syntax error at or near "char_length"
LINE 2: id varchar(32) primary key CHECK char_length(id)=32,
                                         ^

Sorry, any ideas? (I know it's a stupid question)
Alex

On 16/10/2010 17:48, Alexander Farber wrote:
> snake=>  create table gps (
> id varchar(32) primary key CONSTRAINT id_length char_length(id)=32,
> stamp timestamp DEFAULT current_timestamp,
> pos point);
> ERROR:  syntax error at or near "char_length"
> LINE 2: id varchar(32) primary key CONSTRAINT id_length char_length(...
>                                                          ^
>
> snake=>  create table gps (
> id varchar(32) primary key CHECK id_length char_length(id)=32,
> stamp timestamp DEFAULT current_timestamp,
> pos point);
> ERROR:  syntax error at or near "id_length"
> LINE 2: id varchar(32) primary key CHECK id_length char_length(id)=3...
>                                           ^
>
> snake=>  create table gps (
> id varchar(32) primary key CHECK char_length(id)=32,
> stamp timestamp DEFAULT current_timestamp,
> pos point);
> ERROR:  syntax error at or near "char_length"
> LINE 2: id varchar(32) primary key CHECK char_length(id)=32,
>                                           ^
>
> Sorry, any ideas? (I know it's a stupid question)
> Alex
>

None of those correspond to what I suggested! :-)
Here is is again:

     ... CONSTRAINT length_check CHECK char_length(id) = 32, ...

Note the keywords "constraint" and "check" both present. Not that I'm
saying I'm correct - I'm going on memory.... If in doubt consult the
docs under "create table".

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 10/16/2010 12:48 PM, Alexander Farber wrote:
> snake=>  create table gps (
> id varchar(32) primary key CONSTRAINT id_length char_length(id)=32,
> stamp timestamp DEFAULT current_timestamp,
> pos point);
> ERROR:  syntax error at or near "char_length"
> LINE 2: id varchar(32) primary key CONSTRAINT id_length char_length(...

> Sorry, any ideas? (I know it's a stupid question)

See the documentation section 5.3.1 Check Constraints.  The condition
needs to be in parentheses.  And in order to name your constraint, you
must use the CONSTRAINT variant.

--
Guy Rouillier

On Sat, Oct 16, 2010 at 12:15 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> Hello,
>
> I'm trying to create a table, where md5 strings will serve as primary keys.
> So I'd like to add a constraing that the key length should be 32 chars long
> (and contain [a-fA-F0-9] only):

why don't you use the bytea type, and cut the key size down 50%?  You
can always format it going out the door if you want it displayed hex.
Besides being faster, you get to skip the 'is hex' regex.

create table foo(id bytea check(length(id) = 16));
insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
insert into foo values (digest('b', 'md5')); -- if using pgcrypto (preferred)

select encode(id, 'hex') from foo;

merlin

Merlin Moncure wrote:
> On Sat, Oct 16, 2010 at 12:15 PM, Alexander Farber
> <alexander.farber@gmail.com> wrote:
>
>> Hello,
>>
>> I'm trying to create a table, where md5 strings will serve as primary keys.
>> So I'd like to add a constraing that the key length should be 32 chars long
>> (and contain [a-fA-F0-9] only):
>>
>
> why don't you use the bytea type, and cut the key size down 50%?  You
> can always format it going out the door if you want it displayed hex.
> Besides being faster, you get to skip the 'is hex' regex.
>
> create table foo(id bytea check(length(id) = 16));
> insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
> insert into foo values (digest('b', 'md5')); -- if using pgcrypto (preferred)
>
> select encode(id, 'hex') from foo;
>
> merlin
>
>

Why not the support uuid type instead.  Aren't md5s only as unique as
the source?  i.e. The same value hashed results in the same md5, no?

Thank you for your advices.

I actually would like to store GPS coordinates, but anonymously,
so I was going to save md5(my_secret+IMEI) coming from a mobile...

I have to lookup if uuid is supported there

Regards
Alex

On Sat, Oct 16, 2010 at 11:08 PM, Rob Sargent <robjsargent@gmail.com> wrote:
> Merlin Moncure wrote:
>> why don't you use the bytea type, and cut the key size down 50%?  You
>> can always format it going out the door if you want it displayed hex.
>> Besides being faster, you get to skip the 'is hex' regex.
>>
>> create table foo(id bytea check(length(id) = 16));
>> insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
>> insert into foo values (digest('b', 'md5')); -- if using pgcrypto
>> (preferred)
>>
>> select encode(id, 'hex') from foo;
>
> Why not the support uuid type instead.  Aren't md5s only as unique as the
> source?  i.e. The same value hashed results in the same md5, no?

Alexander Farber wrote:
> I'm trying to create a table, where md5 strings will serve as primary keys.
> So I'd like to add a constraing that the key length should be 32 chars long
> (and contain [a-fA-F0-9] only):
>
> create table gps (
> id varchar(32) primary key CONSTRAINT char_length(id)==32,
> stamp timestamp DEFAULT current_timestamp,
> pos point);

If you want to use a text type for this and you are restricting the character
repertoire anyway, which presumably you'd need a regex for, then use the same
regex to restrict the length too.

Adjusting your example:

   create table gps (
     id text primary key CONSTRAINT id ~ '^[a-fA-F0-9]{32}$',
     stamp timestamp DEFAULT current_timestamp,
     pos point
   );

But I would further restrict this to just upper or just lowercase, so that the
values compare correctly as text; you then have to upper/lower your inputs:

   create table gps (
     id text primary key CONSTRAINT id ~ '^[A-F0-9]{32}$',
     stamp timestamp DEFAULT current_timestamp,
     pos point
   );

I would further recommend turning the above into a separate data type,
especially if you'd otherwise be using that constraint in several places, like this:

   CREATE DOMAIN md5text
     AS text
     CHECK (
         VALUE IS NOT NULL
         AND
         VALUE ~ '^[A-F0-9]{32}$'
     )
     DEFAULT '00000000000000000000000000000000';

   create table gps (
     id md5text primary key,
     stamp timestamp DEFAULT current_timestamp,
     pos point
   );

This all being said, I would go with the other advice you mentioned and use a
bitstring or numeric type to represent the md5 rather than using text.

-- Darren Duncan

I just read the "anonymously" part, so I take it you have ruled out
recording the given coordinate components directly, in multiple columns
presumably?  Otherwise it seems you could then do a) a composite key and
b) queries directly against coordinate values.



Alexander Farber wrote:
> Thank you for your advices.
>
> I actually would like to store GPS coordinates, but anonymously,
> so I was going to save md5(my_secret+IMEI) coming from a mobile...
>
> I have to lookup if uuid is supported there
>
> Regards
> Alex
>
> On Sat, Oct 16, 2010 at 11:08 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>
>> Merlin Moncure wrote:
>>
>>> why don't you use the bytea type, and cut the key size down 50%?  You
>>> can always format it going out the door if you want it displayed hex.
>>> Besides being faster, you get to skip the 'is hex' regex.
>>>
>>> create table foo(id bytea check(length(id) = 16));
>>> insert into foo values (decode(md5('a'), 'hex')); -- if not using pgcrypto
>>> insert into foo values (digest('b', 'md5')); -- if using pgcrypto
>>> (preferred)
>>>
>>> select encode(id, 'hex') from foo;
>>>
>> Why not the support uuid type instead.  Aren't md5s only as unique as the
>> source?  i.e. The same value hashed results in the same md5, no?
>>
>
>

Hello, really good advices here! But -

On Sun, Oct 17, 2010 at 2:37 AM, Rob Sargent <robjsargent@gmail.com> wrote:
> I just read the "anonymously" part, so I take it you have ruled out
> recording the given coordinate components directly, in multiple columns
> presumably?  Otherwise it seems you could then do a) a composite key and b)
> queries directly against coordinate values.

what do you mean here? Do you suggest using line segments
instead of points in my records or something else?

Regards
Alex

In article <4CBA2BC4.9030105@darrenduncan.net>,
Darren Duncan <darren@darrenduncan.net> writes:

> I would further recommend turning the above into a separate data type,
> especially if you'd otherwise be using that constraint in several
> places, like this ...

FWIW, the shatypes contrib package includes a binary md5 datatype.

Hello again,

I have 1 more question please:

how do you select the "x" and "y" parts of a point data type?

Can't find it in
http://www.postgresql.org/docs/8.4/interactive/functions-geometry.html

For example, I have this table with a "pos" column:

snake=> \d gps
                  Table "public.gps"
 Column |            Type             |   Modifiers
--------+-----------------------------+---------------
 id     | bytea                       |
 stamp  | timestamp without time zone | default now()
 pos    | point                       | not null
Check constraints:
    "gps_id_check" CHECK (length(id) = 16)

snake=> select encode(id,'hex') as id, pos from gps;
                id                |   pos
----------------------------------+----------
 0cc175b9c0f1b6a831c399e269772661 | (51,7)
 0cc175b9c0f1b6a831c399e269772661 | (51,7)
 92eb5ffee6ae2fec3ad71c777531578f | (51,7)
 92eb5ffee6ae2fec3ad71c777531578f | (51.3,7)
(4 rows)

but my problem is, that I'd prefer to fetch
the x part of pos as "lat" and y part as "lon",
so I can print them in my php script (source below).

Thank you
Alex

<?php

$id  = trim($_REQUEST['id']);
$lat = strtr(trim($_REQUEST['lat']), ',', '.');
$lon = strtr(trim($_REQUEST['lon']), ',', '.');

if (preg_match('/^[a-fA-F0-9]{32}$/', $id) &&
    preg_match('/^[+-]?[0-9.]+$/', $lat) &&
    preg_match('/^[+-]?[0-9.]+$/', $lon)) {

        try {
                $db = new PDO('pgsql:host=/tmp', 'user', 'password');
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

                $insert = $db->prepare("insert into gps (id, pos)
values (decode(?, 'hex'), point(?, ?))");
                $insert->execute($id, $lat, $lon);

                $select = $db->prepare("select encode(id, 'hex') as
id, stamp, pos from gps");
                $select->execute();

                header('Content-Type: text/xml; charset=utf-8');
                print '<?xml version="1.0"?><gps>';
                while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
                        printf('<pos id="%s" lat="%f" lon="%f" />',
$row['id'], $row['lat'], $row['lon']);
                }
                print '</gps>';
        } catch (Exception $e) {
                print 'Database problem: ' . $e->getMessage();
        }

} else {
        header('Content-Type: text/html; charset=utf-8');
        print '<html>
<body>
<form method="post">
<p>Id: <input type="text" name="id" size=32 maxlength=32 /></p>
<p>Latitude: <input type="text" name="lat" /></p>
<p>Longitude: <input type="text" name="lon" /></p>
<p><input type="submit" value="Save" /></p>
</form>
</body>
</html>
';

}

?>

Alexander Farber <alexander.farber@gmail.com> writes:
> how do you select the "x" and "y" parts of a point data type?

pointval[0] and pointval[1] --- this is mentioned somewhere in the fine
print in the "geometric functions and operators" page, IIRC.

            regards, tom lane

Alexander Farber wrote:
> Hello, really good advices here! But -
>
> On Sun, Oct 17, 2010 at 2:37 AM, Rob Sargent <robjsargent@gmail.com> wrote:
>
>> I just read the "anonymously" part, so I take it you have ruled out
>> recording the given coordinate components directly, in multiple columns
>> presumably?  Otherwise it seems you could then do a) a composite key and b)
>> queries directly against coordinate values.
>>
>
> what do you mean here? Do you suggest using line segments
> instead of points in my records or something else?
>
> Regards
> Alex
>
>


Sorry Alexander, I have to confess here and now that I'm not familiar
with GPS data.  My presumption was that it was represented as something
like latitude, longitude (,alitutde?, others?), and that while those
would surely hash nicely with md5 I thought you would be loosing the
inherent information of the position.