Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » Auto Increment PostgreSQL
Auto Increment PostgreSQL [message #4250] Tue, 10 June 2014 08:22 Go to next message
kong is currently offline  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 messageGo to next message
AJM is currently offline  AJM
Messages: 2246
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.


Re: Auto Increment PostgreSQL [message #4257 is a reply to message #4251] Fri, 13 June 2014 20:08 Go to previous message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
The updated driver fixed the problems. Thanks.
Previous Topic: Creating a List 2 transaction between multiple databases
Next Topic: How to show tab as an image hyperlink
Goto Forum:
  


Current Time: Sat Jun 12 08:18:17 EDT 2021

Total time taken to generate the page: 0.01960 seconds