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

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!

peng
On 17 May 2010 15:31, sunpeng <bluevaley@gmail.com> 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!

peng

What is it you're testing?

You can insert many rows into a table by doing something like the following:

CREATE TABLE test
(
test_id serial,
test_num int
test_value int
)

INSERT INTO test (test_num, test_value)
SELECT s.a, ceil(random()*100) FROM generate_series(1,1000) as s(a);

That would just put 1,000 entries into the table, the first column would get its value from its sequence, the second from the series and the third would be random.

Regards

Thom
On 17 May 2010 15:53, sunpeng <bluevaley@gmail.com> wrote:
Thanks for your quickly reply.
Maybe i haven't expressed clearly.My purpose is in the postgresql source codes which function invoke should i use to create table and insert tuples.
for example are there any function just like _createTable(char *tableName,int firstColumn,int secondColumn) ?


Please use "reply to all" so everyone can see your responses.

I'm not aware of anything that will do what you're asking.  You can use client interfaces (http://www.postgresql.org/docs/8.4/static/client-interfaces.html) but as for libraries with variadic functions, I wouldn't know.

Regards

Thom

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

From
Guy Rouillier
Date:
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

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.

2010/5/17 Guy Rouillier <guyr-ml1@burntmail.com>
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

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

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

From
John R Pierce
Date:
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.


creating a table based on a table in stored in another database

From
Malm Paul
Date:
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


Re: creating a table based on a table in stored in another database

From
"A. Kretschmer"
Date:
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

Re: creating a table based on a table in stored in another database

From
Malm Paul
Date:
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

Re: creating a table based on a table in stored in another database

From
Scott Mead
Date:


On Tue, May 18, 2010 at 4:45 AM, Malm Paul <paul.malm@saabgroup.com> wrote:
 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


Do you:

 A) Install the dblink contrib module?
 B) Run the dblink SQL file?
 

--Scott

/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 the same 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

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

metadata on a table

From
Malm Paul
Date:
Hi,
I'm trying to read the metadata from table to create the same sort of table in another database, using java.
 
This is how I do it today

quwstion = "SELECT  * FROM table"
stmnt
= dbConnection.prepareStatement(question);

rs = stmnt.executeQuery();

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);

The first object is the primary key with the name "fid",
columnTypeName = int4
 
I can see in the original table that fid has the type serial, which is an int4 (pgAdmin).
But when I create the table in the other database I would like to create it as serial not just int4, how can I know that it is a serial? 
 
Kind regards,
Paul

Re: metadata on a table

From
Malm Paul
Date:
Ahhh.

boolean isAutoIncerment = rsMetaData.isAutoIncrement(1);

if int4 and autoIncremented = serial

Sorry!!



From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Malm Paul
Sent: den 19 maj 2010 08:44
To: pgsql-general@postgresql.org
Subject: [GENERAL] metadata on a table

Hi,
I'm trying to read the metadata from table to create the same sort of table in another database, using java.
 
This is how I do it today

quwstion = "SELECT  * FROM table"
stmnt
= dbConnection.prepareStatement(question);

rs = stmnt.executeQuery();

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);

The first object is the primary key with the name "fid",
columnTypeName = int4
 
I can see in the original table that fid has the type serial, which is an int4 (pgAdmin).
But when I create the table in the other database I would like to create it as serial not just int4, how can I know that it is a serial? 
 
Kind regards,
Paul