Re: [SQL] Join table with itself for heirarchial system?
You might look on our contrib/ltree module (http://www.sai.msu.su/~megera/postgres/gist/ltree) Oleg On Wed, 16 Jul 2003, Benjamin Smith wrote: > Can you query a set of nested entries to simulate a heirarchial system with a > single query? > > I'm building a nested category table with a definition like below" > > CREATE TABLE category ( > id serial, > parent integer not null, > title varchar); > > Idea is that we can "nest" categories so that we have > > idparent title > -- > 1 0 Clothing > 2 1 Shirts > 3 1 Pants > 4 1 Socks > 5 4 Male > 6 4 Silk > > So that, for example, id 6 would be > > Clothing -> Socks -> Silk. > > So far, I've only been able to derive this with 3 queries - 1 to get the > parent for id #6 (Silk) another to get the parent for id #4 (Socks) and > finally for id #1 (Clothing) and since parent ==0 I stop. > > This seems wasteful - can this be done in a single query? > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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: [SQL] inet versus text for ip addresses
Dan Langille wrote:
> The PostgreSQL inet datatype stores an holds an IP host address, and
> optionally the identity of the subnet it is in, all in one field.
> This requires 12 bytes.
>
> Using my "random" data of approximately 8000 IP addresses collected
> during previous polls, I've found the average length of an IP address
> is 13.1 bytes.An integer requires 4 bytes.
>
> First question: Why not store an option to store just an IP address?
> That should require less than the 12 bytes for inet.
We store inet and cidr in similar structures, and they are of variable
length (4 byte overhead):
/*
* This is the internal storage format for IP addresses
* (both INET and CIDR datatypes):
*/
typedef struct
{
unsigned char family;
unsigned char bits;
unsigned char type;
union
{
unsigned int ipv4_addr; /* network byte order */
/* add IPV6 address type here */
} addr;
} inet_struct;
/*
* Both INET and CIDR addresses are represented within Postgres as varlena
* objects, ie, there is a varlena header (basically a length word) in front
* of the struct type depicted above.
*
* Although these types are variable-length, the maximum length
* is pretty short, so we make no provision for TOASTing them.
*/
typedef struct varlena inet;
In 7.4, we support IPv6, so they will be even larger.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(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
