in air for you:
SELECT distinct on (p.name) p.name, l.name, datetime
FROM location l
INNER JOIN product_move m ON m.destination_location = l.id
INNER JOIN product p ON m.product_id = p.id
WHERE
m.datetime < '2012-12-31'
ORDER BY p.name, datetime DESC
--
Jaime Casanova www
>new_data" seems better and i
guess you can use hstore functions on it
(http://www.postgresql.org/docs/9.0/static/hstore.html)
The other one uses arrays to store column names, old values, new
values, still more parseable
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQ
On Thu, Apr 8, 2010 at 2:51 PM, Andreas wrote:
> or rank() over (order by object, ts) % 2
use dense_rank() instead
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
Sent via pgsql-sql mail
n if the FooKey exists in the ToDo table for the user.
you should do this when inserting data (with triggers of course)
you can't use rules because a RULE ON SELECT you only can use one
SELECT, no INSERT, UPDATE nor DELETE
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
A
On Thu, Nov 26, 2009 at 6:26 AM, aymen marouani
wrote:
>
> How can I load the auto_explain module ?
>
http://www.postgresql.org/docs/8.4/static/auto-explain.html
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ec
On Tue, Dec 16, 2008 at 12:16 PM, Tom Lane wrote:
> it's been like
> that for so long that I'm worried about how much user code we'll break
> if we do.
>
code making something like that is actually broken, what a "fix" here
will do is making that obviou
o?
>
set timezone = 'GMT' (or set the guc in postgresql.conf)
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
rals again?
> > maybe i can inject "select * from tab where intcol = intcol; set
> > allow_literals all; add any query you want"
>
> How do you inject this? How would the application looks like where
> this can be injected?
>
ok... point taken
--
regards,
Jaime Cas
inject "select * from tab where intcol = intcol; set
allow_literals all; add any query you want"
--
regards,
Jaime Casanova
Soporte de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
On Feb 5, 2008 2:39 PM, Li, Jingfa <[EMAIL PROTECTED]> wrote:
> check the typo -- "ilike"
>
that's not a typo, it's "case insensitive LIKE"
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build
HINT: No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>
did you read the release notes?
http://www.postgresql.org/docs/current/static/release-8-3.html
"""
Non-character data types are no longer automatically cast to TEXT (Peter, Tom)
&qu
sorry, i resubmit to the list
On 7/18/07, Jaime Casanova <[EMAIL PROTECTED]> wrote:
On 7/18/07, Norm Garand <[EMAIL PROTECTED]> wrote:
> Thanks Jaime:
>
> I tried using the CASE function and it still didn't work.
>
> What I need to do is set a conditional,
; 0 else t_stoc = 0 end;
baan$# $$ language sql;
CREATE FUNCTION
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiot
ither, or they have?
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
es anybody know what's the problem is?
maybe m$ knows
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idi
-- Forwarded message --
From: Carlos Alberto Silva <[EMAIL PROTECTED]>
Date: Jun 19, 2007 2:00 PM
Subject: Re: [pgsql-es-ayuda] Ejecutar \copy desde VB
To: Jaime Casanova <[EMAIL PROTECTED]>
Yo lo haría de otra manera. Una rutina q lea el archivo de texto en VB
(nad
erified with EMS Manager and it tells me the DDL for
the function in the database is set to stable. Is there something I can do
to tell PostgreSQL that I really did mean stable?
maybe this is silly but you can verify what the database thinks of the
function selecting from pg_proc
select pr
t
OPEN csr FOR l_query;
l_query := l_query || a_from_clause
|| 'where ename in (' ||
quote_literal(user_input) || ')'
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof progr
On 7/12/06, aurora <[EMAIL PROTECTED]> wrote:
Yes that helped!
reindex index users_email_address_text_key;
what version of postgres is this? there have been some bug fixes that
involved indexes on text columns.
wich locale are you using?
--
regards,
Jaime Casanova
"Programmi
CREATE the index again? it certainly sounds to an index corruption.
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the univ
EXECUTE 'SELECT count(*) FROM ' || tablename INTO rowcount;
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and bette
is the default PG behaviour (READ COMMITTED)
>
yes, it is
> Question:
> If I do not explicitly START TRANSACTION before the SELECT, will this READ
> COMMITTED XA behaviour still be in effect?
>
yes. all statements not executed inside a transaction block are in an
implicit transact
cs/current/static/sql-select.html
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
the transaction level after the begin and before every
other statement... after the begin you have a select that invoke your
function so that set is not the first statement...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 6: explain analyze is your friend
rsion nor postgres version, you didn't show your own
results either (and that is a good start to try to see the reason of
your problem...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
using the '<' operator as it suggest.
>
> Can somebody help me?.
> thanks in advance
>
> Mauricio Fernández A.
> Ingeniero de Sistemas
> U. Autónoma de Manizales
>
>
that's why is a bad idea to develop in version that is superior to the
one you will use
miss it before it goes back to the end user.
> I also don't think this has been made particularly clear in changes.
>
> Peter Childs
>
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
FOR EACH ROW EXECUTE PROCEDURE insertInto();
>
>
> I would like to implement something like this but I
> don't know how to do.
>
> Thank you for you help!
>
> Jakob
>
>
>
>
>
>
> ___
> Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de
On 12/20/05, Michael Burke <[EMAIL PROTECTED]> wrote:
> Is it possible to execute a SELECT query as an argument to a function?
>
have you tried?
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 2:
founds no unique index on orders(order_code)
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
at you need is to know how many times a single row is
duplicated so i think what you need is something like this:
SELECT fld1, COUNT(DISTINCT fld1)
FROM (SELECT ROW(*) as fld1 FROM mytable) AS foo
GROUP BY fld1;
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
-
distinct with...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
k. How can I do it with Postgres?
>
> Thanks,
> Otto
>
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
On 12/5/05, Matthew Peter <[EMAIL PROTECTED]> wrote:
> it's in a loop so there's an extra comma at the end so i was thinking i
> could put in a throw away value to keep the update from breaking if there's
> an additional comma
>
> Jaime Casanova <[EMAIL P
"throw away value"?
are you trying to update and if the row doesn't exist then insert? if
that is you can do a function in plpgsql and use exceptions for
that... there is an example in the manuals
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(
>
> (Is this what the "FOR UPDATE OF tablename" clause is for?)
>
> Mark
>
INSERT INTO table SELECT
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
, if it right behaviour?
>
> --
> engineer
>
--- extracted from TODO ---
o Allow an alias to be provided for the target table in UPDATE/DELETE
This is not SQL-spec but many DBMSs allow it.
--- end extracting ---
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
ogs (breed varchar)INHERITS (animals);
ALTER TABLE dogs ALTER COLUMN type SET DEFAULT 'DOG';
CREATE TABLE birds (bool hasFeathers) INHERITS (animals);
ALTER TABLE birds ALTER COLUMN type SET DEFAULT 'BIRD';
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
(816) 578-4704 - Home
> (816) 578-4215 - City
> (816) 564-0769 - Cell
>
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilat
ation, btree index rows can only have certain size
(i don't remember the numbers now)... so if you exceed that size (and
in a text field you can) you will get an error... you have to control
within your application that...
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 6: explain analyze is your friend
nd looks like.
>
Make 3 rules (INSERT/UPDATE/DELETE), 4 if you want SELECT as well...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
cho
t of
the 52nd or 53rd week of the previous year. For example, 2005-01-01 is
part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd
week of year 2005.
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 7
--- end extracted text ---
into temp m1;
select m1.object1_id, m1.object2_id, m2.object1_id, m2.object2_id
from m1, c_mappings m2
where m2.object1_id in (select aliases_of(m1.object2_id));
just an idea...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)-
| name5
> 6 | name6| 2 | name2
>
> (C) FUNCTION: aliases_of
> CREATE FUNCTION aliases_of(bigint) RETURNS SETOF bigint
> AS 'SELECT $1
> UNION
> SELECT object1_id FROM alias_table WHERE object2_id = $1
>
S $session_update$
> > [..function body..]
> > $session_update$ LANGUAGE plpgsql;
I think it should be:
CREATE FUNCTION session_update() RETURNS trigger AS $$
[..function body..]
$$ LANGUAGE plpgsql;
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
you know all your queries are going to search for one, but not the other?
>
I guess it will be effective only if you know wich value will be less
frequent... on the other value a sequential scan will be a win, isn't
it?
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
-
e he feels they are/should be unique. Given that they are rows
> from a logfile, I'm not convinced this is the case.
>
If this a log he will need a timestamp field to be usefull, making
that field part of the primary key and letting the data out of the
primary has more sense to me.
--
this sintaxe with 7.4.7 release and did not works!
>
> Thanks in advance!
>
8.0.x and later
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
lmerchandise m
WHERE m.caseid = tblcase.caseid AND m.clientnum::text =
tblcase.clientnum::text)
--
Regards,
DBA* Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
70
>
Actually, i don't see any difference between problem and results but maybe
select * from yourTable order by class, score desc
regards,
Jaime Casanova
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
3
> Banana 4
> Apple 5
>
> Is there a way I can get the following results:
>
> Apple 2
> Orange 1
> Banana 1
> Apple 1
>
A function?
regards,
Jaime Casanova
---(end of broadcast)---
TIP 3: if posting/reading thro
the function has to be called once per row in
academico.aca_t_alumnocurso that matches the where.
but I don't know any easy way of doing that
for each row in a query.
any ideas?
regards,
Jaime Casanova
_
Do You Yahoo!?
Información de E
> > anybody has an experience or know how to solve it,
> please help me. Thanks.
> >
>
> I believe that the contrib module dblink will do
> what you want, but I've never
> used it.
>
What about using schemas not databas
o proceed
> concurrently too (though
> not VACUUMs). See
>
>
http://www.postgresql.org/docs/7.4/static/explicit-locking.html
>
> regards, tom lane
>
Hi,
Talking about lock tables there is a way to do a
select ... for update
and then a
update .. where c
Hi all,
I ask: "why not to disallow nulls in boolean fields?". It was a question not a proposal.
The explanation was clear to me. Nulls are not values but the absence of a known value.
It is comparable to the state of a c (or almost any other programming language) variable that had not been in
Hi all,
Tri-valued boolean?? that's not against boolean concept?? i'm not saying that SQL is wrong nor Postgresql has to go beyond standard, i'm just trying to understand this stuff.
Why not disallow the ability of boolean fields to be null?
thanx in advance,
Jaime CasanovaMichael Glaesema
ni_codigo =
$3)
else cic_codigo = $4
end
' LANGUAGE 'sql'
but when $4 is null the function executes the else part and what i want to
do is to execute the when null. Can you help me???
Thanx in advance,
Jaime Casanova
_
You're not expressing yourself clearly, because as far as I can
understand you there are guaranteed to be no such results.
regards, tom lane
ok,
the output i want is equivalent to:
SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
CPA.cpa_fechavencimiento
FROM rec_m_cuadropagos CPA, re
s test certainly fails at every row of CPA.
Perhaps you meant "= ANY"?
Not really becuase ANY has the same efect that IN and what i want is all the
results that are equal in all the rows in any of the cur_paralelo values.
But must be in all the cur_paralelo or nothing.
thanx in advance
"Jaime Casanova" <[EMAIL PROTECTED]> writes:
brings 'A ', 'B ', 'C ' well when i cHange the real query
Given that you spelled it like that, I wonder whether you aren't
confused about the behavior of cross-data-type comparisons. If
one column
> On Mon, 14 Jun 2004, Jaime Casanova wrote:
>
> > On Mon, 14 Jun 2004, Jaime Casanova wrote:
> >
> > > i have an strange result here, i'm using 7.4.2 on redhat 8
> > >
> > > i have a query like this
> > >
>
On Mon, 14 Jun 2004, Jaime Casanova wrote:
> i have an strange result here, i'm using 7.4.2 on redhat 8
>
> i have a query like this
>
> SELECT CPA.rub_codigo, RUB.rub_descripcion, CPA.cpa_valor,
> CPA.cpa_fechavencimiento
> FROM rec_m_cuadropagos CPA, rec_m_rubro
Jaime Casanova wrote:
Hi all,
i have an strange result here, i'm using 7.4.2 on redhat 8
i have a query like this
[snip]
this query proves that its result is 'A', 'B', 'C'
any idea, is something wrong in my thinking?
Do you have any null values involved? That mi
CPA.ent_codigo = 1 AND CPA.sec_codigo = 1 AND CPA.ani_codigo =
'2004-2005' AND
CPA.cic_codigo = 1 AND CPA.esp_codigo = 0 AND CPA.cur_codigo = 1 AND
CPA.cur_paralelo = 'A '
then it brings the result that it would but when i do
cur_codigo = 1
this query proves that its result is 'A', 'B', 'C'
any idea, is something wrong in my thinking?
thanx in advance,
Jaime Casanova
_
The new MSN 8: advanced
p_numero), 0) + 1) from rec_t_ordenpago) part with
nextval('seq1') and that work.
So my question is, is this a postgreSQL limitation or is the way it have to
happen? why?
thanx in advance,
Jaime Casanova
_
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
---(end of broadcast)---
TIP 8: explain analyze is your friend
hi all,
i have a function of a user defined type it returns a record of values but
just a row.
select * from rec_f_consultar_alumno(a.ent_codigo, a.alu_codigo)
what i want to do now is a select in wich i can do a seq scan or an index
scan of a table and
pass the values i retrieve into the functi
Sorry it was my mistake, i was using psql from 7.2.2
_
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
---(end of broadcast)---
TIP
Sorry it was my mistake, i was using psql from 7.2.2
_
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
---(end of broadcast)---
gger_46768,
RI_ConstraintTrigger_46769
that is the same i got from 7.2. I think 7.3 version is better and much
informative.
why the hackers go back in this?
thanx in advance,
Jaime Casanova
_
MSN 8 helps eliminate e-mail viruses. Get 2 months FR
Hi all, is there a way to set the isolation level to something like the sql
standard dirty read.
Thanx in advance,
Jaime Casanova
_
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features
Hi all,
I have a similar problem, i'm connecting to postgresql with th psql-odbc,
create a temp table outside the transaction, insert into the temp, commit
then i try to use the temp and it isn't there.
Maybe an odbc problem?? are you using the odbc?
___
73 matches
Mail list logo
