Re: [SQL] current_date vs 'now'
Jamie Tufnell writes:
> AFAIK current_date is standard and 'now' and 'today', etc are not...
> so that's one reason to continuing using current_date. However, I
> wonder why 'today' and current_date don't generate the same query
> plan?
'today'::date is a special string that is recognized by the date type's
input routine, but what it produces is a constant, for instance
'2009-02-05'::date if I executed it today. current_date is a function
that produces the current date whenever it is executed. Consider
for instance
regression=# create view v as select current_date as d1, 'today'::date as d2;
CREATE VIEW
regression=# select * from v;
d1 | d2
+
2009-02-05 | 2009-02-05
(1 row)
regression=# \d v
View "public.v"
Column | Type | Modifiers
+--+---
d1 | date |
d2 | date |
View definition:
SELECT 'now'::text::date AS d1, '2009-02-05'::date AS d2;
As the view definition printout suggests, tomorrow this view will produce
d1 | d2
+
2009-02-06 | 2009-02-05
because the constant isn't going to change.
As you can see from both this example and your own, our current
implementation of current_date is ('now'::text)::date. (Which
sucks, we ought to hide it better ;-)) What that actually means
is you have a constant of type text containing the string 'now',
and when the expression is executed there's a *run time* cast to
type date, causing the date input converter to get invoked at
that time. So it'll still work tomorrow.
Of course, the cost of the runtime interpretation of the string
is what accounts for your speed difference.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Must I use DISTINCT?
Please consider the following SQL SELECT e.eid, e.name FROM entry e, access a WHERE e.eid = 120 AND (e.ownid = 66 OR e.aid = a.aid) The intent is to match one entry with the eid of 120. However I would like to impose an additional constraint that either e.ownid must be 66 or e.aid must match the aid of an entry in the access table (there's actually a lot more to the query but I think this should be sufficient to illustrate my problem). The problem is that the e.ownid is 66 and therefore the same entry is returned for each access entry. Of course I can simply SELECT DISTINCT but that seems like an improper usage of DISTINCT here. Is there an alternative way to write this query? I only want to select from the access table for the purpose of constraining by aid. Mike -- Michael B Allen Java Active Directory Integration http://www.ioplex.com/ -- 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] current_date vs 'now'
On 2/6/09, Tom Lane wrote: > As the view definition printout suggests, tomorrow this view will produce > > 2009-02-06 | 2009-02-05 > > because the constant isn't going to change. Thanks for that explanation Tom. Very clear and helpful. Jamie -- 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] Array iterator
On Tuesday 27 Jan 2009, Achilleas Mantzios wrote:
> Στις Tuesday 27 January 2009 14:40:29 ο/η Raj Mathur έγραψε:
> > select regexp_split_to_array('string with tokens', '[^A-Za-z0-9]');
>
> maybe
> select regexp_split_to_table('string with tokens', '[^A-Za-z0-9]');
> would help?
That did the job, thanks!
-- Raju
--
Raj Mathurr...@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
--
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] Must I use DISTINCT?
have you tried Join using , eg SELECT e.eid, e.name FROM entry e join access a ON( e.eid = 120 AND (e.ownid = 66 OR e.aid = a.aid) ) ; some sample data might also help in understanding the prob more clrearly. regds rajesh kumar mallah. On Fri, Feb 6, 2009 at 3:27 AM, Michael B Allen wrote: > Please consider the following SQL > > SELECT e.eid, e.name > FROM entry e, access a > WHERE e.eid = 120 > AND (e.ownid = 66 OR e.aid = a.aid) > > The intent is to match one entry with the eid of 120. However I would > like to impose an additional constraint that either e.ownid must be 66 > or e.aid must match the aid of an entry in the access table (there's > actually a lot more to the query but I think this should be sufficient > to illustrate my problem). > > The problem is that the e.ownid is 66 and therefore the same entry is > returned for each access entry. > > Of course I can simply SELECT DISTINCT but that seems like an improper > usage of DISTINCT here. > > Is there an alternative way to write this query? I only want to select > from the access table for the purpose of constraining by aid. > > Mike > > -- > Michael B Allen > Java Active Directory Integration > http://www.ioplex.com/ > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- 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] Must I use DISTINCT?
On Thu, Feb 5, 2009 at 10:59 PM, Rajesh Kumar Mallah
wrote:
> have you tried Join using , eg
> SELECT e.eid, e.name
> FROM entry e join access a ON( e.eid = 120
> AND (e.ownid = 66 OR e.aid = a.aid) ) ;
>
> some sample data might also help in understanding the prob
> more clrearly.
Hi Rajesh,
Unfortunately using JOIN does not seem to change the result.
Here is some real data:
> select eid, name, ownid, aclid from foo_entry;
+-+---+---+---+
| eid | name | ownid | aclid |
+-+---+---+---+
| 64 | system|64 | 0 |
| 66 | abaker|66 | 0 |
| 67 | bcarter |67 | 0 |
| 68 | cdavis|68 | 0 |
| 69 | aba...@example.com|66 | 114 |
| 70 | bcar...@example.com |67 | 120 |
| 71 | cda...@example.com|68 | 0 |
| 72 | (201) 555-1234|66 | 0 |
| 73 | (201) 555-4321|67 | 0 |
| 74 | (908) 555-2341|68 | 0 |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |66 | 0 |
| 92 | IOPLEX Software |64 | 0 |
| 114 | Small Cap Consultants |66 | 0 |
| 120 | Equity Report Readers |66 | 0 |
| 111 | proton.foo.net|64 | 0 |
+-+---+---+---+
15 rows in set (0.01 sec)
> select a, b from foo_link;
+-+-+
| a | b |
+-+-+
| 64 | 111 |
| 64 | 113 |
| 66 | 69 |
| 66 | 72 |
| 66 | 113 |
| 66 | 114 |
| 67 | 70 |
| 67 | 89 |
| 67 | 113 |
| 68 | 71 |
| 68 | 113 |
| 69 | 72 |
| 70 | 73 |
| 71 | 74 |
| 71 | 92 |
| 114 | 120 |
+-+-+
16 rows in set (0.00 sec)
So there are two tables: foo_entry AS e and foo_link AS a1. I want to
select the the single row from foo_entry with e.eid = 113 but only if
the e.ownid = 66 OR if e.aclid is indirectly linked with 66 via the
foo_link table.
SELECT e.eid, e.name
FROM foo_entry e
JOIN foo_link a1 ON (e.eid = 113 AND (e.ownid = 66 OR (e.aclid = a1.a
AND a1.b = 66)))
This yields:
+-+---+
| eid | name |
+-+---+
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
| 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |
+-+---+
16 rows in set (0.00 sec)
So it matched eid 113 because e.ownid is 66 but it also matched
duplicates for each entry in the foo_link table because the is nothing
to constrain it with foo_link.
Ultimately what I'm trying to do is implement simple access lists in
SQL. The foo_link table represents links between account and access
list entries. So to select an entry, you either need to own it (ownid
= 66) or your account id is linked with it through an access list
entry (e.aclid = a1.a AND a1.b = 66).
If I use DISTINCT it works fine but it just does not feel right. I'm
worried that as the number of links increases (there could be
thousands) performance be negatively affected.
Mike
> On Fri, Feb 6, 2009 at 3:27 AM, Michael B Allen wrote:
>> Please consider the following SQL
>>
>> SELECT e.eid, e.name
>> FROM entry e, access a
>> WHERE e.eid = 120
>> AND (e.ownid = 66 OR e.aid = a.aid)
>>
>> The intent is to match one entry with the eid of 120. However I would
>> like to impose an additional constraint that either e.ownid must be 66
>> or e.aid must match the aid of an entry in the access table (there's
>> actually a lot more to the query but I think this should be sufficient
>> to illustrate my problem).
>>
>> The problem is that the e.ownid is 66 and therefore the same entry is
>> returned for each access entry.
>>
>> Of course I can simply SELECT DISTINCT but that seems like an improper
>> usage of DISTINCT here.
>>
>> Is there an alternative way to write this query? I only want to select
>> from the access table for the purpose of constraining by aid.
>>
--
Michael B Allen
Java Active Directory Integration
http://www.i
Re: [SQL] Must I use DISTINCT?
On Thu, 5 Feb 2009, Michael B Allen wrote: > Please consider the following SQL > > SELECT e.eid, e.name > FROM entry e, access a > WHERE e.eid = 120 > AND (e.ownid = 66 OR e.aid = a.aid) > > The intent is to match one entry with the eid of 120. However I would > like to impose an additional constraint that either e.ownid must be 66 > or e.aid must match the aid of an entry in the access table (there's > actually a lot more to the query but I think this should be sufficient > to illustrate my problem). > > The problem is that the e.ownid is 66 and therefore the same entry is > returned for each access entry. > > Of course I can simply SELECT DISTINCT but that seems like an improper > usage of DISTINCT here. > > Is there an alternative way to write this query? I only want to select > from the access table for the purpose of constraining by aid. Would something like: SELECT e.eid, e.name FROM entry e WHERE e.eid = 120 AND (e.ownid = 66 OR e.aid in (select a.aid from access a)) do what you wnat? -- 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] Must I use DISTINCT?
On Friday 06 Feb 2009, Michael B Allen wrote:
> On Thu, Feb 5, 2009 at 10:59 PM, Rajesh Kumar Mallah
>
> wrote:
> > have you tried Join using , eg
> > SELECT e.eid, e.name
> > FROM entry e join access a ON( e.eid = 120
> > AND (e.ownid = 66 OR e.aid = a.aid) ) ;
> >
> > some sample data might also help in understanding the prob
> > more clrearly.
>
> Hi Rajesh,
>
> Unfortunately using JOIN does not seem to change the result.
>
> Here is some real data:
> > select eid, name, ownid, aclid from foo_entry;
>
> +-+---+---+---+
>
> | eid | name | ownid | aclid |
>
> +-+---+---+---+
> | 113 | {MD5}ff132413c937ad9fd1ea0d5025891c2d |66 | 0 |
> +-+---+---+---+
> 15 rows in set (0.01 sec)
>
> > select a, b from foo_link;
>
> +-+-+
>
> | a | b |
>
> +-+-+
> | 71 | 92 |
> +-+-+
> 16 rows in set (0.00 sec)
>
> So there are two tables: foo_entry AS e and foo_link AS a1. I want to
> select the the single row from foo_entry with e.eid = 113 but only if
> the e.ownid = 66 OR if e.aclid is indirectly linked with 66 via the
> foo_link table.
Something like this?
select * from foo_entry where eid = 113 and ownid in (select a from
foo_link where a=66 or b=66);
Regards,
-- Raju
--
Raj Mathurr...@kandalaya.org http://kandalaya.org/
GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F
PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
