Auto Increment PostgreSQL [message #4250] |
Tue, 10 June 2014 08:22 ![Go to next message Go to next message](/theme/default/images/down.png) |
kong
Messages: 90 Registered: December 2011
|
Member |
|
|
In Radicore 1.84 with PostgreSQL 9.3, I tested auto increment PK feature using this:
CREATE SEQUENCE test_test_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE test_test_id_seq OWNER TO postgres;
CREATE TABLE test (
test_id integer DEFAULT nextval('test_test_id_seq'::regclass) NOT NULL,
test_desc character varying(255) NOT NULL
);
ALTER TABLE test OWNER TO postgres;
ALTER TABLE ONLY test
ADD CONSTRAINT test_pkey PRIMARY KEY (test_id);
REVOKE ALL ON SEQUENCE test_test_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE test_test_id_seq FROM postgres;
GRANT ALL ON SEQUENCE test_test_id_seq TO postgres;
GRANT ALL ON SEQUENCE test_test_id_seq TO PUBLIC;
REVOKE ALL ON TABLE test FROM PUBLIC;
REVOKE ALL ON TABLE test FROM postgres;
GRANT ALL ON TABLE test TO postgres;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE test TO PUBLIC;
I then imported this new table, imported the columns, after which I examined the test_id column via column->update and noted 2 problems:
1) the default value was set to test_test_id_seq
2) the auto increment property was set to 'no' and the screen does not allow any changes to this property.
The first problem is easy to work around, just delete the content in the default field every time columns are imported or re-imported.
The second problem is more troublesome, as a work around requires you to open the dict.inc file and manually add 'auto_increment' => 'y' on the key field, otherwise system will show 'test_id cannot be blank' error when you try to create a record.
|
|
|
Re: Auto Increment PostgreSQL [message #4251 is a reply to message #4250] |
Wed, 11 June 2014 04:37 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Something has changed in Postgres as in my test application I used data types of either 'serial' or 'bigserial' to indicate an auto_increment column, but when querying the table it returns the value 'integer' or 'bigint' with the default value set to "nextval('....')".
I attach a copy of the updated Postgres driver. After importing the columns you will have to manually clear the old default value, but it will now set 'auto_increment' to TRUE as it should.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|