Thread: inconsistent automatic casting between psql and function
Hello all,
I'm experiencing a strange problem with postgresql 8.3.4.
I have the following table
tx_queue
txid serial
securityid integer
portfolioid integer
datequeued timestamp default now()
tradedate date
numshares numeric(25,7)
transactiontype char(1)
tradeprice numeric(25,7)
every time a new tx is created in the table I need to check if it's the result of 2 previous transaction being aggregated:
I receive 2 tx with the following values:
securityid, portfolioid, tradedate, numshares, transactiontype, tradeprice
2, 1, '2008-12-08', 2, 'B', 15.23
2, 1, '2008-12-08', 6, 'B', 15.23
Later I'll receive another tx:
2, 1, '2008-12-08', 8, 'B', 15.23
This isn't a new trade but just the sum of the previous 2 it should be therefore ignored.
To create the tx in the table I use a function which receives all the values and runs the following query to check whether it's a sum of previous txs. (The tradedate is passed as a timestamp)
SELECT INTO vpsum sub1.possible_sum
FROM (
SELECT tq.securityid, date_trunc('hour', tq.datequeued) AS split_tq_time, count(*) AS cnt,
sum(tq.numshares) as possible_sum, tq.transactiontype, tq.tradeprice, tq.portfolioid
FROM tx_queue AS tq
WHERE tq.securityid= 2
AND tq.tradeprice = 15.23
FROM (
SELECT tq.securityid, date_trunc('hour', tq.datequeued) AS split_tq_time, count(*) AS cnt,
sum(tq.numshares) as possible_sum, tq.transactiontype, tq.tradeprice, tq.portfolioid
FROM tx_queue AS tq
WHERE tq.securityid= 2
AND tq.tradeprice = 15.23
AND tq.portfolioid = 1
AND tq.tradedate = '2008-12-08 02:00:00'
AND tq.datequeued + interval '1 hour' <= now() -- tx received more than 1 hour ago
GROUP BY date_trunc('hour', tq.datequeued), tq.securityid, tq.portfolioid, tq.tradeprice, tq.transactiontype
AND tq.tradedate = '2008-12-08 02:00:00'
AND tq.datequeued + interval '1 hour' <= now() -- tx received more than 1 hour ago
GROUP BY date_trunc('hour', tq.datequeued), tq.securityid, tq.portfolioid, tq.tradeprice, tq.transactiontype
HAVING count(*)>1
) AS sub1
WHERE sub1.possible_sum = 8.0000000
) AS sub1
WHERE sub1.possible_sum = 8.0000000
ORDER BY sub1.split_tq_time DESC
LIMIT 1;
LIMIT 1;
If I run this query from the psql client it works just fine. From the function it doesn't return anything.
What I discovered is that for it to work from the function I need to explicitly cast the tradedate variable to DATE (ie '2008-12-08 02:00:00'::DATE - Note the field is of type date).
It would seem that the psql client casts the value automatically.
Any reason why this should be?
This inconsistent behaviour makes code much harder to debug.
Regards,
Stefano
---------------------------------
Stefano Buliani
Covestor
Stefano Buliani
Covestor
This message is intended solely for the recipient(s) to whom it is addressed. If you are not the intended recipient, you should not disclose, distribute or copy this email. Please notify the sender immediately and delete this email from your system.
Stefano Buliani wrote:
> If I run this query from the psql client it works just fine. From the function it doesn't return anything.
> What I discovered is that for it to work from the function I need to explicitly cast the tradedate variable to DATE
(ie'2008-12-08 02:00:00'::DATE - Note the field is of type date).
>
> It would seem that the psql client casts the value automatically.
> Any reason why this should be?
> This inconsistent behaviour makes code much harder to debug.
Seems unlikely. Can't reproduce the problem assuming you're using a
quoted literal as your query shows.
=> CREATE FUNCTION date_test() RETURNS boolean AS $$BEGIN RETURN
current_date = '2008-12-09 02:00:00'; END;$$ LANGUAGE plpgsql;
CREATE FUNCTION
=> SELECT date_test();date_test
-----------t
=> SELECT current_date = '2008-12-09 02:00:00';?column?
----------t
On the other hand, if you are using variable interpolation:
CREATE OR REPLACE FUNCTION date_test2(timestamp) RETURNS boolean AS
$$BEGIN RETURN current_date = $1; END;$$ LANGUAGE plpgsql;
CREATE FUNCTION
=> SELECT date_test2('2008-12-09 02:00:00');date_test2
------------f
=> SELECT current_date = '2008-12-09 02:00:00'::timestamp;?column?
----------f
That's because a quoted literal isn't necessarily a timestamp. Without
context it could be anything, and in the context of comparing to a date
the planner probably tries to make it a date.
Your variable is definitely a timestamp though (you've said so
explicitly) so PG has to decide what it means to compare a date to a
timestamp. It decides the reasonable approach is to turn the date into a
timestamp (by adding '00:00:00' to it) and then the comparison fails.
That seems reasonable to me - you're unlikely to want to discard
information from an equality test.
The obvious question is - why are you comparing a date to a timestamp in
the first place?
-- Richard Huxton Archonet Ltd
Stefano Buliani wrote:
> Richard,
>
> understand I shouldn't be comparing a date to a timestamp. Fact is I
> need the full timestamp to process other info in the rest of the function.
>
> My question is: why is the planner casting the timestamp to date when I
> run the query from psql and the other way around from the function?
It's not. As I said, a quoted literal isn't necessarily a timestamp.
This: '2008-12-09 18:23:00' is not a timestamp.
It is an untyped quoted literal that contains something I'll grant
*looks* like a timestamp, but we can't tell what it is really supposed
to be until it's used.
SELECT length('2008-12-09 18:00:00');
Here it must be text (because we don't have a length() defined for
timestamps - see \df length).
=> SELECT date_trunc('month', '2008-12-09 18:00:00');
ERROR: function date_trunc(unknown, unknown) is not unique
LINE 1: SELECT date_trunc('month', '2008-12-09 18:00:00'); ^
HINT: Could not choose a best candidate function. You might need to add
explicit type casts.
Here it couldn't decide (\df date_trunc to see what it was choosing between)
And in the next one it guesses it has an interval (because that's what
the other thing is, I'm guessing).
=> SELECT '2008-12-09 18:00:00' + '2 hours'::interval;
ERROR: invalid input syntax for type interval: "2008-12-09 18:00:00"
So - it's not casting from timestamp to date, it's casting from
"unknown" to date in your interactive sql.
-- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> That's because a quoted literal isn't necessarily a timestamp. Without
> context it could be anything, and in the context of comparing to a date
> the planner probably tries to make it a date.
I think the real point here is this:
regression=# select '2008-12-09 02:00:00'::date; date
------------2008-12-09
(1 row)
ie, when it does decide that a literal should be a date, it will happily
throw away any additional time-of-day fields that might be in there.
Had it raised an error, Stefano might have figured out his mistake
sooner.
ISTM we deliberately chose this behavior awhile back, but I wonder
whether it does more harm than good.
regards, tom lane
Hi All, Is it possible to add unique constraint on updateable views in postgres? Thanks, Jyoti Seth
In response to Jyoti Seth : > Hi All, > > Is it possible to add unique constraint on updateable views in postgres? Add the constraint to the base-table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > > That's because a quoted literal isn't necessarily a timestamp. Without > > context it could be anything, and in the context of comparing to a date > > the planner probably tries to make it a date. > > I think the real point here is this: > > regression=# select '2008-12-09 02:00:00'::date; > date > ------------ > 2008-12-09 > (1 row) > > ie, when it does decide that a literal should be a date, it will happily > throw away any additional time-of-day fields that might be in there. > Had it raised an error, Stefano might have figured out his mistake > sooner. > > ISTM we deliberately chose this behavior awhile back, but I wonder > whether it does more harm than good. Well, it seems fine to me because it works just like the cast of a float to an integer: test=> select 1.23432::integer; int4------ 1(1 row) -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
I want to put unique constraint on columns of more than one table. -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of A. Kretschmer Sent: Wednesday, December 10, 2008 6:04 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] unique constraint on views In response to Jyoti Seth : > Hi All, > > Is it possible to add unique constraint on updateable views in postgres? Add the constraint to the base-table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql