Re: [GENERAL] Constraint: string length must be 32 chars
Hello, really good advices here! But - On Sun, Oct 17, 2010 at 2:37 AM, Rob Sargent robjsarg...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
In article 4cba2bc4.9030...@darrenduncan.net, Darren Duncan dar...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
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
pId: input type=text name=id size=32 maxlength=32 //p
pLatitude: input type=text name=lat //p
pLongitude: input type=text name=lon //p
pinput type=submit value=Save //p
/form
/body
/html
';
}
?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
Alexander Farber alexander.far...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
Alexander Farber wrote: Hello, really good advices here! But - On Sun, Oct 17, 2010 at 2:37 AM, Rob Sargent robjsarg...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
On Sat, Oct 16, 2010 at 12:15 PM, Alexander Farber
alexander.far...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
Merlin Moncure wrote:
On Sat, Oct 16, 2010 at 12:15 PM, Alexander Farber
alexander.far...@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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
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 robjsarg...@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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
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 '';
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
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 robjsarg...@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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
