Hi, everybody!
I am writing a fairly long query, that joins several (like 10-15)
tables. It is dynamically generated, and the tables in the join may be
different, depending on the criteria etc...
The problem is that I need to outer (left) join some of those tables.
The only way I know to do
Tom Lane wrote:
Dmitry Tkach [EMAIL PROTECTED] writes:
I suppose, this is because the planner takes the order, in which the
tables appear in the explicit joins as some kind of a hint to how I want
that query to be executed,
It's not a hint, it's a requirement. In general, changing
Dave Dribin wrote:
Hi, I'm having trouble with what I think should be an easy query. For
simplicity, I will use a CD database as an example. Each CD may have
multiple genres. Here's some sample data:
Artist Title Genres
--
Tom Lane wrote:
I put up a proposal in pgsql-hackers to change this behavior:
http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php
If we made that change then the wrong way of defining the default
would fail in an obvious fashion --- the 'now' would get reduced to a
particular time
Tom Lane wrote:
Dmitry Tkach [EMAIL PROTECTED] writes:
Does it mean that the *application* (not the database) user would then
have to know the exact specific way to represent the current time in his
data entry form?
Such an application looks like (how do I say it politely?) not a very
user
Data entry. You don't necessarily have the option to invoke a function,
as opposed to just sending a string for the datetime input parser.
(It would be rather difficult for an application to allow this one case
without permitting SQL-injection attacks, I'd think.)
Does it mean that the
[EMAIL PROTECTED] wrote:
Quoting Dmitry Tkach [EMAIL PROTECTED]:
What about:
CREATE VIEW my_view AS SELECT b,c from
(SELECT a, b FROM table1 WHERE b=1) as my_ab,
(SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
This looks like what you are trying to do, and doesn't use
The first query is able to use the index on nr_proponente, because the
condition involves that column directly, the second query is not,
because the index only contains the values of nt_proponente, not results
of trunc(..)/
Try replacing that condition with something like
pa.nr_proponente
[EMAIL PROTECTED] wrote:
SELECT my_c.b, my_c.c FROM (SELECT table2.b, table2.c FROM table2, (SELECT
table1.a, table1.b FROM table1 WHERE (table1.b = 1)) my_ab WHERE (table2.a =
my_ab.a)) my_c;
You were doing what I wanted to avoid - you are using a SUBSUBSELECT.
What about:
CREATE VIEW
I think, your example would work if you replaced the new.id in the rule
with curval ('main_id_seq');
... but see Tom's earlier reply - this is still not a very good thing to
do... For example, it won't work if you try to insert into main anything
with explicitly specified id (not generated by
Josh Berkus wrote:
Well I suppose I could try TCL. The problem is that there is little to
no documentation on postgres stored procedures in TCL and I've never
even seen the language before. None the less, I'll look into it. It's
almost worth it. If that fails, I may even try perl shudders.
Yudie wrote:
Hi,
Anyone know how the procedure or commands to copy table to another
database.
or querying from another database if possible?
thank you
yudie
Something like this, perhaps?
psql -d first_database -c '\copy mytable to stdout' | psql -d
second_database -c '\copy mytable from
Terence Kearns wrote:
CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS
'DECLARE
BEGIN
RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool;
END;'
LANGUAGE 'sql';
produces this error
ERROR: parser: parse error at or near RETURN at character 20
I'm trying to create a
Gary Stainburn wrote:
Hi folks,
I'm back with my lnumbers table again.
nymr=# \d lnumbers
Table lnumbers
Column | Type | Modifiers
---+---+---
lnid | integer | not null
lnumber | character varying(10) |
I can't help you explain what is going on with this query - like you, I
am puzzled by the fact that it actually works, and have no idea how it
is interpreted, and what it is doing...
The right way to do what you want, I think, would be something like:
delete from mytable where not exists
The problem is I don't know how to convert the following pseudo code to valid
SQL:
create trigger unique_current on insert/update to lnumbers
if new.lncurrent = true
update lnumbers set all other records for this loco to false
I see... The bad news is you can't do it directly... You can
But what makes you think, that it is quicker to scan 10 tables with 25
million rows each than it would be to scan one table with 250 million rows?
It won't... In fact, it will be *longer*.
If you have a way to narrow the number of tables to scan down based on
the condition, you can have that
Gary Stainburn wrote:
Hi folks,
I've got a table holding loco numbers and an id which references the locos
table. How can I sort this table, so that numeric values appear first in
numerical order followed by alpha in alpha order.?
What about
select lnid,lnumber,lncurrent from
(select *,
Hi, everybody!
I was trying to formulate a sql query, that I now think is impossible :-(
I just thought, I'd run it by you before I give up - perhaps, you guys
would have some ideas...
Suppose, I have a table like this
create table test
(
stuff int,
stamp timestamp
);
Now, I want to count
You must have your sequence out of date with the content of the table
(so that the next value in the sequence has already been inserted).
One way to get into a situation like that is loading the table data with
COPY (the input contains the pks, and the COPY command does not update
the sequence,
You need to elaborate on your logic some more, and state exactly what you
would want in the A,B,C case above. Does B get lumped with A or with C?
It is within 24 hours of both, after all. Does C not get lumped in with
B simply because B has already been lumped in with A?
Yes. The first
There is no such thing, as far as I know :-(
Here is the poor man solution I used to emulate this 'nowait' behaviour:
create table master_lock
(
projectid text primary key,
locker int
);
Now, the application first acquires an exclusive lock on the table,
then, while the table is locked it
What about lpad?
select lpad (7, 2, 0) || '-' || lpad (9, 2, '0') || '-2003';
?column?
07-09-2003
(1 row)
I hope, it helps...
Dima
Yasir Malik wrote:
Thank you so much! But my problem is that when I do
to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr,
'')
On Wed, 9 Jul 2003, Dmitry Tkach wrote:
Date: Wed, 09 Jul 2003 18:40:37 -0400
From: Dmitry Tkach [EMAIL PROTECTED]
To: Yasir Malik [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Datatype conversion help
What about lpad?
select lpad (7, 2, 0) || '-' || lpad (9, 2, '0') || '-2003
You must have dropped and recreated the function after your trigger was
created...
You need to recreate the trigger now, so that it picks up the new
function id.
In the future use 'CREATE OR REPLACE' to modify a function instead of
DROP and CREATE - this will make sure the modified function
I am afraid, this looks even uglier then your second solution, but
should work, and be quicker...
-- You need this to avoid having to rescan the whole table for each
customerid every time and resort the results
create index customer_txstamp_idx on bank_account (customer_id, ts);
select ba.*
Rado Petrik wrote:
Hi,
I have table users;
id name bin
--
1 xx 9
dec(1) dec(9) = dec(1)
bin(0001) bin(1001) = bin(0001)
This query is bad.
SELECT name FROM users WHERE id_user=1 AND (bin 1)
This query return
Warning: PostgreSQL query failed: ERROR: right-
I think, something like this should work:
select o.id,o.num_purch,o.program from orders o left join lists l on
(l.order_id=o.id) where
(l.status is null or l.status!='processing') and o.uid=1 and o.status!='closed'.
(l.status is null should take care about the case when there is no matching
SQL92 says:
direct select statement: multiple rows ::=
query expression [ order by clause ]
[...]
3) Let T be the table specified by the query expression.
4) If ORDER BY is specified, then each sort specification in the
order by clause shall identify a column of
Vernon Wu wrote:
12/02/2003 2:24:49 PM, Dmitry Tkach [EMAIL PROTECTED] wrote:
You don't want it to be serial - just make it 'person_id in'
Any reasons?
Yeah... Two of them:
- It does not make sense for a serial column to reference other tables -
the only purpose of serial
You don't want it to be serial - just make it 'person_id in'
I hope, it helps...
Dima
Arunachalam Jaisankar wrote:
This is a multi-part message in MIME format.
--=_NextPart_000_0005_01C2D1EE.61998D70
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding:
Does any one know what is the reason not to put this logic into date_trunc () function?
It seems to work with pretty much *any* unit imaginable, *except* for 'week'...
Dima
Bruno Wolff III wrote:
On Thu, Sep 26, 2002 at 11:55:48 -0400,
Jean-Luc Lachance [EMAIL PROTECTED] wrote:
How about:
First of all, try replacing the username/foldername indexes on operator_messages with
a single combined
index on, say (username,foldername)...
It is still not clear to me why it decides not to use one of those indexes you have
(it would be less efficient than a
combined index, but still better
I think this should work:
alter table mytable owner to postgres;
grant all on my table to public;
revoke delete on my table from public;
I hope, it helps...
Dima
Rajesh Kumar Mallah. wrote:
Hi ,
I have a created a database and a table in it,
I want to prevent DELETES on the table in
Jeroen Olthof wrote:
Hi,
When developing applications is a good thing to create abstraction between
different layers
The one concerning the database would be the persistence layer. To create
such abstraction I want all mij datababase activitie runned through
functions. But how can I
Sorry, the previous message was wrong...
This is better:
create function month_start (date) as
'select date_trunc ('month', $1)::date;'
language 'sql';
create function month_end (date) as
'select month_start ($1) - 1 + interval '1 month';
language 'sql';
I hope, it helps...
Dima
David
what about
CREATE TABLE one (
fileda INTEGER,
filedb INTEGER,
filedc INTEGER );
CREATE VIEW two AS SELECT * FROM one;
?
Dima
PostgreSQL Server wrote:
HI!
I'm new to postgres. I need to have a table as a copy of another one.
Example:
CREATE TABLE one
fb.b=0
It results in the same query plan (seq scan on fbr).
Dima
-Original Message-
From: Dmitry Tkach [mailto:[EMAIL PROTECTED]]
Sent: Thursday, July 11, 2002 3:51 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: [SQL] Please, HELP! Why is the query plan so wrong???
Hi
for this problem?
This would be really great!
Dima
Jie Liang
-Original Message-
From: Dmitry Tkach [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 12, 2002 7:34 AM
To: Jie Liang
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [SQL] Please, HELP! Why is the query plan so wrong???
Jie
Hi, everybody!
Here is the problem:
test=# create table fb (a int, b int, c datetime);
CREATE
test=# create table fbr (a int, c datetime, d int);
CREATE
test=# create unique index fb_idx on fb(b);
CREATE
test=# create index fbr_idx on fbr(a,c) where d is null;
CREATE
test=# set
40 matches
Mail list logo
