Re: [SQL] Table Design for Hierarchical Data

On Tue, Apr 6, 2010 at 11:43 PM, silly sad wrote: > P.S. > almost foget, do not try any oracle-like "tree-jouns" or "special types" or > such a crap. > > your problem as plain as to store a pair of integers > (or numerics (i prefer)) Since it's an identifier and not really a numeric per se, I'd s

Re: [SQL] Table Design for Hierarchical Data

P.S. almost foget, do not try any oracle-like "tree-jouns" or "special types" or such a crap. your problem as plain as to store a pair of integers (or numerics (i prefer)) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.

Re: [SQL] Table Design for Hierarchical Data

single table. nested tree + ordinal parent reference. nests are calculated in a trigger on insert. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Table Design for Hierarchical Data

Lee Hachadoorian wrote: I am trying to come up with a structure to store employment data by NAICS (North American Industrial Classification System). The data uses a hierarchical encoding scheme ranging between 2 and 5 digits. That is, each 2-digit code includes all industries beginning with t

Re: [SQL] Table Design for Hierarchical Data

On Tue, Apr 6, 2010 at 3:04 PM, Michael Glaesemann wrote: > Another is nested sets which performs quite nicely for loads which are more > read than write (which I suspect is the case here). Pg 9.0 has two new features are nice for both Nest set trees. one is deferrable unique constraints. Whil

Re: [SQL] Table Design for Hierarchical Data

On Apr 6, 2010, at 13:33 , Lee Hachadoorian wrote: > A standard way to store hierarchical data is the adjacency list model, where > each node's parent appears as an attribute (table column). Another is nested sets which performs quite nicely for loads which are more read than write (which I sus

Re: [SQL] count function alternative in postgres

Quoth junaidmali...@gmail.com (junaidmalik14): > > Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in > postgres. We get error if we > > write count like this count(distinct profile.id, profile.name, profile.age) > but it works well in mysql. Pg does support COUNT(DIST

Re: [SQL] count function alternative in postgres

junaidmalik14 wrote on 03.04.2010 14:58: Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.my

[SQL] Table Design for Hierarchical Data

Please point me to another listserv or forum if this question is more appropriately addressed elsewhere. I am trying to come up with a structure to store employment data by NAICS (North American Industrial Classification System). The data uses a hierarchical encoding scheme ranging between 2 and 5

[SQL] INSERT INTO...RETURNING vs SELECT

I was doing some experimenting and was wondering why the following does not work: CREATE TABLE accounts ( id SERIAL PRIMARY KEY NOT NULL, accounts_id INTEGER REFERENCES accounts, name TEXT ); INSERT INTO accounts (accounts_id, name) VALUES ( (INSERT INTO accounts (accounts_id, name) VALUE

[SQL] count function alternative in postgres

Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in postgres. We get error if we write count like this count(distinct profile.id, profile.name, profile.age) but it works well in mysql. Reference url is given below http://dev.mysql.com/doc/refman/5.1/en/group-by-functi