Postgres users see different data

Question

I have been using MySQL for quite some time, and I decided to learn Postgres. The transition was not terrible, but today I ran into a problem:

I have an administrator account, postgres and a user specifically for this application, tv . For convenience, I changed some rows in the table under the administrator account. The website will not reflect any changes to the database.

After I blamed the various caching strategies for an hour, I finally ran psql as the tv user and noticed that none of the lines in it reflected the changes made during postgres login. Based on the background of MySQL, this behavior was completely incomprehensible to me.

In short: is this a feature, or am I misinterpreting something? And is there a way to make the database different?

Thanks for any help.

---

Update: The following are examples of commands:

  [12:23:04] blake $ sudo -u postgres psql -d teevee
 psql (9.1.3)
 Type "help" for help.

 teevee = # SELECT COUNT (*) FROM episode;
  count
 -------
      1
 (1 row)

  [12:23:25] blake $ psql -U tv -d teevee -h localhost -W
 Password for user tv:
 psql (9.1.3)
 SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
 Type "help" for help.

 teevee => SELECT COUNT (*) FROM episode;
  count
 -------
    176
 (1 row)

Answer 1

You can list the schemas using \dn or \dn+ under psql , but I would suggest using a tool like pgAdmin for a better visual presentation. You can find out if you have conflicting tables in these potential tv and public schemas using \dt public.* And \dt tv.* .

Using multiple schemas may be useful, but often not required.

The default search path is set to "$user",public , which means that he will first try to use the tables in the schema so named after the user and return to the public schema (the most common).

I would suggest using the public scheme as a whole if you have no good reason. Usually this should be the default value. (I'm not sure why you had a different scheme.)

You can change the table schema using ALTER TABLE xxxxxx SET SCHEMA yyyyyyy , although you may need to discard another one with a conflicting name first. You, of course, want to copy the data first.

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

If you do not have anything useful in your public schema at the moment, you can completely abandon the schema ( DROP SCHEMA public ) and rename another one ( ALTER SCHEMA tv RENAME to public ). Depending on what has already been provided, you may also need something like this:

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