From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Semi-unable to add new records to table--primary key needed? |
Date: | 2019-12-21 05:02:42 |
Message-ID: | be2e9263-bf72-aa3d-efb9-58c1fa63bffd@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 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.
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2019-12-21 06:44:41 | Re: Semi-unable to add new records to table--primary key needed? |
Previous Message | Tom Lane | 2019-12-21 03:06:18 | Re: Semi-unable to add new records to table--primary key needed? |