Postgres users see different data

Question

I've been using MySQL for quite some time, and I've decided to learn Postgres. The transition hasn't been terrible, but I ran into a snag today: I have the admin account, `postgres`, and a user specifically for this application, `tv`. For the sake of convenience, I was modifying some rows in a table under the admin account. The website would not reflect any of the changes I was making to the database. After blaming my various caching strategies for about an hour I finally ran `psql` as the `tv` user and noticed that none of the rows in there reflected the alterations made while logged in as `postgres`. Coming from a MySQL background, this behavior was completely baffling to me. Long story short: is this a feature, or did I misconfigure something somewhere? And is there any way to make the database not act like this? Thanks for any help. Update: Here are a sampling of commands: ``` [12:23:04] blake$ sudo -u postgres psql -d teevee psql (9.1.3) Type "help" for help. teevee=# SELECT COUN…

Accepted Answer

You can list schemas using `\dn` or `\dn+` under `psql`, but I would suggest using a tool like pgAdmin to have a better visual representation. You can find whether you have conflicting tables in those potential `tv` and `public` schemas using `\dt public.*` and `\dt tv.*`.

Using multiple schemas can be useful, but is often not required.

The default search path tends to be `"$user",public`, which means that it will first try to use the tables in the schema named after the user and fall back onto the `public` schema (the most common).

I would suggest using the `public` schema in general, unless you have a good reason not to. This should generally be the default. (I'm not sure why you had another schema.)

You can change a table schema using `ALTER TABLE xxxxxx SET SCHEMA yyyyyyy`, although you may need to drop the other one with a conflicting name beforehand. You would certainly want to copy the data out first.

Something like `INSERT INTO public.episode SELECT * FROM tv.episode` should work in your case, even better with the column names specified. Whether this works may depend on other constraints.

If you have nothing useful in your `public` schema at the moment, you might as well drop the schema entirely (`DROP SCHEMA public`) and rename the other one (`ALTER SCHEMA tv RENAME to public`). Depending on what's already granted, you may then also need something like this:

```
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
```

Postgres users see different data

Solution

Problem