After running script, column names not appearing in pgadmin

Question

Sometimes when I run my Python script which calls shp2pgsqlto upload a new table to the database, when I view this table in pgadmin, it appears with blank column names:

This one has column names Usually when I run the script again it fixes the problem, and pgadmin displays a message about database vacuuming. Honestly the problem is my boss because he takes this as a sign there is something wrong with my code and we can't move forward until he sees the names in pgadmin (by chance when I demonstrated the script it was the 1/10 time that it messed up without the column names).

In postgres is it even possible to have a table without column names?

Here is the vacuum message

Here is the output from psql's \d (assume XYZ is the name of the project and the name of the db)

xyz => \d asmithe.intersect
                                     Table "asmithe.intersect"
   Column   |             Type             |                         Modifiers

------------+------------------------------+------------------------------------
------------------------
 gid        | integer                      | not null default nextval('intersect
ion_gid_seq'::regclass)
 fid_xyz_09 | integer                      |
 juris_id   | character varying(2)         |
 xyz_plot   | numeric                      |
 poly_id    | character varying(20)        |
 layer      | character varying(2)         |
 area       | numeric                      |
 perimeter  | numeric                      |
 lid_dist   | integer                      |
 comm       | character varying(252)       |
 cdate      | character varying(30)        |
 sdate      | character varying(30)        |
 edate      | character varying(30)        |
 afsdate    | character varying(30)        |
 afedate    | character varying(30)        |
 capdate    | character varying(30)        |
 salvage    | double precision             |
 pb_harv    | double precision             |
 utotarea   | numeric                      |
 nbacvers   | character varying(24)        |
 totarea    | numeric                      |
 areamoda   | numeric                      |
 areamodb   | numeric                      |
 areamodt   | double precision             |
 areamodv   | numeric                      |
 area_intr  | numeric                      |
 dist_perct | numeric                      |
 id         | double precision             |
 floodid    | double precision             |
 basr       | double precision             |
 floodmaps  | double precision             |
 floodmapm  | double precision             |
 floodcaus  | double precision             |
 burnclas   | double precision             |
 geom       | geometry(MultiPolygon,13862) |
Indexes:
    "intersect_pkey" PRIMARY KEY, btree (gid)

Quitting and restarting usually does fix it.

Answer 1

In postgres is it even possible to have a table without column names?

It is possible to create a table with zero columns:

test=> CREATE TABLE zerocolumns();
CREATE TABLE
test=> \d zerocolumns 
Table "public.zerocolumns"
 Column | Type | Modifiers 
--------+------+-----------

but not a zero-width column name:

test=> CREATE TABLE zerowidthcol("" integer);
ERROR:  zero-length delimited identifier at or near """"
LINE 1: CREATE TABLE zerowidthcol("" integer);

                                  ^

though a column name composed only of a space is permissible:

test=> CREATE TABLE spacecol(" " integer);
CREATE TABLE
test=> \d spacecol 
   Table "public.spacecol"
 Column |  Type   | Modifiers 
--------+---------+-----------
        | integer | 

Please show the output from psql's \d command if this happens. With only (heavily edited) screenshots I can't tell you anything more useful.

If I had to guess I'd say it's probably a drawing bug in PgAdmin.

---

Update: The VACUUM message is normal after big changes to a table. Read the message, it explains what is going on. There is no problem there.

There's nothing wrong with the psql output, and since quitting and restarting PgAdmin fixes it, I'm pretty confident you've hit a PgAdmin bug related to drawing or catalog access. If it happens on the current PgAdmin version and you can reproduce it with a script you can share with the public, please post a report on the pgadmin-support mailing list.

Answer 2

The same happened to me in pgAdmin 1.18.1 when running the DDL (i.e. SQL script that drops and recreates all tables). After restarting pgAdmin or refreshing the database it is working again (just refreshing the table is not sufficient). It seems that pgAdmin simply does not auto-refresh table metadata after the tables are replaced.