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

Поиск
Список
Период
Сортировка
От Boylan, Ross
Тема Semi-unable to add new records to table--primary key needed?
Дата
Msg-id DM6PR05MB5740B3492AD0AEE1EF5600DC872C0@DM6PR05MB5740.namprd05.prod.outlook.com
обсуждение исходный текст
Ответы Re: Semi-unable to add new records to table--primary key needed?  (Ron <ronljohnsonjr@gmail.com>)
Re: Semi-unable to add new records to table--primary key needed?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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
thatis 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.
UnfortunatelyDoCmd 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.

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
thatI 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
explainwhat was going on? 

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
cando 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,
despitethe large number of records.  But the sequences associated with other tables also are very low, like 3 or 4.  3
or4 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 );



В списке pgsql-general по дате отправления:

Предыдущее
От: tony@exquisiteimages.com
Дата:
Сообщение: Re: Upgrading from PostgreSQL 9.3 to 11.6
Следующее
От: Ron
Дата:
Сообщение: Re: Semi-unable to add new records to table--primary key needed?