Postgresql – Postgres/PostGIS is returning data from a table that doesn’t exist (I’m a newbie — show me why I am wrong)

postgispostgresqlselect

I am following a tutorial for PostGIS. I have uploaded all the example datasets (all shapefiles) into a PostgreSQL database, although I have modified the names so they do not all match the names of those in the tutorial. I then ran the following code from the tutorial:

df = pd.read_sql_query('''
  SELECT
  subways.name AS subway_name,
  neighborhoods.name AS neighborhood_name,
  neighborhoods.boroname AS borough
  FROM neighborhoods AS neighborhoods
  JOIN stations AS subways
  ON ST_Contains(neighborhoods.geom, subways.geom);''', engine)

This worked and returned the expected output. But when I went back through this query I noticed that the table "subways" was referenced (maybe this is where I am confused) but I don't have a table named "subways" because I had titled this table "stations". Indeed, here is the list of available tables in the db:

pd.read_sql_query('''SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'public';''',engine)

       table_name
   0   geography_columns
   1    geometry_columns
   2     spatial_ref_sys
   3       neighborhoods
   4      raster_columns
   5    raster_overviews
   6             streets
   7               solar
   8            stations
   9              census
   10           vehicles

So, why is this query working?

I think I am asking for rows from a table named subways with the subway.name in the SELECT portion of the query, isn't it true?

Best Answer

FROM ...
     JOIN stations AS subways
    ...

This is called an alias. You aliased stations as subways. So for the rest of the query (ON, SELECT part) the table is called subways and you can only reference it as subways.

Related Solutions

Postgresql – To select from all tables inside the schema in PostgreSQL

You can't do it directly as one can not write a 'normal' query if the table names are not known (ie. coming from some variable or subquery). But you can build and execute a dynamic SQL statement for this. For example, if you need the column 'name' from every table, you can do the following (inside a PL/pgSQL function):

FOR i IN SELECT table_name 
           FROM information_schema.tables 
          WHERE table_schema = 'your_desired_schema'
LOOP
    sql_string := sql_string || format($$
        -- some whitespace is mandatory here
        UNION
        SELECT name FROM %I
    $$,
    i.table_name);
END LOOP;

EXECUTE sql_string;

In this form it won't work however, since you cannot SELECT in plpqsql unless you do it into a variable. You can either create a temporary table for this, loop over the results (in an other FOR loop), or - not using UNION - just return in every iteration, depending on your needs.

And, of course, this presumes that you want to select a single column (or more than one, but always with the same name and type) from all the tables. If you simply need all the data from every table, then the tables must have the same structure (the same column types in the same order, with the same names).

Notes:

the format() function was introduced in version 9.1
I've omitted some mandatory elements of any PL/pgSQL block to keep it simple
the earlier version mentioned using a DO block. The problem with it is that you cannot simply use SELECT there to return rows, as it was pointed out in another answer.

Postgresql – How to implement a dynamic string into a (prepared) sql statement

The bad news: Prepared statements do not work like that. Parameters can only be data, not code or identifiers.

The good news: you do not need parameters for a ALTER TABLE statement at all.

final String sqlps = "ALTER TABLE filen ADD COLUMN gn_id integer";

...
    ps = con.prepareStatement(sqlps);
    ps.execute();

Your column needs a data type. I added integer on suspicion. Possibly add a NOT NULL constraint after that.
Friendly advice: do not use quoted upper-case column names in Postgres if you can avoid it. Use legal, lower case, unquoted names. Makes your life easier.

Best Answer

Related Solutions

Postgresql – To select from all tables inside the schema in PostgreSQL

Postgresql – How to implement a dynamic string into a (prepared) sql statement

Related Question