On 26 Apr 2005 at 14:24, Christoph Haller wrote:
> Dan Langille wrote:
> >
> > The goal of my query is: given a book, what did other people who
> > bought this book also buy? I plan the list the 5 most popular such
> > books. In reality, this isn't about books,
id appears?
I'm having trouble constructing the query. I'm not even sure I can
do this in one select, but that would be nice. Examples and clues
are appreciated.
Any ideas?
Thank you.
--
Dan Langille : http://www.langille.org/
BSDCan - The Technical BSD Conference - http://w
On Wed, 8 Oct 2003, Josh Berkus wrote:
> Dan,
>
> > UPDATE commit_log_ports_elements X
> >SET X.needs_refresh = CLP.needs_refresh,
> >X.port_version = CLP.port_version,
> >X.port_revision = CLP.port_revision
>
> FROM commit_log_ports CLP
> WHERE X.commit_log_id = CLP.commit_lo
I know there is a simple solution, but I can't remember what it is. :(
I have two similar tables. I want to update the fields from one table to
contain the values form the other. The two tables are:
laptop.freshports.org=# \d commit_log_ports
Table "public.commit_log_ports"
Column |
able A, B, C
where A.Location = B.Location
and A.Item_Num = C.Item_Num
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTE
Yes it would, by a factor of 5.
P.S. but it would not show dates for which there are no logins. The
above can return zero rows. The previous example always returns
MaxDays rows.
--
Dan Langille : http://www.langille.org/
---(end of broadcast)--
users (cost=0.00..446.75 rows=1730
width=8) (actual time=0.87..195.38 rows=110 loops=1)
Filter: ((date(lastlogin))::timestamp without
time zone > (('now'::text)::date - '3 days'::interval))
Total runtime: 199.33 msec
(7 rows)
freshports=#
T
functions return the start time of the current transaction; their
values do not change during the transaction. timeofday() returns the
wall clock time and does advance during transactions.
--
Dan Langille : http://www.langille.org/
---(end of
Error occurred while executing PL/pgSQL function logincounts
WARNING: line 9 at execute statement
ERROR: parser: parse error at or near "days" at character 151
thnks
--
Dan Langille - http://www.langille.org/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
On 28 Sep 2003 at 15:45, Tom Lane wrote:
> Dan Langille <[EMAIL PROTECTED]> writes:
> > WHERE lastlogin between current_date - interval \''' ||
> > quote_literal(i - 1) || '' days\'
> > AND current_da
ggested this:
SELECT *
FROM master
WHERE NOT EXISTS (
SELECT *
FROM matches
WHERE master.pathname LIKE (matches.pathname || '/%'));
Cheers.
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP
ories
specified in MATCHES.
My first attempt, which works only if MATCHES contains one item:
SELECT *
FROM MASTER JOIN MATCHES
ON NOT (MASTER.pathname ~ ('^' || MATCHES.pathname || '/.+'));
However, if there is more than one row in MATCHES, this will not work.
Clues please
will buy you a beer should we ever meet.
I'm sure others would feel simlarly obliged.
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP 8: explain analyze is your friend
On 30 Aug 2003 at 13:59, Stephan Szabo wrote:
> On Sat, 30 Aug 2003, Dan Langille wrote:
>
> > Hi folks,
> >
> > I'm playing with SETOF on functions. But I can't get the return type
> > correct. What have I missed? A cast?
> >
> > CREATE
r
name | text
type | text
status | text
iscategory | boolean
isport | boolean
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscr
references recount_polls (pollid) on update restrict on delete
restrict;
It removes a table and the associated primary key, and removed a
foreign key from the modified recount_iptopolls table.
Comments?
--
Dan Langille : http://www.langille.org/
---(e
Unable to identify a function that satisfies the given
> argument types You may need to add explicit typecasts
>
> And tried various typecasts without any success.
>
> Any help?
I think you need to read the 7.3 documentation and release notes.
--
Dan Langille
ments, etc appreciated.
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On Fri, 21 Feb 2003, Josh Berkus wrote:
> Dan, Chad,
>
> > I see the distinction you are making.
> >
> > Maybe Tom or Josh could throw out a better answer, but I think that youve
> > called it one thing in your select and tried to group by it using a
> > syntaticly different name.
>
> This looks l
tch_list_element.watch_list_id
WHERE
watch_list.user_id = 1
GROUP BY element_id
My question: why should it not work? It's referring to the same
column as the previous two examples which do work.
--
Dan Langille : http://www.langille.org/
---(end of broadcast)-
>
> heres what it says in the docs
> *The ORDER BY clause specifies the sort order:
>
> *SELECT select_list
> * FROM table_expression
> * ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...]
> *column1, etc., refer to select list columns. These can be eithe
element.watch_list_id
> WHERE
> watch_list.user_id = 1
>GROUP BY watch_list_element.element_id
ERROR: Attribute unnamed_join.element_id must be GROUPed or used in
an aggregate function
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
: Attribute unnamed_join.element_id must be GROUPed or used in
an aggregate function
Note that in the select the table name is not mentioned but it is in
the GROUP BY. To solve the problem, you either have to name the
table in both locations or not name it in either location.
Why?
--
Dan Langille
ase,
both technical and user.
Cheers
--
Dan Langille : http://www.langille.org/
---(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 thro
d,sub.operation
test-# FROM document doc LEFT OUTER JOIN document_subscription sub
test-# ON sub.document_id = doc.id AND sub.user_id = 6;
id | title | user_id | operation
+---+-+---
1 | doc1 | |
2 | doc2 | 6 | op2
4 | doc4 | |
--
Dan
6 | op2
4 | doc4 | |
(4 rows)
>
> here're also simplified definitions of the two tables used in the query
> and some test data:
Thanks for supplying the table and data. That makes things much
easier.
--
Dan Langille : http://www.langille.org/
---
On 12 Dec 2002 at 11:01, Tim Perdue wrote:
> That command works, but now I think I have 2x as many triggers as I
> want. How do I get rid of the original triggers?
I had to do something similar and documented it at
http://www.freebsddiary.org/postgresql-dropping-constraints.php
hth
-
19.78 msec
Phew! That's fast!
--
Dan Langille : http://www.langille.org/
---(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
example of an OUTER JOIN but not the master-detail situation which
occurs elsewhere in the database.
cheers
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
= DBI -> connect ( "..");
> $sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
> $sth -> execute($a , $b );
> $sth -> finish();
> $dbh -> commit();
> $dbh -> disconnect();
IIRC, there is a dbi->quote()
On 26 Nov 2002 at 0:29, Tom Lane wrote:
> "Dan Langille" <[EMAIL PROTECTED]> writes:
> > SELECT E.name, EP.pathname, E.id, EP2.pathname, CL.id
> > ...
> > and EP2.pathname like EP.pathname || '/%'
>
> > I am still suspicous of th
On 25 Nov 2002 at 22:15, Dan Langille wrote:
> I know this can be done better, I just can't figure out how. I keep
> thinking of a subselect but I'm totally blocked. It must be bed time.
It's odd what reading the paper, relaxing with a book, and then
trying to sleep can g
ime=0.21..0.23 rows=1 loops=2)
-> Seq Scan on element_pathnames ep
(cost=0.00..2355.70 rows=119570 width=41) (actual time=0.08..858.74
rows=119570 loops=2)
Total runtime: 9464.51 msec
Clues please?
--
Dan Langille : http://www.langille.org/
---
On 17 Nov 2002 at 11:39, Dan Langille wrote:
> My existing tree implementation reflects the files contained on disk.
> The full pathname to a particlar file is obtained from the path to the
> parent directory. I am now considering putting this information into
> a field
On 20 Nov 2002 at 15:20, Dan Langille wrote:
> On 17 Nov 2002 at 14:51, Josh Berkus wrote:
>
> > Dan,
> >
> > > My existing tree implementation reflects the files contained on
> > > disk.
> > > The
> > > full pathname to a particlar file i
On 22 Nov 2002, praveen vejandla wrote:
> Dear All,
>
> Is there any way in postgresql to calculate the interval between
> two times excluding specific days,specific duration.
>
> Ex:
> timestamp1 : 2002-10-01 10:30AM
> timestamp2 : 2002-15-01 04:50PM
>
> suppose if i need the difference between
On Fri, 22 Nov 2002, Dan Langille wrote:
> Here is the actual data for the above nodes:
>
> freshports=# select * from element where id in (104503, 104504, 104505);
>id | name | parent_id | directory_file_f
I just installed 7.3rc1 and added contrib/tablefunc. I am able to get the
example in the README document to work. I am having trouble understanding
how to get my real data to behave.
The table is:
freshports=# \d element
Table "public.element"
Column
e from
http://developer.postgresql.org/cvsweb.cgi/pgsql-
server/contrib/ltree/ltreetest.sql
[2] - My application involves mirroring a file system (directories
and files). FWIW, in this instances, files are not renamed, they are
deleted and recreated elsewhere.
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
p
> 4 | 1 | home | /home
> 5 | 4 | greg | /home/greg
> 6 | 5 | etc | /home/greg/etc
> (6 rows)
>
> UPDATE tree SET name='users' WHERE id=4;
>
> SELECT * FROM mytree ORDER BY id;
>
> id | parent_id |
My existing tree implementation reflects the files contained on disk. The
full pathname to a particlar file is obtained from the path to the parent
directory. I am now considering putting this information into a field in
the table.
Attached you will find the pg_dump from my test database (2.4k)
1
test=# insert into master values (2);
INSERT 20959596 1
test=# select * from slave;
id
1
1
(2 rows)
test=# commit;
COMMIT
test=# select * from slave;
id
(0 rows)
test=#
Our hope was that after the commit, slave would retain the original
rows.
cheers
-
On Sun, 6 Oct 2002, Brian Ward wrote:
And Brian, since this *is* a mailing list, it would be polite to use a
valid email address. I suggest that you either unsubscribe or fix the
email address.
Mail to [EMAIL PROTECTED] bounces with the message: '550 Invalid
recipient: <[EMAIL PROTECTED]>'.
On Sun, 6 Oct 2002, Brian Ward wrote:
> How do I create a time stamp column in a table that default to the current
> time
> when a row is inserted?
> I tried putting in now() as the default but I have something wrong
> with the syntax or something I think
> Any one have an example of a table crea
On Sun, 6 Oct 2002, Brian Ward wrote:
> I have a table
> id int
> statusint
> create_dt datetime
>
> When users log in and out I insert a row into this table throughout the day.
> I'm trying though to come up with a querie to tell me and ordering of users
> as they report in in the mo
On Sun, 6 Oct 2002, Joe Conway wrote:
> Aasmund Midttun Godal wrote:
> > It would be very usefull to have these in sql, so that it is even easier to create
>tables with encrypted passwords.
> >
>
> See contrib/pgcrypto
See also http://www.freebsddiary.org/postgresql-7.2.php which shows how I
in
On 30 Sep 2002 at 12:09, Bruce Momjian wrote:
> Dan Langille wrote:
> > On 30 Sep 2002 at 8:54, Josh Berkus wrote:
> >
> > > As such, I'd reccommend one of two approaches for you:
> > >
> > > 1) Post some of your schema ideas here, and
that
he's right about normalizing your data.
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscrib
On Thu, 26 Sep 2002, Jordan Reiter wrote:
> Are string comparisons in postgresql case sensitive?
Yes, AFAIK.
I disagree with your comments and recommendations posted at
http://www.postgresql.org/idocs/index.php?datatype-character.html because
my testing shows that varying text and fixed test co
On Thu, 26 Sep 2002, Andrew Perrin wrote:
> No, I don't think it's supposed to be case-sensitive. In any case, whether
> it's supposed to be or not, it certainly isn't in practice.
AFAIK, they are case sensitive by design. It is the right thing to do.
---(end of broadca
e the sequence itself.
> Currently the only way I can make it work is to grab the next seq
> value and insert my own numbers into the file
Yes:
create sequence mytable_id_seq;
alter table mytable alter column id set default
nextval('mycolumn_id_seq'::text);
--
Dan Langille
ifc. What do you mean, "specified order"?
My reading of what is required:
- sort a given result set according to some criteria
- then do something like UPDATE table SET myfield = NEXTVAL
- each row in the result set would get a value one more than the
previous row
--
dded in commit_date order.
Any clues please?
thanks
--
Dan Langille
And yes, I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister com
53 matches
Mail list logo
