can not create a table in database with libpq in C

Question

I created a c function that create a table on my postgresql db. here:

#include <stdio.h>
#include <libpq-fe.h>
#include <stdlib.h>

PGconn *connection;
PGresult  *re;

void create_table(){
        re= PQexec(connection, "SELECT to_regclass('fp_stores_data')");
        if(!re){
                PQclear(re);

                re = PQexec(connection ,"CREATE TABLE test(name VARCHAR(3))");

                if(PQresultStatus(re)==PGRES_COMMAND_OK){
                        printf("table created!");
                }else{
                        printf("failed to create table!");
                }
        }else{
                printf("table was created befor!");
        }
        PQclear(re);
}


int main() {
        connection = PQconnectdb("user=username password=123 dbname=projectdb");
        create_table();
        PQfinish(connection);
        return 0;
}

but every time I run the program it print failed to create table! and there will be nothing in database with that table name.

Answer 1

Use PQresultErrorMessage to get the error text for the SQL you executed, eg:

printf("%s\n", PQresultErrorMessage(re));

Or PQerrorMessage to get the error text for any error associated with your connection, eg:

printf("%s\n", PQerrorMessage(connection));

The postgresql documentation of these functions is here: https://www.postgresql.org/docs/current/libpq-exec.html