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 requirem
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 tha
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
--
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 *appli
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
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
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 B
[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'
[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 m
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 th
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 stdi
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 .
And what's
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 log
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
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 (select
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) |
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 functi
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 *, cas
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 lo
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 (earli
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,
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 t
doesn't - that's why I suggested it :-)
Dima
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 he
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,
'')
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 keep
Josh Berkus wrote:
Folks,
I have several functions in a 7.2.4 database that I accidentally REPLACEd
while logged in as the superuser, instead of the db owner. As a result, the
db owner can no longer modify those functions -- they belong to the
superuser.
As this is a production database, I
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 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.* fro
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 row
SQL92 says:
::=
[ ]
[...]
3) Let T be the table specified by the .
4) If ORDER BY is specified, then each in the
shall identify a column of T.
Then it looks like postgres behaviour is still not compliant, if I read it correctly, because
select x from myt
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 pu
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: quoted-pr
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
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 t
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
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 S
Well, month_end could be more straightforward as something like
select month_start ($1) + interval '1 day' - interval '1 month';
Dima
David Stanaway wrote:
> Here are the 2 functions I have at the moment. I was wondering if
> someone had a better way?
>
> CREATE OR REPLACE FUNCTION month_start
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
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 TABL
ist) :-(
Perhaps, somebody, familiar with this code could come up with a patch
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
nd
fb.c=fbr.c and fbr.d is null) where 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]
>Subj
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 enable_seqsca
42 matches
Mail list logo
