CODEWITHSUNDEEP
sqldatabasepostgresql
Cezariusz
Cezariusz

Reputation: 533

How to use default column value for the rowtype variable in PostgreSQL?

I have a table and a procedure like this:

CREATE TABLE test_table (
id SERIAL PRIMARY KEY,
info TEXT);

create or replace function test() returns void as
$$
declare
  v_row test_table%ROWTYPE;
begin
  v_row.info := 'test';
  insert into test_table values (v_row.*);
end;
$$ language plpgsql;

select test();

ERROR:  null value in column "id" violates not-null constraint

How to use default value for the v_row.id field? I know I could write

insert into test_table (info) values (v_row.info);

But in my real case I have a lot of such tables with many columns and I really want to avoid enumerating all the columns in the insert statement.

Upvotes: 0

Views: 2425

Answers (2)

Jan Marek
Jan Marek

Reputation: 11170

You can check, if Postgresql have a SEQUENCE for this column and then, if this column have a DEFAULT value set. In psql try:

\d+ test_table

You have to see somethink like this:

id    | integer                     | default nextval('test_table_id_seq'::regclass) |

If there is not a default nextval('somethink'), then you have to check, if there is sequnence for this column:

\ds+

You have to see somethink like this:

 public | test_table_id_seq | sequence

If you will not have a sequence, you have a CREATE it:

CREATE SEQUENCE test_table_id_seq;

And if you will have not a `default nextval('somethink'), you have use a ALTER TABLE:

ALTER TABLE test_table ALTER COLUMN id SET DEFAULT nextval('test_table_id_seq');

You can find about it some informations here: http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL

Perhaps you can understand it, although English is not my native language...

Upvotes: 0

filiprem
filiprem

Reputation: 7124

By writing insert into test_table values (v_row.*); you actually force postgres to insert NULL value into the id column.

You will need to run such code - either in application 

v_row.id := nextval( 'mysequence' );

.. or in trigger 

IF NEW.id IS NULL THEN
    NEW.id := nextval( 'mysequence' );
END IF;

Upvotes: 1

Related Questions