Thread: 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.
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
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.
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
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