PostgreSQL: relation does not exist error

Question

Perhaps this error can be caused by problems with the SCHEMA, I tried to fix it, but I was completely confused.

Here is the detailed description.

There is a separate file that runs the following commands:

CREATE DATABASE weather;
CREATE SCHEMA public;
CREATE SCHEMA schema1;

SET search_path = schema1, public;

CREATE TABLE "Sities" (
                    Id SERIAL PRIMARY KEY,
                    name TEXT,
                    country TEXT,
                    weather_id_api int);

CREATE TABLE "Forecasts" (
                    Id SERIAL PRIMARY KEY,
                    city_id int,
                    time DATE,
                    temp INT,
                    humidity INT,
                    pressure INT);

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO postgres;
GRANT usage ON SCHEMA public TO postgres;

The execution of each command is checked for errors. These commands are executed without problems.

Further in another file such commands are executed:

SET search_path = schema1, public;
INSERT INTO "Sities" (name, country, weather_id_api)
            SELECT 'Orenburg', "RU", 234234
            WHERE NOT EXISTS (SELECT name FROM "Sities" WHERE name="Orenburg");

The last command causes an error:

panic: pq: Relation "Sities" does not exist

goroutine 1 [running]: main.PostToDatabase(0x11731ee0) D:/Go/src/WeatherSoket/main.go:135 +0x40f main.Update() D:/Go/src/WeatherSoket/main.go:150 +0x52 main.main() D:/Go/src/WeatherSoket/main.go:165 +0xbe exit status 2

Why don't you reference the schema name by name? schema.table — salient
– salient, Commented Sep 30, 2017 at 13:41
@Being Sunny, "extra"? I think, without quotes the PostgreSQL will result in lowercase. — ErvingGoffman
– ErvingGoffman, Commented Sep 30, 2017 at 13:56
Show the code that executes the two separate sets of SQL commands, and also show the code that connects to the database. Also at the top of your first file you create a database called weather, your subsequent commands are executed outside of that new database since the script runs in the database to which you originally connected. Anyways your not giving us the whole picture so we are unable to help. — mkopriva
– mkopriva, Commented Sep 30, 2017 at 15:00
And also from your link (ibb.co/iCLaRG) it's clear that you are either connecting to the wrong database (the image says postgres and who knows what database your Go code is connecting to...) or that your first set of commands, the ones that create the tables, did not work as you inteded, and so you have no tables to work with. — mkopriva
– mkopriva, Commented Sep 30, 2017 at 15:07

Eugene Lisitsky · Accepted Answer · 2017-09-30 14:04:48Z

1

This works - try to check quotes " and apostrophes ':

SET search_path = schema1, public;

INSERT INTO "Sities" (name, country, weather_id_api)
            SELECT 'Orenburg', 'RU', 234234
            WHERE NOT EXISTS (SELECT name FROM "Sities" WHERE name='Orenburg');

http://sqlfiddle.com/#!17/5abd9/4

answered Sep 30, 2017 at 14:04

Eugene Lisitsky

13k6 gold badges42 silver badges63 bronze badges

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

3 Comments

Eugene Lisitsky Over a year ago

SHOW search_path; ?

ErvingGoffman Over a year ago

"$user", public

Eugene Lisitsky Over a year ago

So search_path was not applied. Do you use any connection pooler?

Collectives™ on Stack Overflow

PostgreSQL: relation does not exist error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related