[SQL] insert into help

[Nicholas I]

Hi,

 i have two tables,
---
*table1

id type serial, name varchar;*
*--
table 2

name varchar;*
---

i want to insert the values of table 2 into table 1, with automatic id's.

insert into table1(select * from table2);

is not working, how can i append the data to table 1 with auto incremented
or nextval.

-Nicholas I

Re: [SQL] insert into help

[Guillaume Lelarge]

Le 22/09/2010 09:32, Nicholas I a écrit :
> Hi,
> 
>  i have two tables,
> ---
> *table1
> 
> id type serial, name varchar;*
> *--
> table 2
> 
> name varchar;*
> ---
> 
> i want to insert the values of table 2 into table 1, with automatic id's.
> 
> insert into table1(select * from table2);
> 
> is not working, how can i append the data to table 1 with auto incremented
> or nextval.
> 

INSERT INTO table1 (name) SELECT name FROM table2;


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

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

Re: [SQL] insert into help

[venkat]

HI,


 Please go through
http://www.java2s.com/Tutorial/Oracle/0080__Insert-Update-Delete/CopyingRowsfromOneTabletoAnotherINSERTINTOselect.htm

Thanks
and Regards,

Venkat

On Wed, Sep 22, 2010 at 1:02 PM, Nicholas I wrote:

> Hi,
>
>  i have two tables,
> ---
> *table1
>
> id type serial, name varchar;*
> *--
> table 2
>
> name varchar;*
> ---
>
> i want to insert the values of table 2 into table 1, with automatic id's.
>
> insert into table1(select * from table2);
>
> is not working, how can i append the data to table 1 with auto incremented
> or nextval.
>
> -Nicholas I
>

Re: [SQL] Question about PQexecParams

[Steve]

 Original-Nachricht 
> Datum: Sun, 12 Sep 2010 01:52:04 +0400
> Von: Dmitriy Igrishin 
> An: Steve 
> CC: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] Question about PQexecParams

> Hey Steve,
> 
> 2010/9/11 Steve 
> 
> > Hello list,
> >
> > I would like to call a function from my C application by using libpq and
> > PQexecParams. My problem is that I don't know how to specify that I want
> to
> > send an array to the function.
> >
> > Assume the function is called lookup_data and takes the following
> > parameters: lookup_data(integer,integer,bigint[])
> >
> > I would like to specify the OID with my query. How would I do that?
> Assume
> > I would like to query 3 values for bigint:
> >
> > const char *paramValues[5];
> > Oid paramTypes[5];
> > int paramLengths[5];
> > int paramFormats[5];
> >
> > int32_t ivalue1 = htonl(value1);
> > paramValues[0]  = (char *)&ivalue1;
> > paramTypes[0]   = INT4OID;
> > paramLengths[0] = sizeof(ivalue1);
> > paramFormats[0] = 1;
> >
> > int32_t ivalue2 = htonl(value2);
> > paramValues[1]  = (char *)&ivalue2;
> > paramTypes[1]   = INT4OID;
> > paramLengths[1] = sizeof(ivalue2);
> > paramFormats[1] = 1;
> >
> > etc...
> >
> > How would I tell libpq that the next 3 values are an array of bigint?
> >
> > I tried to use INT8OID and specify the query like below but that did not
> > work:
> > SELECT * FROM lookup_data($1,$2,{$3,$4,$5})
> >
> Incorrect.
> 
> >
> > Probably I have to set the query to be:
> > SELECT * FROM lookup_data($1,$2,{$3})
> >
> Incorrect.
> 
> >
> > Or:
> > SELECT * FROM lookup_data($1,$2,$3)
> >
> Correct.
>
Thanks.


> You may specify a data type by OID (1016 for bigint[],
> please refer to
> http://www.postgresql.org/docs/9.0/static/catalog-pg-type.html
> to obtain information about types) or attach an explicit cast to a
> parameter symbol to force treating it as bigint[] (or any specified type),
> e.g.
> SELECT * FROM lookup_data($1, $2, $3::bigint[])
> 
Thanks.


> > But what would I set for paramTypes? How can I say that the values are
> an
> > array of bigint? I assume that I can set paramValues to be an array and
> > paramLengths to be sizeof one value multiplied by the amount of elements
> in
> > the array.
> >
> Please note, that in this case, you must pass to paramValues[2] a textual
> representation
> of bigint[], e.g. '{1,2,3}'.
> Its not necessary to specify a length of text-format parameters (its
> ignored).
> The length is essential only if you transmit data in a binary format.
> 
Really? I must use a textual representation of the array? Why?
I searched the Internet up and down and as far as I can tell, there is a 
possibility to send the array in binary. I have to add a special header to the 
array and do off course that host to network translation and then I can send 
the array in binary. Unfortunately I can not find enough information about the 
format of the whole header + array. The header looks to be easy to create (just 
3 times 4 bytes for 1) number of dimensions (aka ndims), 2) if the array has 
null elements (aka hassnull), 3) array element oid (aka typeid. In my case 
INT8OID aka 20)) and then followed by the content of the array. And here I have 
a problem. I don't know how that data following the header should look like? I 
think that each value is in a block of 8 bytes (converted from host to 
network). But I am not sure (the examples I have seen are all for int4 and not 
for bigint). I am confused by the two examples I have found so far. One of them 
is dividing those 8 bytes into two 4 bytes blocks and adds so
 mething they call "dims" and "lbound". I have no clue what that is? I think 
the PostgreSQL function "array_recv()" is responsible for the format but I can 
not find any documentation about the format of a binary array representation. 
Maybe you know a place where I can read about how to send an array of int64_t 
to the PostgreSQL backend in binary?

I know that I could go the textual representation path, but I really want to 
send the data in binary. And I don't want/can libpqtypes (which would btw make 
the task ultra easy).

Maybe I can not see the forest because of the trees but I really can not find 
any documentation how to create a correct struct representing an array 
datatype. Can it be that this part is not documented at all?


> 
> >
> > I am somehow lost and don't know how to call the function and pass an
> array
> > to libpq.
> >
> > Can any one help me with this?
> >
> Hope this helps.
> 
Yes. You helped me a bit. But I am still not there where I want/need to be.


> Regards,
> Dmitriy
>
// Steve
-- 
Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief!  
Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail

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

[SQL] HowTo divide streetname from house-nr ?

[Andreas]

 Hi,

how could I divide streetnames from housenumbers ?

I have to deal with input like this:

Parkstreet 42
Parkstr. 42
Casle Avenue 42
Casle Str. 42-47
Casle Str. 54 - 55

probaply even
Casle Str. 42-47 a

Perhaps one could cut ap the 1st numeric char and regard everything left 
of it as the street name and the rest as house number.

OK, this would fail with "42, Parkstreet" but those aren't to frequent.

How would I do this?

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

Re: [SQL] HowTo divide streetname from house-nr ?

[Craig Ringer]

On 23/09/2010 9:25 AM, Andreas wrote:

Hi,

how could I divide streetnames from housenumbers ?


You can't, reliably. There are papers written on the subject of 
addressing, address analysis, addressing in databases, etc.


How would you handle the address:

  Person's Name
  RD3 Clemo Rd
  Whangarei

? "RD3" is "Rural Delivery Area 3". The posties deliver within the area 
by named recipient. This is a real address scheme.


The world is full of weird and wacky addressing. IMO, unless you're 
willing to confine your schema to only handling addresses of a 
particular area you know about, don't try to normalize address. Even 
then, I wouldn't try to normalize addresses with text processing, I'd 
ask the user to do it during form entry or not do it at all.


If you're trying to find duplicate addresses, matching addreses, etc, 
then IMO you're better off using existing tools that do this with 
free-form addresses using national phone databases, postcode databases, etc.


--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

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

[SQL] unique fields

[Adrian Johnson]

hi:

I have a fairly large table.

sample_id | chr | cfrom | cto |
---
1c219   20
2c219   20
3c219   20
1c510   11
3c510   11


(25,000 rows)

I want to find out how many duplications are there for chr, cfrom and cto

a.   c2,19,20 are common to samples 1,2 and 3.

since there will be many instances like that, do I have to loop over
entire rows and find common chr, cfrom and c2 and ouput with
sample_id.
how can I do that.

thanks
adrian

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