[SQL] conditional FROM
Good day... I'm trying to build a query for PGSQL 9.1 where a table has two references with only one being used depending of the type of entry.. For example, the table has following simplified structure: portid primary key port2node index to table node port2card index to table card So how can I do a conditional FROM clause in the query depending on the column port2node and port2card? If port2card is Null or 0 I don't want it in the FROM clause as the query will return unneccessary duplicate row...the same goes for port2node being Null or 0... thanx in advance richard -- 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] conditional FROM
Richard Klingler wrote: > Good day... > > I'm trying to build a query for PGSQL 9.1 where a table has two > references with only one being used depending of the type of entry.. > > For example, the table has following simplified structure: > > portid primary key > port2node index to table node > port2card index to table card > > So how can I do a conditional FROM clause in the query depending on the > column port2node and port2card? > If port2card is Null or 0 I don't want it in the FROM clause as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... I think, you can't do that, you have to build your query and execute that string. You should use a function to do that. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] conditional FROM
On Dec 10, 2011, at 11:03, Richard Klingler wrote: > Good day... > > I'm trying to build a query for PGSQL 9.1 where a table has two > references with only one being used depending of the type of entry.. > > For example, the table has following simplified structure: > >portidprimary key >port2nodeindex to table node >port2cardindex to table card > > So how can I do a conditional FROM clause in the query depending on the > column port2node and port2card? > If port2card is Null or 0 I don't want it in the FROM clause as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... > > > thanx in advance > richard > > Two options (one of which may not work for you). 1. Write two queries, one for each table, and union the results. 2. Use LEFT JOINs (somehow...) David J. -- 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] conditional FROM
This seems to do the trick... select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan from arp, port, node where arp.arp2port = port.portid and port.name = 'Fa1/0/1' and port.port2node = node.nodeid and node.name like 'nodename%' union select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan from arp, port, card, node where arp.arp2port = port.portid and port.name = 'Fa1/0/1' and port.port2card = card.cardid and card.card2node = node.nodeid and node.name like 'nodename%' ; Though I just can't order the rows anymore by inet(arp.ip) anymore... Any hints on my ordering isn't anylonger possible? But at least the query is way faster than before (o; 2msec instead of 650msecs (o; thanx ina dvance richard On Sat, 10 Dec 2011 11:28:29 -0500, David Johnston wrote: > On Dec 10, 2011, at 11:03, Richard Klingler wrote: > >> Good day... >> >> I'm trying to build a query for PGSQL 9.1 where a table has two >> references with only one being used depending of the type of entry.. >> >> For example, the table has following simplified structure: >> >>portidprimary key >>port2nodeindex to table node >>port2cardindex to table card >> >> So how can I do a conditional FROM clause in the query depending on the >> column port2node and port2card? >> If port2card is Null or 0 I don't want it in the FROM clause as the >> query will return unneccessary duplicate >> row...the same goes for port2node being Null or 0... >> >> >> thanx in advance >> richard >> >> > > Two options (one of which may not work for you). > > 1. Write two queries, one for each table, and union the results. > 2. Use LEFT JOINs (somehow...) > > David J. -- 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] conditional FROM
Richard Klingler wrote: > This seems to do the trick... > > select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan > from arp, port, node > where > arp.arp2port = port.portid and port.name = 'Fa1/0/1' > and port.port2node = node.nodeid > and node.name like 'nodename%' > union > select arp.ip, arp.mac, arp.port2time, arp.time, arp.hostname, arp.vlan > from arp, port, card, node > where > arp.arp2port = port.portid and port.name = 'Fa1/0/1' > and port.port2card = card.cardid > and card.card2node = node.nodeid > and node.name like 'nodename%' > ; > > Though I just can't order the rows anymore by inet(arp.ip) anymore... > Any hints on my ordering isn't anylonger possible? select * from (insert the query above here) foo order by ... Regards... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] conditional FROM
I guess this is what you mean: create table node( id bigint primary key); insert into node values (1); insert into node values (2); create table card( id integer primary key); insert into card values (1); insert into card values (2); create table port_activity ( portid integer primary key, port2node bigint, port2card integer ); alter table port_activity add constraint myconst1 foreign key (port2node) references node (id); alter table port_activity add constraint myconst2 foreign key (port2card) references card (id); insert into port_activity values (1,1,NULL); insert into port_activity values (2,NULL,1); select p.portid as port, coalesce(n.id,c.id) as destination from port_activity as p left outer join node as n on p.port2node=n.id left outer join card as c on p.port2card=c.id; Best regards Bèrto On 10 December 2011 19:03, Richard Klingler wrote: > Good day... > > I'm trying to build a query for PGSQL 9.1 where a table has two > references with only one being used depending of the type of entry.. > > For example, the table has following simplified structure: > >portid primary key >port2node index to table node >port2card index to table card > > So how can I do a conditional FROM clause in the query depending on the > column port2node and port2card? > If port2card is Null or 0 I don't want it in the FROM clause as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... > > > thanx in advance > richard > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
[SQL] Subselects to Joins? Or: how to design phone calls database
I have a table called 'calls' which holds 'call detail records'. Let's
assume the table looks like this:
CREATE TABLE cdr (
call_id serial,
phone_number text
);
And I have a table with country call prefixes, that looks like this:
CREATE TABLE prefixes (
prefix text,
country text
);
And now some test data:
INSERT INTO prefixes VALUES ('1', 'USA');
INSERT INTO prefixes VALUES ('44', 'UK');
INSERT INTO prefixes VALUES ('385', 'Croatia');
INSERT INTO prefixes VALUES ('387', 'Bosnia');
INSERT INTO prefixes VALUES ('64', 'New Zeland');
INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile');
INSERT INTO calls VALUES (1, '11952134451');
INSERT INTO calls VALUES (2, '448789921342');
INSERT INTO calls VALUES (3, '385914242232');
INSERT INTO calls VALUES (4, '385914242232');
INSERT INTO calls VALUES (5, '645122231241');
INSERT INTO calls VALUES (6, '444122523421');
INSERT INTO calls VALUES (7, '64212125452');
INSERT INTO calls VALUES (8, '1837371211');
INSERT INTO calls VALUES (9, '11952134451');
INSERT INTO calls VALUES (10, '448789921342');
INSERT INTO calls VALUES (11, '385914242232');
INSERT INTO calls VALUES (12, '385914242232');
INSERT INTO calls VALUES (13, '645122231241');
INSERT INTO calls VALUES (14, '4441232523421');
INSERT INTO calls VALUES (15, '64112125452');
INSERT INTO calls VALUES (16, '1837371211');
Now, if I want to have a 'join' between those two tables, here is what I
am doing right now:
SELECT
call_id,
phone_number,
(SELECT
country
FROM
prefixes
WHERE
calls.phone_number LIKE prefix || '%'
ORDER BY
length(prefix) DESC LIMIT 1
) AS country
FROM calls;
Is there a way I could use join here? I can do something like:
SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix || '%'
but I'd get duplicate rows there (for instance, for New Zeland calls,
from my test data).
Or should I add 'prefix' field to the calls table, and then do a inner
join with prefixes table?
Mario
--
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] Subselects to Joins? Or: how to design phone calls database
Hi Mario,
over - partition by will help. Iy that syntax is uncomfortable you can use
multiple joins, using maximum length or max(prefix::int) but that will slow
down the process. select over - partition by is fastest solution i think.
On Sat, Dec 10, 2011 at 11:24 PM, Mario Splivalo
wrote:
> I have a table called 'calls' which holds 'call detail records'. Let's
> assume the table looks like this:
>
> CREATE TABLE cdr (
>call_id serial,
>phone_number text
> );
>
> And I have a table with country call prefixes, that looks like this:
>
> CREATE TABLE prefixes (
>prefix text,
>country text
> );
>
> And now some test data:
>
> INSERT INTO prefixes VALUES ('1', 'USA');
> INSERT INTO prefixes VALUES ('44', 'UK');
> INSERT INTO prefixes VALUES ('385', 'Croatia');
> INSERT INTO prefixes VALUES ('387', 'Bosnia');
> INSERT INTO prefixes VALUES ('64', 'New Zeland');
> INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile');
> INSERT INTO calls VALUES (1, '11952134451');
> INSERT INTO calls VALUES (2, '448789921342');
> INSERT INTO calls VALUES (3, '385914242232');
> INSERT INTO calls VALUES (4, '385914242232');
> INSERT INTO calls VALUES (5, '645122231241');
> INSERT INTO calls VALUES (6, '444122523421');
> INSERT INTO calls VALUES (7, '64212125452');
> INSERT INTO calls VALUES (8, '1837371211');
> INSERT INTO calls VALUES (9, '11952134451');
> INSERT INTO calls VALUES (10, '448789921342');
> INSERT INTO calls VALUES (11, '385914242232');
> INSERT INTO calls VALUES (12, '385914242232');
> INSERT INTO calls VALUES (13, '645122231241');
> INSERT INTO calls VALUES (14, '4441232523421');
> INSERT INTO calls VALUES (15, '64112125452');
> INSERT INTO calls VALUES (16, '1837371211');
>
>
> Now, if I want to have a 'join' between those two tables, here is what I
> am doing right now:
>
> SELECT
>call_id,
>phone_number,
>(SELECT
>country
>FROM
>prefixes
>WHERE
>calls.phone_number LIKE prefix || '%'
>ORDER BY
>length(prefix) DESC LIMIT 1
>) AS country
> FROM calls;
>
>
> Is there a way I could use join here? I can do something like:
>
> SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix ||
> '%'
>
> but I'd get duplicate rows there (for instance, for New Zeland calls,
> from my test data).
>
> Or should I add 'prefix' field to the calls table, and then do a inner
> join with prefixes table?
>
>Mario
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
---
Viktor Bojović
---
Wherever I go, Murphy goes with me
Re: [SQL] conditional FROM
On 2011-12-10, Richard Klingler wrote: > Good day... > > I'm trying to build a query for PGSQL 9.1 where a table has two > references with only one being used depending of the type of entry.. > > For example, the table has following simplified structure: > > portid primary key > port2node index to table node > port2card index to table card > > So how can I do a conditional FROM clause in the query depending on the > column port2node and port2card? > If port2card is Null or 0 I don't want it in the FROM clause as the > query will return unneccessary duplicate > row...the same goes for port2node being Null or 0... use left outer join. SELECT * FROM port LEFT OUTER JOIN node ON node.nodeid=port.port2node LEFT OUTER JOIN card ON card.cardid=port.port2card or something like that. You may find coalesce() useful to combine columns where node and card both carry equivalent information. -- ⚂⚃ 100% natural -- 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] Question on imports with foreign keys
On 2011-12-08, Andreas wrote:
> Hi,
>
> suppose you need to import a csv with standard ciolums like name,
> adress, phone, ... and some additional text columns that need to be
> split off into referenced tables.
...
> How is the easiest way to to find the customer.id of the new customers
> so I can insert the projectinfos?
create table tmp.customer (id integer, name text, addr text)
copy tmp.customer ( id,name,addr ) from stdin ;
...
alter table tmp.customer add column new_id integer default
nextval('customer_id.seq'::regclass);
(here the default is the same default that the customer table uses for
its id.)
now you can use "insert ... select ..." to insert these new records
explicitly using new_id to fill the id column of the customer table.
iport the other csv data into similar tables also
and use join on the old id in tmp.customer to get the new id for
copying the other imported tabled.
--
⚂⚃ 100% natural
--
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] partitions versus databases
On 2011-12-08, chester c young wrote: > have an db with about 15 tables that will handle many companies. no data > overlap between companies. is it more efficient run-time to use one database > and index each row by company id, and one database and partition each table > by company id, or to create a database for each company? > > it is a web-based app using persistent connections. no copying. > if you know you will never want to aggregate data across several companies. databases are cheap, portable, easily duplicated, and self-contained, can easily be dumped, restored, and dropped individually, go with one per company. if there's a possibility you may want to merge two companies, or aggregate data in some other way you want to put them all in the same database so that sequences can be shared to ensure that ids are unique etc... you still have the option of partitioning by schema, table name, or just by tagging each record. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
