Postgres query returning SQL Error syntax error at "Where"

Question

I have the following query, when I execute it I'm getting an error

INSERT INTO AM_EVENT_STATUS (EVENT_STATUS_ID, EVENT_ID, TIMESTAMP,OLD_STATUS_ID, NEW_STATUS_ID, NOTE, USERNAME)
SELECT
"external_id" AS EVENT_STATUS_ID,
"external_id" AS EVENT_ID,
"timestamp"   AS TIMESTAMP,
NULL          AS OLD_STATUS_ID,
'unknown'     AS NEW_STATUS_ID,
NULL          AS NOTE,
NULL          AS USERNAME
FROM (SELECT "2cba77463c899fee1656b367e39892"
  FROM "2cba77463c899fee1656b367e39892"
  INNER JOIN (SELECT "external_id", MAX("timestamp") AS "timestamp"
            FROM "2cba77463c899fee1656b367e39892"
              GROUP BY "external_id") LATEST WHERE "2cba77463c899fee1656b367e39892"."external_id" = LATEST."external_id"
    AND "2cba77463c899fee1656b367e39892"."timestamp" = LATEST."timestamp"
    AND "2cba77463c899fee1656b367e39892"."event_type" = 'transaction_update');

Error is:

SQL Error [42601]: ERROR: syntax error at or near "WHERE" Position: 601 org.postgresql.util.PSQLException: ERROR: syntax error at or near "WHERE" Position: 601

What am I doing wrong here ? Thanks in advance.

John, make sure you explain why adding the correct tag is important instead of dictating something for no reason. It's extremely unprofessional and makes the community look bad. — user12217470
– user12217470, Commented May 1, 2021 at 19:15

Gordon Linoff · Accepted Answer · 2017-01-25 02:18:01Z

2

Change the LATEST WHERE to LATEST ON. You have a JOIN, so the correct comparison clause is ON, not WHERE.

You also need a table alias after the last parentheses.

edited Jan 25, 2017 at 2:18

answered Jan 25, 2017 at 2:10

Gordon Linoff

1.3m62 gold badges704 silver badges856 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anusha Over a year ago

nope didn't work. SQL Error [42601]: ERROR: subquery in FROM must have an alias Hint: For example, FROM (SELECT ...) [AS] foo. Position: 344 org.postgresql.util.PSQLException: ERROR: subquery in FROM must have an alias Hint: For example, FROM (SELECT ...) [AS] foo. Position: 344

Anusha Over a year ago

quick question, i'm trying to select "units_sold" field which is nested inside a jsonb field "custom_fields". How can I access that field ?

Anusha · Accepted Answer · 2017-01-25 02:23:29Z

INSERT INTO "AM_EVENT_STATUS" ("EVENT_STATUS_ID", "EVENT_ID","TIMESTAMP", "OLD_STATUS_ID", "NEW_STATUS_ID", "NOTE", "USERNAME")
SELECT
"external_id" AS EVENT_STATUS_ID,
"external_id" AS EVENT_ID,
"timestamp"   AS TIMESTAMP,
NULL          AS OLD_STATUS_ID,
'unknown'     AS NEW_STATUS_ID,
NULL          AS NOTE,
NULL          AS USERNAME
FROM (SELECT "2cba77463c899fee1656b367e39892".*
    FROM "2cba77463c899fee1656b367e39892"
    INNER JOIN (SELECT "external_id", MAX("timestamp") AS "timestamp"
                FROM "2cba77463c899fee1656b367e39892"
                GROUP BY "external_id") AS LATEST 
    ON "2cba77463c899fee1656b367e39892"."external_id" = LATEST."external_id"
    AND "2cba77463c899fee1656b367e39892"."timestamp" = LATEST."timestamp"
    AND "2cba77463c899fee1656b367e39892"."event_type" = 'transaction_update') as EVENTSTATUS;

Collectives™ on Stack Overflow

Postgres query returning SQL Error syntax error at "Where"

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related