Re: [PERFORM] Fixed width rows faster?
If you have an actual business-logic requirement to restrict a field to no more than N characters, then by all means use varchar(N); that's what it's for. But I agree with what I think Josh meant: there is very seldom any non-broken reason to have a hard upper limit on string lengths. If you think you need varchar(N) you should stop and ask why exactly. If you cannot give a specific, coherent reason why the particular value of N that you're using is the One True Length for the field, then you really need to think twice. One nice reason to have like VARCHAR(4096) or whatever is that if there is a bug in your website and you forget to length check some user input, it stops them from screwing you by uploading megs and megs of data into a 'firstname' field, say. Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Fixed width rows faster?
On Sat, 2004-03-06 at 20:16, Mike Nolan wrote: Actually, I don't. Good reason to have a check constraint on it though (hint, check constraints can be changed while column types cannot be, at this moment). Is there a way to copy a table INCLUDING the check constraints? If not, then that information is lost, unlike varchar(n). No, not constraints. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Fixed width rows faster?
Mike Nolan wrote: Is there a way to copy a table INCLUDING the check constraints? If not, then that information is lost, unlike varchar(n). pg_dump -t should work fine, unless I'm misunderstanding you. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Fixed width rows faster?
On Sat, 2004-03-06 at 21:26, Mike Nolan wrote: Mike Nolan wrote: Is there a way to copy a table INCLUDING the check constraints? If not, then that information is lost, unlike varchar(n). pg_dump -t should work fine, unless I'm misunderstanding you. I was specifically referring to doing it in SQL. The COPY command goes from table to file or file to table, the CREATE TABLE ... SELECT loses the check constraints. Is there no SQL command that allows me to clone a table, including check constraints? There is not in the spec or in PostgreSQL. Although, this may be a relevant extension to the LIKE structure inheritance in 200N spec (partly implemented 7.4). ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Fixed width rows faster?
On Fri, 5 Mar 2004, Jeff wrote: Is there any performance advantage to using a fixed width row (with PG)? As far as I know there is only a small win when you want to extract some field from a tuple and with variable width fields you have to walk to the correct field. But this is a small performance problem unless you have very many variable size columns in the table. different databases and we have also some custom software here that uses fixed width rows to be able to hit row N in O(1) This can not happen in pg since there is no row N. Every transaction can have a different view of the table, some rows are visible and some others are not. To find row N you have to walk from the start and inspect every tuple to see if it's visible to this transaction or not. -- /Dennis Björklund ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Fixed width rows faster?
Mike Nolan [EMAIL PROTECTED] writes: Frankly, the only reason to use anything other than TEXT is compatibility with other databases and applications. You don't consider a requirement that a field be no longer than a certain length a reason not to use TEXT? If you have an actual business-logic requirement to restrict a field to no more than N characters, then by all means use varchar(N); that's what it's for. But I agree with what I think Josh meant: there is very seldom any non-broken reason to have a hard upper limit on string lengths. If you think you need varchar(N) you should stop and ask why exactly. If you cannot give a specific, coherent reason why the particular value of N that you're using is the One True Length for the field, then you really need to think twice. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Fixed width rows faster?
Mike Nolan [EMAIL PROTECTED] writes: Can't you just create a TEXT(255) field same as you can just create VARCHAR (with no length) field? I think they're basically synonyms for each other these days. I'll defer to the SQL standard gurus on this, as well as to the internals guys, but I suspect there is a difference between the standard itself and implementor details, such as how char, varchar, varchar2 and text are implemented. As long as things work as specified, I don't think the standard cares much about what's happening behind the curtain. TEXT is not a standard datatype at all; that is, you will not find it in the standard, even though quite a few DBMSes have a datatype that they call by that name. Postgres' interpretation of TEXT is that there is no length-limitation option. I don't know what other DBMSes do with their versions of TEXT. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
