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 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
Alexander Farber 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
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 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 ''; while ($row = $select->fetch(PDO::FETCH_ASSOC)) { printf('', $row['id'], $row['lat'], $row['lon']); } print ''; } catch (Exception $e) { print 'Database problem: ' . $e->getMessage(); } } else { header('Content-Type: text/html; charset=utf-8'); print ' Id: Latitude: Longitude: '; } ?> -- 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 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, really good advices here! But - On Sun, Oct 17, 2010 at 2:37 AM, Rob Sargent 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
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 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
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 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
Merlin Moncure wrote: On Sat, Oct 16, 2010 at 12:15 PM, 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): 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
On Sat, Oct 16, 2010 at 12:15 PM, 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): 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
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 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
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: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
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general