Thread: which function should i invoke to create a table and insert tuples?

On 5/17/2010 10:31 AM, sunpeng wrote:
> hi,when i do experiment on postgresql 8.4,i need to create a table and
> insert some tuples,which function should i invoke?
> for example,i want to create a table with "create table test (uid
> int,catcode int)" and insert tuples with "insert into test values(1,1)".
> thanks millions!

What do you mean by function?  Are you trying to do this from PgAdmin,
from a stored proc or from some flavor of source code?  If either of the
latter two, which language?

--
Guy Rouillier

sunpeng wrote:
> it's in source codes,actually i'm writting codes in postgresql source
> codes,just to verify some of my ideas. C language is used.

you would pass the SQL statements to do what you want to the various
libpq library functions...


something like...

    PGconn *conn;
    PGresult *res;

    conn = PQconnectdb("dbname=mydatabase");
    if (PQstatus(conn) != CONNECTION_OK) {
    fprintf(stderr, "Connection to database failed: %s",
    PQerrorMessage(conn));
    exit_nicely(conn);
    }

    res = PQexec(conn, "create table test (id serial, num int, value
    text);");
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
    fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
    PQclear(res);
    exit_nicely(conn);
    }
        ...



most folks would probably put the PQexec() and status tests into a
function to simplify things.

Hi list,
in a database I have different kind of tables. I would like to take the meta data from one of those tables and create
thesame type of table (but empty) in another database. 
Can anyone, please, tell me how to do this?

Kind regards,
Paul

In response to Malm Paul :
>  Hi list,
> in a database I have different kind of tables. I would like to take the meta data from one of those tables and create
thesame type of table (but empty) in another database. 
> Can anyone, please, tell me how to do this?

Create a schema-only dump and restore it into the other database.


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

I can add that I would like to do this in my java application.
E.i. just create an empty table in database1 which has the same column names and types as a table stored in database2.

I tried this for a start:
stmnt = dbConnection.prepareStatement("select dblink_connect('myconn', 'dbname=gemaps')");
resultset = stmnt.executeQuery();

but got ERROR: function dblink_connect(unknown, unknown) does not exist

/P
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of A. Kretschmer
Sent: den 18 maj 2010 10:16
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] creating a table based on a table in stored in another database

In response to Malm Paul :
>  Hi list,
> in a database I have different kind of tables. I would like to take the meta data from one of those tables and create
thesame type of table (but empty) in another database. 
> Can anyone, please, tell me how to do this?

Create a schema-only dump and restore it into the other database.


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
System.out.println("resultSet MetaData column Count=" + numberOfColumns);
int precision = rsMetaData.getPrecision(1);
String columnTypeName = rsMetaData.getColumnTypeName(1);
String name = rsMetaData.getColumnName(1);
String classname = rsMetaData.getColumnClassName(1);

ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
System.out.println("resultSet MetaData column Count=" + numberOfColumns);
int precision = rsMetaData.getPrecision(1);
String columnTypeName = rsMetaData.getColumnTypeName(1);
String name = rsMetaData.getColumnName(1);
String classname = rsMetaData.getColumnClassName(1);