I am attempting to get the primary key value for a newly inserted row using the returning into SQL syntax.
My table is defined as follows:
Code: Select all
-- Table: public.table_alias
-- DROP TABLE public.table_alias;
CREATE TABLE public.table_alias
(
table_alias_id integer NOT NULL DEFAULT nextval('table_alias_table_alias_id_seq'::regclass),
table_name character varying(30) NOT NULL,
human_readable_name character varying(100) NOT NULL,
table_alias character varying(10) NOT NULL,
CONSTRAINT pk_table_alias PRIMARY KEY (table_alias_id)
USING INDEX TABLESPACE indx,
CONSTRAINT uk_tba_table_alias UNIQUE (table_alias)
USING INDEX TABLESPACE indx,
CONSTRAINT uk_tba_table_name UNIQUE (table_name)
USING INDEX TABLESPACE indx,
CONSTRAINT chk_tba_table_alias CHECK (lower(btrim(table_alias::text)) = table_alias::text AND btrim(table_alias::text) IS NOT NULL),
CONSTRAINT chk_tba_table_name CHECK (upper(btrim(table_name::text)) = table_name::text AND btrim(table_name::text) IS NOT NULL)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.table_alias
OWNER TO postgres;
-- Index: public.i_tba_table_alias
-- DROP INDEX public.i_tba_table_alias;
CREATE INDEX i_tba_table_alias
ON public.table_alias
USING btree
(table_alias COLLATE pg_catalog."default")
TABLESPACE indx;
-- Index: public.i_tba_table_name
-- DROP INDEX public.i_tba_table_name;
CREATE INDEX i_tba_table_name
ON public.table_alias
USING btree
(table_name COLLATE pg_catalog."default")
TABLESPACE indx;
Code: Select all
private static void InsertTableAlias(PgSqlConnection connection)
{
const string TABLE_ALIAS_ID = "TABLE_ALIAS_ID";
var command = new PgSqlCommand(string.Format(@"
insert into table_alias (table_name, human_readable_name, table_alias)
values ('table_alias', 'Table Alias', 'tba')
returning table_alias_id
into :{0}", TABLE_ALIAS_ID), connection);
var parameter = command.Parameters.Add(TABLE_ALIAS_ID, PgSqlType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
var count = command.ExecuteNonQuery();
var command2 = new PgSqlCommand("select * from table_alias");
var reader = command2.ExecuteReader();
var table = new PgSqlDataTable();
table.Load(reader);
}
I had initially tried setting the parameter direction to "Output", but received the same error.
Please advise.