Published 4/30/2007 1:45:04 AM  in  Postgresql

Lets look at how inserting data into Postgresql works. We will use a parameterized insert statement - cause building up the sql string is not recommended.

Using 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();

lets write the C# 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) ", 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;

            command.ExecuteNonQuery();

            oConn.Close();

        }

Pretty much straightforward. Default values can be left out.