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

Ron <ronljohnsonjr@xxxxxxxxx> · Sat, 21 Dec 2019 00:44:41 -0600

On 12/20/19 11:02 PM, Adrian Klaver wrote:
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.

Shows how much I use pgAdmin and Access...

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.

--
Angular momentum makes the world go 'round.