0

I have a PostgreSQL table containing some point of interest data. I have a second table with overlapping continent polygons. The following code splits the POI into the continents, but instead of extracting them, the continent ID gets written into a column of the POI data. The query is working so far.

select t3.*, array_to_string(array_cont, '') as conts from
(select ogc_fid, array_agg(conts) as array_cont from
(SELECT t1.*, cont.id AS conts
    FROM continents cont, poi t1
    WHERE st_contains(cont.geom, t1.wkb_geometry)
    order by conts)t1
    group by ogc_fid)t2, poi t3
where t2.ogc_fid = t3.ogc_fid
order by conts;

But when I run this query inside a CREATE VIEW statement, I get the following error:

CREATE OR REPLACE VIEW poi_split AS
select t3.*, array_to_string(array_cont, '') as conts from
(select ogc_fid, array_agg(conts) as array_cont from
(SELECT t1.*, cont.id AS conts
    FROM continents cont, poi t1
    WHERE st_contains(cont.geom, t1.wkb_geometry)
    order by conts)t1
    group by ogc_fid)t2, poi t3
where t2.ogc_fid = t3.ogc_fid
order by conts;    

ERROR:  syntax error at or near "create"
LINE 1: create or replace view poi_split as
        ^

Sorry for the bad formatting, but as far as I can see, it is no syntax error involved, as the query runs perfect without CREATE VIEW AS. It's even possible to run

CREATE OR REPLACE VIEW poi_split AS SELECT * FROM poi;

What am I doing wrong?

  • 2
    I suspect a non-printable character at the start of your input. Are you using psql -f to load the definition from a file, pasting the code, reading it from stdin, ... ? – Craig Ringer Feb 9 '14 at 4:00
  • Normally I load it from a file using psql -f, but while debugging it, I copy/paste the code from the SQL file into the psql prompt. Do you think it's something about encoding? – leak_my_brain Feb 9 '14 at 10:15
  • Possibly not so much encoding as unprintable chars like RTL marks, etc. What editor are you using? Also, what's the PostgreSQL version involved here? – Craig Ringer Feb 9 '14 at 10:17
  • I am using Scratch Editor in elementary OS (Ubuntu based). But the original Code came from a colleague, so maybe there was a problem when I transferred the code. PostgreSQL Version is 9.1 with Postgis 2.0. – leak_my_brain Feb 9 '14 at 12:26
  • Ok, in the meantime, I got it running: I rewrote the query line by line and it seems that there was such a character problem as you suggested. – leak_my_brain Feb 9 '14 at 12:28
1

Ok, I got it running: I rewrote the query line by line and it seems that there was a problematic character hidden in the SQL file. Thanks, @Craig Ringer!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.