Thread: Combine, Merge, Concatenate

Dear pgsql users,

I have a problem, which is quite hard to solve it in Perl (for me, of
course). I have to tables, which looks like

First Table:

1|001|002|003
2|006|04|002

Second Table:

001|text1|text2|text3
002|text6|text1|text2

Now I would like to concatenate this two tables into new table:

Third Table:

1|text1|text2|text3|text6 *
2|etc

*Notes; key=1 from first table; substitute 001 from first table with
text1|text2|text3 and 002 with text6 etc...

Is that possible in pgsql?

Thanks in advance for any notes and suggestions,

Cheers, Andre

On Mon, Jan 23, 2006 at 11:06:22AM +0000, Andrej Kastrin wrote:
> I have a problem, which is quite hard to solve it in Perl (for me, of
> course). I have to tables, which looks like
>
> First Table:
>
> 1|001|002|003
> 2|006|04|002
>
> Second Table:
>
> 001|text1|text2|text3
> 002|text6|text1|text2
>
> Now I would like to concatenate this two tables into new table:
>
> Third Table:
>
> 1|text1|text2|text3|text6 *
> 2|etc

I think you're looking for a query known as a "join."  Use a search
engine with words like "sql join tutorial" or check out the relevant
parts of the PostgreSQL documentation:

http://www.postgresql.org/docs/8.1/interactive/tutorial-join.html
http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html

Here's an example that might be similar to what you're looking for:

CREATE TABLE foo (
    id    integer PRIMARY KEY,
    col1  text NOT NULL,
    col2  text NOT NULL
);

CREATE TABLE bar (
    id      integer PRIMARY KEY,
    fooid1  integer NOT NULL REFERENCES foo,
    fooid2  integer NOT NULL REFERENCES foo
);

INSERT INTO foo VALUES (1, 'text1', 'text2');
INSERT INTO foo VALUES (2, 'text3', 'text4');
INSERT INTO foo VALUES (3, 'text5', 'text6');
INSERT INTO foo VALUES (4, 'text7', 'text8');

INSERT INTO bar VALUES (1, 1, 2);
INSERT INTO bar VALUES (2, 3, 4);

SELECT b.id,
       f1.col1, f1.col2,
       f2.col1 AS col3, f2.col2 AS col4
FROM bar AS b
JOIN foo AS f1 ON f1.id = b.fooid1
JOIN foo AS f2 ON f2.id = b.fooid2;

 id | col1  | col2  | col3  | col4
----+-------+-------+-------+-------
  1 | text1 | text2 | text3 | text4
  2 | text5 | text6 | text7 | text8
(2 rows)

--
Michael Fuhr