SQLite syntax error (Conversion from PostgreSQL)

Question

I am trying to merge some contents from table1 and table2 into another table3. I have the following query up and running in PostgreSQL:

CREATE TABLE table3 AS SELECT 
table1.orig_zon AS orig_zon,
table1.dest_zon AS dest_zon,
table1.same_zon AS same_zon,
table1.adjacent AS adjacent,
table1.distance AS distance,
table1.da_ivtt AS da_ivtt1,
table1.da_ovtt AS da_ovtt1,
table1.tr_avail AS tr_avail1,
table1.tr_ivtt AS tr_ivtt1,
table1.tr_ovtt AS tr_ovtt1,
table1.tr_cost AS tr_cost1,
table1.au_cost AS au_cost1,
table1.sr_ivtt AS sr_ivtt1,
table1.sr_cost AS sr_cost1,
table2.da_ivtt AS da_ivtt2,
table2.da_ovtt AS da_ovtt2,
table2.tr_avail AS tr_avail2,
table2.tr_ivtt AS tr_ivtt2,
table2.tr_ovtt AS tr_ovtt2,
table2.tr_cost AS tr_cost2,
table2.au_cost AS au_cost2,
table2.sr_ivtt AS sr_ivtt2,
table2.sr_cost AS sr_cost2,
FROM table1, table2
WHERE ((table1.orig_zon = table2.orig_zon)) AND ((table1.dest_zon = table2.dest_zon));

I have tested the query for a very small dummy dataset and the result is as desired. However my actual database is in SQLite and when I run this in the sqlite3 via Linux shell I get an error as:

Error: near "table1": syntax error

Can anyone point me to the modifications I need to make in order to make this work in SQLite? Thanks for the help!

Edit1: After removing the trailing comma (see Craig's comment below) the issue persists.

Edit2: After playing around a bit I found the fix. Seems very silly but if I were to pass the contents of the query in a single line the statement works!

table2.sr_ivtt AS sr_ivtt2,
table2.sr_cost AS sr_cost2 FROM table1, table2 WHERE ((table1.orig_zon = table2.orig_zon)) AND ((table1.dest_zon = table2.dest_zon));

Oh! The bane of typecasting!!

SQLite's console is newline-terminated, so edit2 makes sense. — Craig Ringer
– Craig Ringer, Commented Oct 6, 2012 at 20:37

Craig Ringer · Accepted Answer · 2012-10-06 08:15:32Z

3

SQL doesn't permit a trailing comma in the SELECT list.

table2.sr_cost AS sr_cost2,
FROM table1, table2

should be:

table2.sr_cost AS sr_cost2
FROM table1, table2

answered Oct 6, 2012 at 8:15

Craig Ringer

328k83 gold badges741 silver badges819 bronze badges

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

3 Comments

sriramn Over a year ago

Thanks for pointing that! But even after I change it the issue persists! Same error... Any idea why?

Craig Ringer Over a year ago

@RazorXsr Nope. Try progressively simplifying the query until it works, then figure out what the last part you took out was.

sriramn Over a year ago

Your fix worked... It was just that I had to change the typing a bit... See Edit 2 above... Thanks for the input!

Marcin Wasiluk · Accepted Answer · 2012-10-06 08:14:10Z

1

You need to delete extra comma

table2.sr_ivtt AS sr_ivtt2,
table2.sr_cost AS sr_cost2
FROM table1, table2

answered Oct 6, 2012 at 8:14

Marcin Wasiluk

4,8844 gold badges42 silver badges46 bronze badges

Collectives™ on Stack Overflow

SQLite syntax error (Conversion from PostgreSQL)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related