Postgresql prepared statement with automatic columns

Question

Trying to use prepared statements with python's psypcop2 for inserts needs to be setup manually. But using prepared statments with automatic columns such as serial id or timestamps causes errors.

Running sql statements directly via psql:

create table ps_test(
   id serial primary key,
   data text );

prepare pstmt (text) as insert into ps_test values( $1 );

execute pstmt( 'abc' );
execute pstmt( 'def' );

deallocate pstmt;
select * from ps_test;

gives "ERROR: column "id" is of type integer but expression is of type text".

Rewriting the table so that the automatic column is defined last:

drop table ps_test;
create table ps_test (
     data text,
     id serial primary key
);

prepare pstmt (text) as insert into ps_test values( $1 );

execute pstmt( 'abc' );
execute pstmt( 'def' );

deallocate pstmt;
select * from ps_test;

Works.

 data | id 
------+----
 abc  |  1
 def  |  2
(2 rows)

Is there a better way ?

unutbu · Accepted Answer · 2019-02-25 00:53:12Z

1

With standard SQL you would use

INSERT INTO ps_test (data) VALUES ( $1 )

to insert $1 into the data column. Notice the INSERT statement syntax allows for the column_name, data, to be specified in parentheses after the table_name, ps_test.

Therefore, your prepared statement would be

PREPARE pstmt (text) AS INSERT INTO ps_test (data) VALUES ( $1 );

answered Feb 25, 2019 at 0:53

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql prepared statement with automatic columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related