Re: Semi-unable to add new records to table--primary key needed?

[Ron <ronljohnsonjr@xxxxxxxxx>]

On 12/20/19 8:19 PM, Boylan, Ross wrote:

I have a table that seems to act for some purposes as if I can't add new records to it.  I would like to understand why that is and fix it.

The initial problem was that an MS-Access application using an ODBC driver (driver and database 64 bit PG 12.0) failed at 
    DoCmd.GoToRecord , , acNewRec
which is basically saying to create a new record in the table.  The error was something like unable to create record.  Unfortunately DoCmd is a black box; I can only speculate what it is doing.

If I open the table in Access's default grid view, the controls to add records are likewise greyed out.
And if I open the table in pgAdmin, it likewise will not let me create a new record.  So the problem is not Access-specific.

Might it be a privilege problem?

With other, similar tables, I can add new records.

And even with tblaliquot, I can add new records with sql; that's how I populated the table.  Perhaps the problem is that I specified values for fields that are serial, and the counter still seems to be at 1.

The table also has lots of records relative to most other ones.  It has about 72,000 records.

The only obvious difference between the tables is that the others had primary keys, and this one doesn't.  Could that explain what was going on?

The lack of PK should make insertions more forgiving.  (It really should have a PK, but that's irrelevant to this problem.)

I had to abandon plans to rollout a conversion to Postgres because of this problem, and so I'd like to solve it so we can do the switch relatively soon.  

Here's the SQL on the table from pgAdmin:
CREATE TABLE public.tblaliquot
(
    aliquotid integer NOT NULL DEFAULT nextval('tblaliquot_aliquotid_seq'::regclass),
    preparationid bigint,
    datealiquotted date,
    rnaaliquottype bigint,
    sequencer character varying(4) COLLATE pg_catalog."default",
    aliquotlabel character varying(40) COLLATE pg_catalog."default",
    aliquotbarcode character varying(255) COLLATE pg_catalog."default",
    rnaaliquotconcentration double precision,
    originalvolume double precision,
    numberdefrosts integer,
    storagetype bigint,
    locationfreezer bigint,
    locationrow character varying(10) COLLATE pg_catalog."default",
    locationrack character varying(10) COLLATE pg_catalog."default",
    locationbox character varying(10) COLLATE pg_catalog."default",
    locationplate character varying(255) COLLATE pg_catalog."default",
    locationspace character varying(10) COLLATE pg_catalog."default",
    locationother character varying(60) COLLATE pg_catalog."default",
    aliquottedby bigint,
    comments text COLLATE pg_catalog."default",
    creationdate date,
    createdby bigint
)

The original code that created the table used AliquotID serial for the first field.

The sequence mentioned in the first field of the definition shows Current Value of 1 in the properties tab in pgAdmin, despite the large number of records.  But the sequences associated with other tables also are very low, like 3 or 4.  3 or 4 might be the number of records added after the initial creation.

This worked from psql:
INSERT INTO tblAliquot VALUES (55338, 6772, '2012-10-05 00:00:00', 6, E'A', NULL, NULL, 24.3, 
   33, 0, 1, NULL, NULL, E'1', E'A', NULL, 
   E'A1', NULL, 23, NULL, '2012-10-18 00:00:00', 55 );

--
Angular momentum makes the world go 'round.