Consider this table:
CREATE TABLE city
(
city_id serial NOT NULL,
city character varying(50) NOT NULL,
country_id smallint NOT NULL,
last_update timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT city_pkey PRIMARY KEY (city_id),
CONSTRAINT city_country_id_fkey FOREIGN KEY (country_id)
REFERENCES country (country_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE city OWNER TO postgres;
-- Index: idx_fk_country_id
-- DROP INDEX idx_fk_country_id;
CREATE INDEX idx_fk_country_id
ON city
USING btree
(country_id);
-- Trigger: last_updated on city
-- DROP TRIGGER last_updated ON city;
CREATE TRIGGER last_updated
BEFORE UPDATE
ON city
FOR EACH ROW
EXECUTE PROCEDURE last_updated();
A common requirement is to be able to insert a row - and retrieve the automatically value for the autonumber field, in this case city_id.
Against other databases (SQL Server for example) we can execute multiple statements in one call by just separating them woth a semicolon - and to my joy it works the same woth Postgresql. This would mean we should be able to do something like 'insert ... values(...) ; select GETLASTID;)
In SQL Server we can use @@IDENTITY, in MySQL it's called last_inserted_id() - in Postgresql things are a little different, I wouldn't say which way is the best, but to be honest - the Postgresql way is more "objectoriented". Cause we can create objects called SEQUENCES. In fact, when we specified SERIAL ( city_id serial NOT NULL ) Postgresql automatically created such an object. They key to remember is that the object is called table_column_seq - so in our case city_city_id_seq. Anmd we can then use the function currval(sequencename) to rertieve the value we are after.
See the links for currval documentation - in short it's session safe - i.e we will retrieve our connections last value, no matter if other users are calling the same function.
So here's the code:
public void Insert(string city, int country_id)
{
Npgsql.NpgsqlConnection oConn = new Npgsql.NpgsqlConnection("Server=192.168.10.4;Port=5432;Userid=postgres;Password=stefan;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable;Database=pagila");
oConn.Open();
Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand("insert into city(city, country_id ) values(:city, :country_id);select currval('city_city_id_seq') ", oConn);
command.Parameters.Add(new Npgsql.NpgsqlParameter("city", DbType.String, 50));
command.Parameters.Add(new Npgsql.NpgsqlParameter("country_id", DbType.Int16));
command.Parameters[0].Value = city;
command.Parameters[1].Value = country_id;
long lNewId = (long)command.ExecuteScalar();
oConn.Close();
}
