My postgresql tables in indigo_database are getting pretty big so I'm working on a purge that will only leave the last record for each device. Up to now I've simply replaced them occasionally but thought it was time to get a little smoother.

Is the ID field unique for each table?
I.e. can I use the ID to delete with and therefor not have to match on several fields?

And an aside question, is there a postgreSQL equivalent for MySQL's Auto Increment that has eluded me thus far? Trying to go all postgreSQL for my Alert Prog.

hwitten wrote:Is the ID field unique for each table?
I.e. can I use the ID to delete with and therefor not have to match on several fields?

OK, so after a quick word with Matt (I'm sorely lacking much understanding of the SQL logging stuff), I believe the answer is yes, the ID field is unique for each table, but not across tables. I don't believe that you can use this field to match much of anything since it seems to be auto generated for each new row. The tables are created with the following SQL:

Code: Select all

CREATE TABLE device_history_basic (
  id    #AUTO_INCR_KEY,
  ts    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dev_name  VARCHAR(255) NOT NULL,
  dev_state  BOOL,
  dev_value  SMALLINT
);

hwitten wrote:And an aside question, is there a postgreSQL equivalent for MySQL's Auto Increment that has eluded me thus far? Trying to go all postgreSQL for my Alert Prog.

And, happily, I believe the answer to this question is also in the code above - again, I don't know for sure, but it sure seems like it.

Thanks for the quick reply.

No problem re the "unique across tables". Only needed to be sure that an id will only exist once in a table so that I can use it to find the record for deletion after parsing for deletion.

I get an error on the #AUTO_INCR_KEY, but that's ok. Gives me a start

Thanks again.

Try this:

Code: Select all

create table mytesttable (myid serial primary key, mytitle varchar(50));

I've just started with Postgres, and what it looks like it does is save an internal variable called myid that auto updates behind the scenes. I think it breaks if you change the 'myid' to another name, and I don't know how to change the 'myid' internal variable to match.

hwitten wrote:I get an error on the #AUTO_INCR_KEY, but that's ok.

FYI, I dug a bit deeper and the Indigo SQL plug-in actually substitutes out #AUTO_INCR_KEY for "SERIAL PRIMARY KEY" before it does the SQL insert.

Thank you ricks and Matt.

Got it working

Thanks again guys, and the board (and Indigo) in general.

That little "create" tidbit allowed me to implement/use the auto increment in my_house table. I can now switch that from mySQL to postgreSQL for good.

Now I only have to change my weather data away from mySQL and it'll be postgreSQL all the way.

This is another example that the value of this board is absolutely "priceless". While Matt and Jay give us an excellent product, it is the "Community" that adds the icing to our home automation cake.

Without Indigo and this Community I'd still be in the X10 dungeons with little more than floodlights as my home automation

THANK YOU ALL