Postgres Pro
Downloads
Home   >   mailing lists

Thread: Some questions

Some questions

From
Vladimir Litovka
Date:
Hello!

 There is table of character types in manual, where the 'text' type suggested
 as the best choice. 'text' is variable length type and I think, that
 fixed length's types (such as char(n)) will be faster, than variable
 length's ones? Am I wrong?

 The second question is more complex :) It about CREATE TABLE statement. Is
 there difference between following statements?

  create table aaa (i int2 UNIQUE);
 and
  create table aaa (i int2, CONSTRAINT aaa_i_key UNIQUE(i));

 First case uses 'column constraint' and second - 'table constraint'. Manual
 says: "A column constraint is an integrity constraint defined as part of a
 column definition, and logically becomes a table constraint as soon as it
 is created". Am I understand correctly - it is never mind where to define
 (any) constraints - all of them does the same?

 And last: what does it mean the following statement in the CREATE TABLE?

 ...
  len INTERVAL HOUR TO MINUTE
 ...

 Thank you :)

---
Vladimir Litovka <doka@webest.com>

abusing an aggregate funct

From
Thomas Good
Date:
Hi all...

I ported a foxpro db to postgres - the foxpro strategy (if this is
not a non-sequitor) was to put everything in one big table...
with no unique constraints on any attr/no p_key.

I had perl iterate thru the dump file prepending an int value to each
tuple - which is now my p_key.  Works fine.

Due to the sheer girth of the table the following query, called from
a data entry script,  takes a bit of time to finish:

SELECT max(rec_num) FROM crtrd1;

I use this to setup `rec_num + 1' for my query number (p_key)...any
way I can speed this up a bit?  I've run vacuum analyze but the
table size is doing me in...

Stuck_In_Staten_Island,
Tom

(thanks ;-)

    ---------- Sisters of Charity Medical Center ----------
                   Department of Psychiatry
                            ----
    Thomas Good                          <tomg@q8.nrnet.org>
    Coordinator, North Richmond C.M.H.C. Information Systems
    75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
    Staten Island, NY   10304            Fax:   718-354-5056

Re: [SQL] abusing an aggregate funct

From
Marc Howard Zuckman
Date:
On Sat, 14 Nov 1998, Thomas Good wrote:

> Due to the sheer girth of the table the following query, called from
> a data entry script,  takes a bit of time to finish:
>
> SELECT max(rec_num) FROM crtrd1;
>
> I use this to setup `rec_num + 1' for my query number (p_key)...any
> way I can speed this up a bit?  I've run vacuum analyze but the
> table size is doing me in...
>
Use a sequence to generate rec_num.  See man create_sequence.

You can then use curval('sequence_name') to get the last
sequence number generated, or if you are performing an insert:
  insert into crtrd1 (p_key,otherdata) values (nextval('sequence_name',moredata);

You could also create the table using nextval as the default
value of p_key.  You may wish to create a unique_index on
p_key, but nextval is guaranteed not to generate duplicates
unless you allow it to rollover.





Marc Zuckman
marc@fallon.classyad.com

_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
_     Visit The Home and Condo MarketPlace              _
_          http://www.ClassyAd.com                  _
_                                  _
_  FREE basic property listings/advertisements and searches.  _
_                                  _
_  Try our premium, yet inexpensive services for a real          _
_   selling or buying edge!                      _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

Re: [SQL] Some questions

From
Herouth Maoz
Date:
At 20:09 +0200 on 13/11/98, Vladimir Litovka wrote:


>
>  There is table of character types in manual, where the 'text' type suggested
>  as the best choice. 'text' is variable length type and I think, that
>  fixed length's types (such as char(n)) will be faster, than variable
>  length's ones? Am I wrong?

Basically, yes. But it depends also on the order of the field. Every field
after the first variable-length field will take longer to access,
regardless of whether in itself it is variable or not.

>  The second question is more complex :) It about CREATE TABLE statement. Is
>  there difference between following statements?
>
>   create table aaa (i int2 UNIQUE);
>  and
>   create table aaa (i int2, CONSTRAINT aaa_i_key UNIQUE(i));
>
>  First case uses 'column constraint' and second - 'table constraint'. Manual
>  says: "A column constraint is an integrity constraint defined as part of a
>  column definition, and logically becomes a table constraint as soon as it
>  is created". Am I understand correctly - it is never mind where to define
>  (any) constraints - all of them does the same?

Not exactly. A column constraint is a private case of a table constraint,
which is limited only to one column. You can say: "I want this column to be
unique, divisable by 10, and positive".

But a table constraint allows you to define a condition on more than one
column in the same table. For example, you can say "I want column A to be a
multiple of column B". One of the constraints I needed once said "Either
column A or column B may be null, but not both of them together".

So, of course, you may add the column constraints to the table constraints,
because they *are* table constraints. However, it is more elegant to put
them together with the column definition. This lets whoever reads the table
definition see immediately all the information there is to know about the
specific column - that it has type integer, default value 13, it may not be
null, is unique, and so forth.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Re: [SQL] abusing an aggregate funct

From
Postgres DBA
Date:

On Sat, 14 Nov 1998, Marc Howard Zuckman wrote:

> On Sat, 14 Nov 1998, Thomas Good wrote:
>
> > Due to the sheer girth of the table the following query, called from
> > a data entry script,  takes a bit of time to finish:
> >
> > SELECT max(rec_num) FROM crtrd1;
> >
> > I use this to setup `rec_num + 1' for my query number (p_key)...any
> > way I can speed this up a bit?  I've run vacuum analyze but the
> > table size is doing me in...
> >
> Use a sequence to generate rec_num.  See man create_sequence.
>
> You can then use curval('sequence_name') to get the last
> sequence number generated, or if you are performing an insert:
>   insert into crtrd1 (p_key,otherdata) values (nextval('sequence_name',moredata);
>
> You could also create the table using nextval as the default
> value of p_key.  You may wish to create a unique_index on
> p_key, but nextval is guaranteed not to generate duplicates
> unless you allow it to rollover.
>
>
Unfortunately, solution using sequences is not so good unless you don't
suppose to use access to that table from some simultaneously running
sessions. The problem is that every sessions accessing this additional
sequence  with nextval()  will get its own pool of values for the
sequence, so early or later you'll get some gaps in records numbering
because of at least on of two reasons:
    a) usually the length of such a pool is set to limit that is
bigger then 1 and  obviously, it's impossible to check the number of
inserts (and thus the number of nestval`s) during every sessions.
    b) and in case of pool with 1-length some of your inserts may
fail, but nextavl would be called and next time you try insert you'll call
nextval again and will miss one or more values of sequence.


Also, you can't issue curval until you haven't call nextval at least once
during currunt session -- this is also source of gaps in values from
sequence assigned to records in the table. So, I think this method can
produce only approximation of real ammount of records:-(


Aleksey.

Re: [SQL] abusing an aggregate funct

From
Thomas Good
Date:
On Sat, 14 Nov 1998, Marc Howard Zuckman wrote:

> Use a sequence to generate rec_num.  See man create_sequence.

Got it.
Thanks Marc!

> You can then use curval('sequence_name') to get the last
> sequence number generated, or if you are performing an insert:
>   insert into crtrd1 (p_key,otherdata) values (nextval('sequence_name',moredata);
>
> You could also create the table using nextval as the default
> value of p_key.  You may wish to create a unique_index on
> p_key, but nextval is guaranteed not to generate duplicates
> unless you allow it to rollover.
>
>
>
>
>
> Marc Zuckman
> marc@fallon.classyad.com
>
> _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
> _     Visit The Home and Condo MarketPlace              _
> _          http://www.ClassyAd.com                  _
> _                                  _
> _  FREE basic property listings/advertisements and searches.  _
> _                                  _
> _  Try our premium, yet inexpensive services for a real          _
> _   selling or buying edge!                      _
> _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
>


    ---------- Sisters of Charity Medical Center ----------
                   Department of Psychiatry
                            ----
    Thomas Good                          <tomg@q8.nrnet.org>
    Coordinator, North Richmond C.M.H.C. Information Systems
    75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
    Staten Island, NY   10304            Fax:   718-354-5056