Re: [SQL] Subselects to Joins? Or: how to design phone calls database

) Then you can join calls to phonenumbers on full number string then join countries on country prefix... Kind Regards, Misa Sent from my Windows Phone From: Mario Splivalo Sent: 10 December 2011 23:27 To: pgsql-sql@postgresql.org Subject: [SQL] Subselects to Joins? Or: how to design phone calls

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

[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,

Re: [SQL] Subselects not allowed?

* Leif Biberg Kristensen: > And even better, in the first comment to the blog post, I was advised about > the > SETVAL() function which does exactly what I wanted in the first place. > > CREATE SEQUENCE persons_person_id_seq; > SELECT SETVAL('persons_person_id_seq', MAX(person_id)) FROM persons;

Re: [SQL] Subselects not allowed?

Leif Biberg Kristensen wrote: > On Saturday 11. June 2011 22.09.09 Leif Biberg Kristensen wrote: > > I've written a blog post which I hope may be helpful to others in a similar > > situation: > > > > > > > > Thanks to Guillaume Lelarge, Tom Lane, and Andreas

Re: [SQL] Subselects not allowed?

On Saturday 11. June 2011 22.09.09 Leif Biberg Kristensen wrote: > I've written a blog post which I hope may be helpful to others in a similar > situation: > > > > Thanks to Guillaume Lelarge, Tom Lane, and Andreas Kretschmer. You guys are > great. And even be

Re: [SQL] Subselects not allowed?

I've written a blog post which I hope may be helpful to others in a similar situation: Thanks to Guillaume Lelarge, Tom Lane, and Andreas Kretschmer. You guys are great. regards, Leif http://code.google.com/p/yggdrasil-genealogy/ -- Sent via pgsql-sql mail

Re: [SQL] Subselects not allowed?

On Saturday 11. June 2011 17.23.40 Andreas Kretschmer wrote: > Leif Biberg Kristensen wrote: > > Can anybody tell me why this doesn't work? > > > > pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT > > MAX(source_id) FROM sources); > > ERROR: syntax error at or near "(" > > LINE

Re: [SQL] Subselects not allowed?

On Saturday 11. June 2011 17.14.21 Tom Lane wrote: > Guillaume Lelarge writes: > > On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote: > >> Can anybody tell me why this doesn't work? > >> pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT > >> MAX(source_id) FROM sourc

Re: [SQL] Subselects not allowed?

Leif Biberg Kristensen wrote: > Can anybody tell me why this doesn't work? > > pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT > MAX(source_id) FROM sources); > ERROR: syntax error at or near "(" > LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX... >

Re: [SQL] Subselects not allowed?

Guillaume Lelarge writes: > On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote: >> Can anybody tell me why this doesn't work? >> pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT >> MAX(source_id) FROM sources); >> ERROR: syntax error at or near "(" > Because it's

Re: [SQL] Subselects not allowed?

On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote: > Can anybody tell me why this doesn't work? > Because it's not supported. The START clause expects a value, not a subquery. > pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT > MAX(source_id) FROM sources); > ER

[SQL] Subselects not allowed?

Can anybody tell me why this doesn't work? pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX(source_id) FROM sources); ERROR: syntax error at or near "(" LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX...

Re: [SQL] Subselects returning array and ANY...

Dawid, > But you are right, though! I just need to leave off the subselect! > Thanks! SELECT h.* FROM haystack h JOIN needles ON straw = ANY (straws); > > (not exactly what I was looking for, but it works ;)) Yeah, the problem with your first query is that it's returning a rowset of arrays, no

Re: [SQL] Subselects returning array and ANY...

On Thu, Feb 28, 2008 at 1:11 AM, Josh Berkus <[EMAIL PROTECTED]> wrote: > Dawid, > >SELECT * FROM foo WHERE t = ANY (SELECT '{x4,5,zzz}'::text[]); > > ERROR: operator does not exist: text = text[] > > HINT: No operator matches the given name and argument type(s). You > > might need to add

Re: [SQL] Subselects returning array and ANY...

Dawid, >SELECT * FROM foo WHERE t = ANY (SELECT '{x4,5,zzz}'::text[]); > ERROR: operator does not exist: text = text[] > HINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts. Drop the second SELECT, I think. postgres=# select 'x' = ANY

[SQL] Subselects returning array and ANY...

A simple text case =# CREATE TEMP TABLE foo (t text); CREATE TABLE =# INSERT INTO foo SELECT 'x'||n FROM generate_series(1,100) AS x(n); INSERT 0 100 This works: SELECT * FROM foo WHERE t = ANY ('{x4,5,zzz}'::text[]); And this works too: SELECT * FROM foo WHERE t IN (SELECT t FROM foo LIMIT

Re: [SQL] Subselects in CHECK clause ...

Gotcha. This is indeed to ensure a specialized ref integrity bit, against a column which "ought not ever change". Once some more old code goes away, then can fully normalize this area, making this check subselect bit go away, replaced by a regular FK. Hmm -- probably could even now make it

Re: [SQL] Subselects in CHECK clause ...

On Wed, 29 Nov 2006, James Robinson wrote: > I see that subselects are not directly supported in check clauses, > but one can work around that by writing a stored function which > returns boolean and performs the subselect. Are there any known > gotchas with doing this? To completely get the con

[SQL] Subselects in CHECK clause ...

I see that subselects are not directly supported in check clauses, but one can work around that by writing a stored function which returns boolean and performs the subselect. Are there any known gotchas with doing this? The CREATE TABLE docs regarding CHECK clauses states: "Currently, CHE

Re: [SQL] subselects - which is faster?

On Thu, 12 Jun 2003, Cedar Cox wrote: > It's been a while since I've done much SQL.. > > . I'm wondering which of these two queries is faster (both get the > same result)? > > . Which one is more correct? Does it even matter or are they the > same? The first one reads easier to me. > > . Wha

[SQL] subselects - which is faster?

It's been a while since I've done much SQL.. . I'm wondering which of these two queries is faster (both get the same result)? . Which one is more correct? Does it even matter or are they the same? The first one reads easier to me. . What's the difference between "InitPlan" and "SubPlan"? exp

Re: [SQL] Subselects, the Oracle way

"Svenne Krap" <[EMAIL PROTECTED]> writes: > Is there any way to mimic the oracle way of subselect, especially > constructs like > select * from (select col1 as x, col2, col6 from t1 union select col2 > as x, col6, col2 from t2) y order by y.x That should work fine in 7.1 ...

[SQL] Subselects, the Oracle way

Hi, Is there any way to mimic the oracle way of subselect, especially constructs like select * from (select col1 as x, col2, col6 from t1 union select col2 as x, col6, col2 from t2) y order by y.x I am aware of the fact, that it is possible to accomplish through the use of tempoary tables,

Re: [SQL] subselects

hubert depesz lubaczewski wrote: > > On Wed, Nov 29, 2000 at 07:03:36PM -0500, Joseph Shraibman wrote: > > I tried to do this: > > SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep > > WHERE m IN(190); > > why dont you use simple join? > like: > select rep.r, rep.a, rep.cdate

Re: [SQL] subselects

On Wed, Nov 29, 2000 at 07:03:36PM -0500, Joseph Shraibman wrote: > I tried to do this: > SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep > WHERE m IN(190); why dont you use simple join? like: select rep.r, rep.a, rep.cdate, dir.u, dir.re from rep, dir where rep.m in (190) a

[SQL] subselects

I tried to do this: SELECT r , a , (SELECT u , re FROM dir WHERE u = a) , cdate FROM rep WHERE m IN(190); ... and I got: ERROR: Subselect must have only one field An explain shows that two subselects result in two queries, even thought they are accessing the same row. Why can subselects on

[SQL] Subselects with IN and EXISTS

Hello, Here is my query. SELECT id, title, type, sub_type, order_number, version, date, referred_to, referred_in FROM sop WHERE (type||sub_type||order_number, version) IN ^^^ (SELECT type||sub_type||order_number, max(version) FROM sop GROUP BY type||sub_type||order_number) ORDER BY type, sub_t