Problem Inserting Postgres

[Das Goravani]

Thank you both Doug and Jim.

My problem turned out to be that my database table was made wrong. 

If you want Omnis to make a Primary Key SERIAL when you use $createnames() or drag and drop the schema onto the session, you need to make that primary key field a "Sequence" type in the schema class. 

I had it on 32 bit integer as I thought it wanted that in Postgres

I now realize the schema only directs a translation to postgres datatypes, so hence they turned sequence into SERIAL

I personally use a window class to create tables. On it I have fields to input the schema and the table name, and then use $creratenames() to get all the fields off the schema in the right format which I put into a big text field with the rest of the statements text, then I insert PRIMARY KEY as another constraint on that column, then I click a button to issue the command to create the table. 

Omnis doesn’t put in PRIMARY KEY for the primary key field, I don’t know why, I put it in, for it is a primary key field

I am using a mix of SQL and Omnis’s Table methods like $insert, $select, $update, $delete, $dowork, $doinserts… etc… 

I am using $excludefrominsert on both the table method $insert and as well because I sometimes use $insertnames() in an $execdirect SQL statement. 

I am excluding the primary key column from inserts so that postgres will set it to NEXTVAL automatically. ($excludefrominsert)

I am using a RETURNING clause on INSERTS both when done as table methods and when done in SQL

I get back the primary key ID column via the returning clause. I then put that into the row so that now I can go into UPDATE mode on that record successfully and also so that I can set that value in child records that are saved after the main record is saved

Everything is going really well now with this project.

Today I did a JOIN and I was surprised how compact the whole thing is in code in Omnis.
The Sta: commands are neat and efficient, easy to read, spaces the code out in logical chunks
Getting the statement and running it in an $execdirect is quick and easy
Then $fetch the list
Then redefine it if you need to

This JOIN I did is just reading in the line items list for an invoice
The line items have some things in their own line items table but they need joined the product number and description
In the line items table I have product ID, the product primary key
I "Join In" the product number the user enters and the product description

I could have done this with a query class, joining two tables, is easy
But I wanted to do it the SQL way.. so that I learn that way more and more
Because it is highly efficient, puts all your database query stuff in one place in your code
Is less code than processing the same result with Omnis code

Like it would be inefficient if I 

Loaded a list of the columns in the line items table for the selected invoice
Ran down this list and for each line
Find the associated product with a SELECT query
Add a line to another list with just the columns I want from both line items and associated product

That is a bunch of Omnis code doing it the manual way and it is probably slower than letting Postgres do it