{"id":2201,"date":"2012-02-16T18:16:38","date_gmt":"2012-02-16T18:16:38","guid":{"rendered":"https:\/\/www.acugis.com\/blogs\/?p=2201"},"modified":"2015-05-11T18:19:39","modified_gmt":"2015-05-11T18:19:39","slug":"auto-increment-in-phppgadmin","status":"publish","type":"post","link":"https:\/\/acugis.com\/blogs\/auto-increment-in-phppgadmin\/","title":{"rendered":"Auto Increment in PhpPgAdmin"},"content":{"rendered":"

Unlike PhpMySQL, PhpPgAdmin does not feature a nice drop-down to auto increment on a column.<\/p>\n

This post will demonstrate how to auto increment on a column in PostgreSQL using either the PhpPgAdmin SQL Editor as well as how to make use of PhpPgAdmin GUI to do some of the work for us.<\/p>\n

The steps for using the PhpPgAdmin SQL Editor can be applied to using the command line (psql) as well.<\/p>\n

In our example we will create a table, Managers.<\/p>\n

Our table will have three columns: mgr_id, mgr_name, and mgr_email.<\/p>\n

For each insertion of a new Manager entry, we want to auto increment our primary key, mgr_id, by 1.<\/p>\n

Let’s start by using the command line. You can use below for the PhpPgAdmin SQL Editor as well.<\/p>\n

 <\/p>\n

Step 1: Create a Sequence<\/strong><\/span><\/div>\n
testdb=> CREATE SEQUENCE mgr_id_seq;\r\nCREATE SEQUENCE\r\n<\/pre>\n
Step 2. Create Table and Set the Column Default<\/strong><\/span><\/div>\n
We now create the Manager table.<\/p>\n
For mgr_id, set as PRIMARY KEY and set the DEFAULT to NEXTVAL(‘mgr_id_seq’) as shown below.<\/p>\n
This will increment our sequence by 1 each time a new Manager entry is inserted.<\/p>\n
testdb=> CREATE TABLE managers(\r\ntestdb(> mgr_id INTEGER PRIMARY KEY DEFAULT NEXTVAL('mgr_id_seq'),\r\ntestdb(> mgr_name VARCHAR(50),\r\ntestdb(> mgr_email VARCHAR(50)\r\ntestdb(> );\r\n\r\nNOTICE:  CREATE TABLE \/ PRIMARY KEY will create implicit index \"managers_pkey\" for table \"managers\"\r\nCREATE TABLE\r\ntestdb=>\r\n<\/pre>\n
Step 3. Insert Data.<\/strong><\/span><\/div>\n
testdb=> INSERT INTO managers (mgr_name, mgr_email) VALUES('bob smith', 'bob@smith.com');\r\nINSERT 0 1\r\ntestdb=> INSERT INTO managers (mgr_name, mgr_email) VALUES('tom jones', 'tom@jones.com');\r\nINSERT 0 1\r\n<\/pre>\n
Step 4. Select on Table to View Sequence.<\/strong><\/span><\/div>\n
testdb=> select * from managers;\r\n mgr_id | mgr_name  |   mgr_email\r\n--------+-----------+---------------\r\n      1 | bob smith | bob@smith.com\r\n      2 | tom jones | tom@jones.com\r\n(2 rows)\r\n\r\ntestdb=>\r\n\r\n<\/pre>\n
As we an see from above, the increment begins at 1 and increments by 1 by default.<\/p>\n
Now, let’s take a look at how we can do this using some of the PhpPgAdmin GUI tools.<\/p>\n
\n
Using the PhpPgAdmin GUI Tools<\/strong><\/p>\n<\/div>\n
\n
 <\/p>\n
Step 1: Create Sequence in PhpPgAdmin<\/strong><\/p>\n<\/div>\n
Create your sequence using by selecting the Sequence node in PhpPgAdmin and then click “Create Sequence”<\/p>\n
Enter the sequence name as shown below.<\/p>\n
\"\"<\/a><\/div>\n
With your sequence created, you can now set the DEFAULT value for the mgr_id column to nextval(‘mgr_id_seq’) in the table creation GUI as shown below:<\/p>\n
\"\"<\/a><\/div>\n
 <\/p>\n
More on Sequences in PostgreSQL:<\/p>\n
http:\/\/www.postgresql.org\/docs\/9.0\/static\/sql-createsequence.html<\/a><\/p>\n
http:\/\/www.postgresql.org\/docs\/9.1\/static\/functions-sequence.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Unlike PhpMySQL, PhpPgAdmin does not feature a nice drop-down to auto increment on a column. This post will demonstrate how to auto increment on a column in PostgreSQL using either the PhpPgAdmin SQL Editor as well as how to make use of PhpPgAdmin GUI to do some of the work for us. The steps for … <\/p>\n
Continue reading “Auto Increment in PhpPgAdmin”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[31,36,35],"tags":[],"class_list":["post-2201","post","type-post","status-publish","format-standard","hentry","category-postgis","category-postgresql","category-tutorials"],"_links":{"self":[{"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/posts\/2201","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/comments?post=2201"}],"version-history":[{"count":1,"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/posts\/2201\/revisions"}],"predecessor-version":[{"id":2202,"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/posts\/2201\/revisions\/2202"}],"wp:attachment":[{"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/media?parent=2201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/categories?post=2201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/acugis.com\/blogs\/wp-json\/wp\/v2\/tags?post=2201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}