After running CREATE TABLE with psql, the table cannot be found

Question

I have been trying to create a table in PostgreSQL but have been getting the following error when I try to list tables with \dt - Did not find any relations.

I used the following create statement - create table jobs( id serial primary key, title text, link text, company text) inside a database named scrape

If I use \dt *.* then a list of tables appear which seem to built-ins -

List of relations
       Schema       |          Name           | Type  |  Owner   
--------------------+-------------------------+-------+----------
 information_schema | sql_features            | table | postgres
 information_schema | sql_implementation_info | table | postgres
 information_schema | sql_languages           | table | postgres
 information_schema | sql_packages            | table | postgres
 information_schema | sql_parts               | table | postgres
 information_schema | sql_sizing              | table | postgres
 information_schema | sql_sizing_profiles     | table | postgres
 pg_catalog         | pg_aggregate            | table | postgres
 pg_catalog         | pg_am                   | table | postgres
 pg_catalog         | pg_amop                 | table | postgres
 pg_catalog         | pg_amproc               | table | postgres
 pg_catalog         | pg_attrdef              | table | postgres
 pg_catalog         | pg_attribute            | table | postgres
 pg_catalog         | pg_auth_members         | table | postgres
 pg_catalog         | pg_authid               | table | postgres
 pg_catalog         | pg_cast                 | table | postgres
 pg_catalog         | pg_class                | table | postgres
 pg_catalog         | pg_collation            | table | postgres
 pg_catalog         | pg_constraint           | table | postgres
 pg_catalog         | pg_conversion           | table | postgres
 pg_catalog         | pg_database             | table | postgres
 pg_catalog         | pg_db_role_setting      | table | postgres
 pg_catalog         | pg_default_acl          | table | postgres
 pg_catalog         | pg_depend               | table | postgres
 pg_catalog         | pg_description          | table | postgres
 pg_catalog         | pg_enum                 | table | postgres
 pg_catalog         | pg_event_trigger        | table | postgres
 pg_catalog         | pg_extension            | table | postgres
 pg_catalog         | pg_foreign_data_wrapper | table | postgres
 pg_catalog         | pg_foreign_server       | table | postgres
 pg_catalog         | pg_foreign_table        | table | postgres
 pg_catalog         | pg_index                | table | postgres
 pg_catalog         | pg_inherits             | table | postgres
 pg_catalog         | pg_init_privs           | table | postgres
 pg_catalog         | pg_language             | table | postgres
 pg_catalog         | pg_largeobject          | table | postgres
 pg_catalog         | pg_largeobject_metadata | table | postgres
 pg_catalog         | pg_namespace            | table | postgres
 pg_catalog         | pg_opclass              | table | postgres
 pg_catalog         | pg_operator             | table | postgres
 pg_catalog         | pg_opfamily             | table | postgres
 pg_catalog         | pg_partitioned_table    | table | postgres
 pg_catalog         | pg_pltemplate           | table | postgres
 pg_catalog         | pg_policy               | table | postgres
 pg_catalog         | pg_proc                 | table | postgres
 pg_catalog         | pg_publication          | table | postgres
 pg_catalog         | pg_publication_rel      | table | postgres
 pg_catalog         | pg_range                | table | postgres
 pg_catalog         | pg_replication_origin   | table | postgres
 pg_catalog         | pg_rewrite              | table | postgres
 pg_catalog         | pg_seclabel             | table | postgres
 pg_catalog         | pg_sequence             | table | postgres
 pg_catalog         | pg_shdepend             | table | postgres
 pg_catalog         | pg_shdescription        | table | postgres
 pg_catalog         | pg_shseclabel           | table | postgres
 pg_catalog         | pg_statistic            | table | postgres
 pg_catalog         | pg_statistic_ext        | table | postgres
 pg_catalog         | pg_statistic_ext_data   | table | postgres
 pg_catalog         | pg_subscription         | table | postgres
 pg_catalog         | pg_subscription_rel     | table | postgres
 pg_catalog         | pg_tablespace           | table | postgres
 pg_catalog         | pg_transform            | table | postgres
 pg_catalog         | pg_trigger              | table | postgres
 pg_catalog         | pg_ts_config            | table | postgres
 pg_catalog         | pg_ts_config_map        | table | postgres
 pg_catalog         | pg_ts_dict              | table | postgres
 pg_catalog         | pg_ts_parser            | table | postgres
 pg_catalog         | pg_ts_template          | table | postgres
 pg_catalog         | pg_type                 | table | postgres
 pg_catalog         | pg_user_mapping         | table | postgres

I also checked the schemas whether they were public or not -

                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(1 row)

If it helps I am also including the users here -

 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 arif      | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

I also checked the databases -

scrape-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 arif      | arif     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 scrape    | arif     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

Did you see a confirmation that your table was created after you ran `create table`? (`psql` will show `CREATE TABLE` to acknowledge it) Did you end your statement with `;`? — , Aug 18 '20 at 06:07
didn't see a confirmation that table was created. Also didn't end the statement with `;`. When I did end it with `;` this is what I got `scrape-# create table jobs(id serial primary key, title text, link text, company text); ERROR: syntax error at or near "create" LINE 2: create table jobs_list(id serial primary key, title text, li... ^` — Arif, Aug 18 '20 at 06:10

score 0 · Accepted Answer · answered Aug 18 '20 at 06:50

You got confused by the semicolons in psql. This happens to everybody in the beginning.

A statement is sent to the server only when psql has received the semicolon that terminates the statement. Otherwise, all you get is a slightly changed prompt that indicates that psql is waiting for a continuation line.

So if you enter

CREATE TABLE mytab (x integer)

without a semicolon, nothing much happens yet.

Then you realize that you forgot the terminating semicolon, and you enter

CREATE TABLE mytab (x integer);

Now psql will consider both lines a single statement and send it to the server, which results in the error message you see.

When in doubt, you can always press Ctrl+C to clear the state and discard the statement buffer.

thanks a ton, this work. Ctrl + C works for me. – Arif Aug 18 '20 at 07:16 — Arif, Aug 18 '20 at 07:16

After running CREATE TABLE with psql, the table cannot be found

1 Answers1