Re: [SQL] Diferent databases on same query...
Just my two cents on the issue: A good reason would be having several databases which each use the same set of tables which contain some information which takes a lot of storage, such as dictionaries or map information. You wouldn't want to maintain several copies of 500 Mb tables, especially if they get updated frequently. The workaround, for now, is to have a table just for the foreign keys for the different systems, and to query the shared database separately from within your programming environment. Troy > > > "Douglas Rafael da Silva" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > Hi, > > > > I'd like to do a query where can be possible I access tables from > > diferent databases on the same query. > > On MySQL, I do: > > > > But Who I can to do this on Postgresql ? > > You CANNOT do that with PostgreSQL. > But why do you want to do that? IMHO it's a rather bas design to hold data > in different places, if you need to select them in one query. > Is there a real reason to hold the tables in different databases? > > Andre > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Character translation?
Would this work? select recid, title, firstname || chr(32) || lastname::char(50) as expert, rank from mytable where length(lastname) <= 50; Troy > > Hi, > > I have a view like this: > create view myview as > select recid, title, firstname || chr(32) || lastname as expert, rank > from mytable; > > When I use this view via odbc on a Windows 2000 system using Visual FoxPro > the expert field shows up as a memo field. This is analogous to the text > field in PSQL. > > What I'd like to do is have the expert column come through as a varchar type > so that it shows up as a text field in VFP. Any suggestions? > > I was looking at the functions and didn't see anything that would do the > trick for me but I could have just missed something. I also saw a reference > to the cast() function but couldn't find any usage info on it in the online > docs. > > Any help is greatly appreciated. > Thanks in advance, > Linn > > > -- > Please remove the number two from domain name for email. > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [OT] Inventory systems (private)
Ries,
One solution is to create a table such as follows:
CREATE TABLE inventory (id serial, product text,
PRIMARY KEY (id)
) ;
CREATE TABLE attributes (prodid int4, textkey text, textvalue text, int4value int4,
FOREIGN KEY (prodid) REFERENCES inventory (id) MATCH FULL ON DELETE CASCADE ON
UPDATE CASCADE
);
INSERT INTO inventory (id, product) VALUES (100, 'Tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Type', 'matchbox
tire');
INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Color', 'black');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
'Diameter', 12, 'mm');
INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100, 'Weight',
20, 'g');
CREATE INDEX textkeys ON attributes USING btree (prodid);
CREATE INDEX textkeys2 ON attributes USING btree (prodid,textkey);
To select diameter for product id 100 (Tire):
SELECT prodid FROM attributes WHERE prodid = 100 AND textkey = 'Diameter';
To select several:
SELECT prodid,textkey,textvalue,int4value FROM attributes WHERE prodid = 100 AND
textkey IN ('Diameter', 'Weight', 'Color', 'Type');
and so on.
The indexes are just a fast guess. You would need to look at the queries
you generate and decide which indexes are needed.
Cheers,
Troy
Troy KorjuslommiTksoft Inc.
[EMAIL PROTECTED]
>
> Hi All,
>
> is there any whitepaper, document or website that can point me to how to
> setup a inventory system?
> I'm particulary interested how other people solve the problem of a unknown
> number of attributes to a inventory item.
>
> example:
> BAL<-- Inventory Item
> - Color <- Attribute
> - Diameter <- Attribute
> - Weight <- Attribute
>
> Car<-- Inventory Item
> - Speed <- Attribute
> - Size <- Attribute
> - Weight <- Attribute
> - Color <- Attribute
>
> Computer<-- Inventory Item
> - Brand <- Attribute
> - Weight <- Attribute
> - Windows/Linux <- Attribute
>
>
> I can ofcource add any number of columns to a table but for a lot of items
> there will be a lot of NULL values and currently I don't know how many
> attrubutes one item can have (possible between 10 and 20). This can even
> change in feature opon request.
>
> Ries
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] [OT] Inventory systems (private)
Ries,
Sorry I missed a few days here. I was busy elsewhere.
Anyway, the speed issue is fixed with indexes. Once you know
the kind of queries you will be making, create an optimized index for
each one of those queries.
You probably want to have a separate table for storing
the attribute names. I.e. instead of using a text variable
in each record, use a unique id which references an entry
in an attributes table. This won't affect the speed of the
system but it will save some space. Or if nothing else, it will
be more elegant.
Cheers,
Troy
>
> Troy, Andy,
>
> you both are right and it was my first tought. My only concern is that would
> this system be fast enough for large tables (for me large is around 250.000
> unique items) and thus my attribute table would be around 2.500.000 and
> 5.000.000 entrys. A record for one attribute is small I think around 128
> byte in size.
>
> One thing is that every attribute must be in it's own domain. For weight for
> example I have three different meanings ( 1] Pull weight 2] push weight 3]
> weight of the items itself) but using the method troy suggested that would
> not be a problem if I create some sort of a domain table.
>
> Anyway so far thangs for the quick responses, I've got something to work on.
>
> best regards,
> Ries van Twisk
>
>
>
>
> -Oorspronkelijk bericht-
> Van: Troy [mailto:[EMAIL PROTECTED]]
> Verzonden: dinsdag 3 december 2002 15:47
> Aan: Ries van Twisk
> CC: [EMAIL PROTECTED]
> Onderwerp: Re: [SQL] [OT] Inventory systems (private)
>
>
> Ries,
>
> One solution is to create a table such as follows:
>
> CREATE TABLE inventory (id serial, product text,
> PRIMARY KEY (id)
> ) ;
> CREATE TABLE attributes (prodid int4, textkey text, textvalue text,
> int4value int4,
> FOREIGN KEY (prodid) REFERENCES inventory (id) MATCH FULL ON DELETE CASCADE
> ON UPDATE CASCADE
> );
>
> INSERT INTO inventory (id, product) VALUES (100, 'Tire');
>
> INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Type',
> 'matchbox tire');
> INSERT INTO attributes (prodid, textkey, textvalue) VALUES (100, 'Color',
> 'black');
> INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
> 'Diameter', 12, 'mm');
> INSERT INTO attributes (prodid, textkey, int4value, textvalue) VALUES (100,
> 'Weight', 20, 'g');
> CREATE INDEX textkeys ON attributes USING btree (prodid);
> CREATE INDEX textkeys2 ON attributes USING btree (prodid,textkey);
>
> To select diameter for product id 100 (Tire):
> SELECT prodid FROM attributes WHERE prodid = 100 AND textkey = 'Diameter';
>
> To select several:
> SELECT prodid,textkey,textvalue,int4value FROM attributes WHERE prodid = 100
> AND textkey IN ('Diameter', 'Weight', 'Color', 'Type');
>
>
> and so on.
>
> The indexes are just a fast guess. You would need to look at the queries
> you generate and decide which indexes are needed.
>
>
>
> Cheers,
>
>
> Troy
>
> Troy KorjuslommiTksoft Inc.
> [EMAIL PROTECTED]
>
>
>
>
> >
> > Hi All,
> >
> > is there any whitepaper, document or website that can point me to how to
> > setup a inventory system?
> > I'm particulary interested how other people solve the problem of a unknown
> > number of attributes to a inventory item.
> >
> > example:
> > BAL<-- Inventory Item
> > - Color <- Attribute
> > - Diameter <- Attribute
> > - Weight <- Attribute
> >
> > Car<-- Inventory Item
> > - Speed <- Attribute
> > - Size <- Attribute
> > - Weight <- Attribute
> > - Color <- Attribute
> >
> > Computer<-- Inventory Item
> > - Brand <- Attribute
> > - Weight <- Attribute
> > - Windows/Linux <- Attribute
> >
> >
> > I can ofcource add any number of columns to a table but for a lot of items
> > there will be a lot of NULL values and currently I don't know how many
> > attrubutes one item can have (possible between 10 and 20). This can even
> > change in feature opon request.
> >
> > Ries
> >
> >
> > ---(end of broadcast)---
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] once again, sorting with Unicode
You can generate indexes for your custom functions, though, which will speed things up. This is what I've done, successfully. Troy > > JBJ <[EMAIL PROTECTED]> writes: > > Furthermore I will have to be able to switch between is_IS and others to > > get the correct sort order ( russian sort order different from icelandic > > for example, simply due to the nature of the letters) live when the user > > selects a different language. > > Postgres can't do that at the moment :-(. You might be able to hack up > some custom functions to do something like > ORDER BY icelandic(text_col) > but it'll be messy, and probably slow. > > 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]) > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] once again, sorting with Unicode
There are various examples in the example source code section
of the postgres distribution, where you can find code you can
use to write exactly the kind of funtion you need. I can't
immediately include source code from us, but I can include
the gist of how the code works.
The basic idea is to convert the input data to byte values
which are in the right order. If the input data is unicode,
utf8, utf16, or whatever, you have to know what it is, so you
can convert the data to a meaningful byte stream which can be
evaluated just like an array of numbers would be. I.e. remove
bytes which indicate something to the encoding and convert
characters to their one byte values. E.g. if the data is UTF8,
it is one or two bytes long for ISO8859_1 (upto six bytes for
others), one byte for ascii and two bytes for ISO8859_1. You
need to convert it to a one byte long value so comparisons at
byte level will work. For pure unicode you just have to skip
every other byte.
1. Source code :
... various includes.
PG_FUNCTION_INFO_V1(sample_encoding_func);
Datum sample_encoding_func(PG_FUNCTION_ARGS) {
text * str;
text * result;
size_t len;
if (PG_ARGISNULL(0))
PG_RETURN_NULL();
str = PG_GETARG_TEXT_P(0);
len = VARSIZE(str) - VARHDRSZ;
... do your conversion thing, allocate memory for the
result and return the value, doing error checking as you
go.
}
Add the function to your db:
DROP FUNCTION sample_encoding_func (text);
CREATE FUNCTION sample_encoding_func (text) RETURNS text
AS 'sample_encoding_func.so'
LANGUAGE 'C' WITH (iscachable,isstrict);
You can create an index with:
create index dummyindex on usertable using btree (sample_encoding_func(username)
text_ops);
Troy
>
> At 20:16 19.2.2003, Troy K wrote:
> >You can generate indexes for your custom functions, though,
> >which will speed things up. This is what I've done, successfully.
>
> Sounds useful, do you have a demo of such a function?
>
> I can if all else fails sort the data using PHP but am not too fond of it
> when I have over 2000 rows or more as will be the case in other tables.
>
> Thanks all for the answers.
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Aggregate query for multiple records
shouldn't take that long, I would think. You have indexes on wid and date? Troy > > Hello, I am new to the list, my apology if this question is beyond the > scope or charter of this list. > > My questions is: > What is the best method to perform an aggregate query to calculate > sum() values for each distinct wid as in the example below, but except > for all wid's (not just WHERE wid='01/1-6-1-30w1/0'). > > Also, performance wise, would it be better to build a function for this > query. The table has 9 million records and these aggregate queries > take hours. > > > SELECT >SUM(oil) as sumoil, SUM(hours) as sumhours, > FROM >(SELECT oil, hours prd_data WHERE wid='01/1-6-1-30w1/0' > ORDER BY date LIMIT 6) subtable > ; > > > Table description: >Table "prd_data" > Column | Type | Modifiers > +---+--- > date | integer | > hours | real | > oil| real | > gas| real | > water | real | > pwid | integer | > wid| character varying(20) | > year | smallint | > Indexes: wid_index6 > > > Actual table (prd_data), 9 million records: > >date | hours | oil | gas | water | pwid | wid | year > +---+---+--+---+--+-+-- > 196507 | 360 | 159.4 | 11.3 | 40.9 | 413 | 01/1-1-1-31w1/0 | 1965 > 196508 | 744 | 280 | 20 | 27.2 | 413 | 01/1-1-1-31w1/0 | 1965 > 196509 | 360 | 171.1 | 11.4 | 50.4 | 413 | 01/1-1-1-31w1/0 | 1965 > 196510 | 744 | 202.1 | 25 | 89.8 | 413 | 01/1-1-1-31w1/0 | 1965 > 196512 | 744 | 201.3 | 23.8 | 71.9 | 413 | 01/1-1-1-31w1/0 | 1965 > 196511 | 720 | 184 | 17.6 | 78.9 | 413 | 01/1-1-1-31w1/0 | 1965 > 196610 | 744 | 99.8 | 15.4 | 53.7 | 413 | 01/1-1-1-31w1/0 | 1966 > 196612 | 744 |86 | 12.8 | 36.1 | 413 | 01/1-1-1-31w1/0 | 1966 > 196611 | 720 |86 | 12.6 | 41.7 | 413 | 01/1-1-1-31w1/0 | 1966 > 196601 | 744 | 191.6 | 22.6 | 50.7 | 413 | 01/1-1-1-31w1/0 | 1966 > 200301 | 461 | 68.8 | 0 | 186.3 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200310 | 740 | 446.3 | 0 | 563.1 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200306 | 667 | 92.1 | 0 | 968.8 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200304 | 0 | 0 | 0 | 0 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200308 | 457 | 100.7 | 0 | 82.8 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200307 | 574 |78 | 0 | 752 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200312 | 582 | 360.9 | 0 | 569 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200311 | 681 | 260.8 | 0 | 563.9 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200305 | 452 | 0 | 0 | 0 | 47899 | 9G/6-1-50-24w3/0 | 2003 > 200309 | 637 | 244.6 | 0 | 193.8 | 47899 | 9G/6-1-50-24w3/0 | 2003 > (20 rows) > > > > Thanks, > > -- > Scott A. Gerhardt, P.Geo. > Gerhardt Information Technologies > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(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
