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

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

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>

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

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

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

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

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