Re: [HACKERS] named generic constraints [feature request]
no - -- is line comment in SQL - it same like // in C++ sorry didn't see this was updated. I know -- is a comment I mean in sql means NOT your function name is emptystr which implies it looks for an emptystr and returns true if the string is found to be empty (at least in my mind). so if you want to create a contrstraint of not empty you'd write NOT emptystr(col) however the way you wrote it would only return true if the string was NOT empty which is a double negative meaning that it is empty thereby rejecting all but empty strings. my final function that I wrote ended up looking like this (note: I didn't specify to include whitespace in my original explanation. CREATE OR REPLACE FUNCTION empty(TEXT) RETURNS bool AS $$ SELECT $1 ~ '^[[:space:]]*$'; $$ LANGUAGE sql IMMUTABLE; COMMENT ON FUNCTION empty(TEXT) IS 'Find empty strings or strings containing only whitespace'; which I'm using like this (note: this is not the full table) CREATE TABLE users ( user_name TEXTNOT NULL UNIQUE CHECK ( NOT empty( user_name )) ); I still wish I could write,something like CREATE CONSTRAINT empty CHECK ( VALUE NOT ~ '^[[:space:]]*$';) CREATE TABLE users ( user_name TEXTNOT NULL UNIQUE CHECK ( NOT empty ) ); CREATE TABLE roles ( role_name TEXTNOT NULL UNIQUE CHECK ( NOT empty) ); -- Caleb Cushing http://xenoterracide.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] named generic constraints [feature request]
So last time I checked this wasn't possible (at least not that anyone
has told me). I'd like to be able to create constraints that aren't
tied to a specific table/column.
I think that the syntax would look something like this
CREATE CONSTRAINT empty CHECK (VALUE = '\0' );
this should allow us to do thinks like
CREATE TABLE users (
username TEXT NOT empty
);
the example from create domain (modified) is also pretty good
CREATE CONSTRAINT zip CHECK(
VALUE ~ '^\\d{5}$'
OR VALUE ~ '^\\d{5}-\\d{4}$'
);
--
Caleb Cushing
http://xenoterracide.blogspot.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named generic constraints [feature request]
On Mon, Nov 23, 2009 at 4:17 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello do you know domains? It is very similar to your proposal. obviously since I cited it. constraint cannot be part of expression. why not? NOT NULL is a contraint, UNIQUE is a contstraint. CREATE OR REPLACE FUNCTION emptystr(text) RETURNS bool AS $$ SELECT $1 ''; -- it is SQL not C $$ LANGUAGE sql; CREATE TABLE users( username TEXT CHECK (NOT emptystr(username)), ... this is probably the 'best' current solution. however, I'd like to be able to not have to name the column for every constraint. and domains only seem right if it's something, like a zip code, that has a very specific set of rules, that is in reality it's own type. where specifying something like 'empty' feels as generic (and arbitrary?) as null. empty is not the only example (I'm sure), just the best I can think of. p.s. Is it related to ANSI SQL? not to my knowledge (can't say that it isn't though, I've never read the standard). -- Caleb Cushing http://xenoterracide.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named generic constraints [feature request]
CREATE OR REPLACE FUNCTION emptystr(text) RETURNS bool AS $$ SELECT $1 ''; -- it is SQL not C $$ LANGUAGE sql; CREATE TABLE users( username TEXT CHECK (NOT emptystr(username)), although I'm not going to continue discussing the request. this code as the opposite desired effect. it should be SELECT $1 = ''; -- you have a double negative -- Caleb Cushing http://xenoterracide.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] single bit integer (TINYINT) revisited for 8.5
I'd like to see this topic revisited since as far as I can see it hasn't been seriously discussed in years. I believe the main arguments against are why do we need more more numeric datatypes and increased maintenance. It would seem to me that a tinyint datatype maintenance wise would get all the same updates as the other int types, making it only a slight increase in maintenance. I think there was 1 more reason but I can't find the original thread now. most (if not all?) of posgresql's major competitor's (mysql, sql server, db2, etc) support a single bit integer datatype. it would bring increased compatibility with existing mysql apps esp, making them easier to port. It (in theory?) should also bring a speed enhancement where usable since it would take less disk space. A couple of times I've been told you don't need tinyint, use boolean which is not true, several projects I've worked on I've needed and integer field that supports number within a small range 0-5 1-10 1-100 or something similar. I end up using smallint but it's range is huge for the actual requirements. -- Caleb Cushing http://xenoterracide.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] single bit integer (TINYINT) revisited for 8.5
On Wed, Jul 1, 2009 at 11:41 AM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: I think you mean byte where you've said bit. you're correct. I'm being a nerf. Boolean would be adequate for a single bit, and I haven't (so far) seen any database which supports both a single-bit type and a boolean. wasn't aware of that. I'm admittedly most familiar with sqlite, postgres, and mysql Many databases support a TINYINT type as a single-byte value, although I'm not sure there's consistency on whether that's a signed or unsigned value. wouldn't any implementation in pg support both? -- Caleb Cushing http://xenoterracide.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] single bit integer (TINYINT) revisited for 8.5
On Wed, Jul 1, 2009 at 12:09 PM, Josh Berkusj...@agliodbs.com wrote: The main reason not to have one is that given byte-alignment, 95% of the time using a tinyint would save no actual disk space or memory over just using INT2 (or indeed INT4). I'll point out that the MySQLers are enamored of the 3-byte integer, which even on MySQL saves zero space or memory over using a 4-byte. Just because people like it doesn't mean it works. So the only real point in having an INT1 would be if you had a table with a lot of them in a row; an unusual but not unheard-of design. Still, that makes this feature less-than-critical for most users. The primary type of field that I see it used as involves some sort of statistics collection. like ratings, or maybe strongly agree - strongly disagree (this could be in hundreds of colums), even collecting gender's is appropriate if I recall.. as the iso for genders is an integer with like 4 possible value's. At least this is my thought, and a lot of these things end up in the same table. It's not critical, but I'd suggest the benefits outweigh the costs. But ... the nice thing about PostgreSQL is that data types can be loaded at runtime. Which means that you don't need INT1 in core for it to be useful to you and others; just write the data type and put it on pgFoundry. Then submit it for /contrib for 8.5, and we'll see how popular the idea is. I suppose I can see what I can do to this end (since it seems to be the consensus) although it'll take me a while given I've no idea what I'm doing... I can read C better than I write it, which doesn't say much. On the other hand I'd think this would be on the list of 'easiest things to do' which means I should be able to get it done. Overall, I'm not keen on it. For the handful of times when peformance-optimization-by-datatype makes sense, there's a large number where it's develpers who have no idea what they're doing. We should be moving in the direction of having the database engine take care of space optimizations, not having the user do it. eh... not sure I agree on that 100%... but to some degree that's what sql is for... at the same time that kinda sounds like sqlite's more dynamic like typing. but it is another discussion -- Caleb Cushing http://xenoterracide.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
