PostgreSQL - Queries and Primary Keys

Hi all,

A new user to Ignition, dba, and scripting but learning. I am using PostgreSQL as my database. I have successfully created a simple database editor as described in the Ignition user manual 8.0. This editor works great!

When I add data to the table the “id” column is automatically updated and is an index.

Now I have created a new table and cant get the “id” column to update automatically.

I just get a null value in the table.

I am testing it in the Named Query editor.

image

image

I had the id column as the primary key and could not get the insert statement to work without explicitly defining every column.

What am I missing? How do I make the id column automatic?

Thanks for the help,

Steven

When you create the table, declare the id column as type serial instead of any integer type… that triggers PostgreSQL’s automatic sequence creation and attachment as the default value for that column.

@pturmel,

That works. However, I have a database that works where the id is INTEGER. I would like to know why it works and this one didn’t.

Either way, that works and gets me back on track. I will set all indexes to serial in the future to avoid any issues.

Thanks for the help!!

Steven

But that’s what serial does. It creates an integer column and hooks up a SEQUENCE to supply the auto-incrementing values.

You can manually create a sequence and attach it to an existing integer column.

2 Likes