Are these supported??
Hi all, been awhile! Some may be saying "not long enough" but eh.
I recently moved a mybb forum away from mysql to postgres. Along the way
I encountered a couple of things that either didn't seem to be supported
or I'm just not doing it right.
First, the server this is on is running version 8.4.22. php is 5.6.22.
#1 Are joins supported in deletes? The same join syntax works fine as
a select.
#2 is extract supported in a select statement dealing with a table? To
explain this one, here is the error I get:
# select date_part('epoch', dateline) from mybb_adminlog limit 1;ERROR:
function date_part(unknown, integer) does not exist
LINE 1: select date_part('epoch', dateline) from mybb_adminlog limit...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
#
or ...
# select extract(epoch from timestamp dateline::timestamp) from
mybb_adminlog limit 1;
ERROR: syntax error at or near "dateline"
LINE 1: select extract(epoch from timestamp dateline::timestamp) fro...
#
Doesn't matter if I use epoch or day or anything else, they all fail with
the same error. And yes, dateline is a timestamp. WITH or WITHOUT
timezone made no difference.
So my questions are..
Does Postgres not support joins in deletes?
If not, is there a reason?
Is EXTRACT and/or date_part not supported in select calls where a table is
involved?
If not, is there a reason or a work around for selecting the epoch of a
timestamp?
Thanks!!! Miss you guys!!!
Vince.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Aug 17, 2016 at 4:33 AM, Vince Vielhaber <vev@chives.michvhf.com> wrote:
I recently moved a mybb forum away from mysql to postgres. Along the way I
encountered a couple of things that either didn't seem to be supported or
I'm just not doing it right.First, the server this is on is running version 8.4.22. php is 5.6.22.
8.4 is very old. It's been unsupported for two years already.
You can't have joins in a DELETE -- which table would it actually
delete from? You can use a subselect to do look up information from
other tables in your delete though.
EXTRACT and date_part have no idea where the data they're passed came
from. They can come from tables or other functions or expressions. The
error you quoted is indicating that dateline is of type integer
however. The syntax for EXTRACT is confusing (blame the SQL
committee...) but you don't want the extra "timestamp" keyword before
the column there -- in the examples that's part of the literal being
used to have it be read as a timestamp.
https://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 17 August 2016 at 11:33, Vince Vielhaber <vev@chives.michvhf.com> wrote:
I recently moved a mybb forum away from mysql to postgres. Along the way
I encountered a couple of things that either didn't seem to be supported or
I'm just not doing it right. First, the server this is on is running
version 8.4.22
8.4 is end-of-life, so it's time to move.
FYI, this isn't really the right place for these questions; pgsql-general
or Stack Overflow is more appropriate for topics not relating to PostgreSQL
code and design.
#1 Are joins supported in deletes? The same join syntax works fine as
a select.
Yes, but the syntax is a bit different and you can't use aliases on the
target table. You can also (unfortunately) only do inner joins.
#2 is extract supported in a select statement dealing with a table? To
explain this one, here is the error I get:
If 'dateline' is an integer, you'll have to turn it into a timestamp or
date before you can extract the epoch.
# select extract(epoch from timestamp dateline::timestamp) from
mybb_adminlog limit 1;
You can't use the typed-literal syntax
TIMESTAMP 'something'
for a column reference, bind-parameter, etc. You can only use it for
literals. Use a CAST or the PostgreSQL :: shorthand. Just remove the
"timestamp" from "FROM timestamp".
Doesn't matter if I use epoch or day or anything else, they all fail with
the same error. And yes, dateline is a timestamp.
No, it isn't. The error message says so.
Please use pgsql-general or Stack Overflow.
--
Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL
Development, 24x7 Support, Training & Services
On Wed, Aug 17, 2016 at 10:38 AM, Greg Stark <stark@mit.edu> wrote:
You can't have joins in a DELETE -- which table would it actually
delete from? You can use a subselect to do look up information from
other tables in your delete though.
We've supported having joins in a DELETE since PostgreSQL 8.1.
https://www.postgresql.org/docs/8.1/static/sql-delete.html
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Aug 17, 2016 at 4:22 PM, Robert Haas <robertmhaas@gmail.com> wrote:
We've supported having joins in a DELETE since PostgreSQL 8.1.
Er, yes. Though he does say he's trying to use the same syntax as select...
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers