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

On 12/20/19 6:28 PM, Ron wrote:

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

Actually it is the problem:

https://www.pgadmin.org/docs/pgadmin4/4.16/editgrid.html

"If the table definition does not include an OID or a primary key, the 
displayed data is read only. "

As I recall Access has the same restriction.

> 
>> I had to abandon plans to rollout a conversion to Postgres because of this problem, and so I'd like to solve it so
wecan 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
or4.  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.


-- 
Adrian Klaver
adrian.klaver@aklaver.com