Postgresql – match two tables in postgresql, sum the data in the number column and create a new column

postgresql

I have two tables. I have about 6500 rows in one table and about 450 rows in other table. The data in the two tables are the same.

What I really want to do is to collect the data in the column "adet" in my table of 450 rows(the table name is "depoyaeklenecek") and the "adet" column in the 6500-row table(the table name is depoadetleri"). Thus, I would like to refresh my table in a way that I can write the number that I have collected in the "adet" column in the 6500-row table.

However, there is a problem, I think I have to match the ids in the "kapino" column to be able to add them. Because the data in both tables are not sorted in the same way, the capino data are in mixed order. I want it to collect the data in the "adet" column in two tables when the "kapino" ids in both tables are equal.

They are not tables with the same rows anyway. One is about 450 lines and the other is 6500 lines. Since the data in one of them will not be in the other, I need to check if the data in the kapino column is equal, then I need to collect it, I think I'm not sure.

Can I ask for help for this? Thanks

Best Answer

As Lennart suggested, you should start with working example, which would help us better understood what the problem is, and what you need to be done. And please use english names in columns and table names.

(I wanted to response in comment, but apparently I have too less reputation to do that).

Basing on you description, though, it seems like simple join between these two tables, like:

select * 
from depoyaeklenecek d1 
inner join depoadetleri d2 on d2.kapino = d1.kapino

Going further, you should specify what to you understand by

collecting the data in the column "adet" - do you want to aggregate (sum?) them somehow? Or just fetch them to be used later?
refreshing my table in a way that I can write the number - do you mean that you want to update values in one table by values from the other one (per proper kapino number?) Or just want to sum these values and put them (where?)

Related Solutions

PostgreSQL – How to Merge Two Huge Tables Keeping Only Unique Rows

The basic problem can be solved with various simple queries. Considering all columns:

CREATE TABLE tbl3 AS
TABLE tbl1
UNION TABLE tbl2;

Given this additional information:

All columns except the id column should be considered for the unique check.

And:

I don't need to preserve the ID column.

Just drop the id column, then you can proceed with the simple query above.

I would import to temporary tables (much faster, less overhead) and only write the final result (tbl3) to a regular table - in one session because temporary tables are dropped automatically at the end of the session.

CREATE TEMP TABLE tbl1 ( <columns from above, without id> );
COPY tbl1 FROM '/path/to/file1';

CREATE TEMP TABLE tbl2 ( <columns from above, without id> );
COPY tbl2 FROM '/path/to/file2';

Alternatively, to preserve the input tables across sessions, you could use unlogged tables.

For best performance create and fill the target with CREATE TABLE AS and add the PK constraint in the same transaction:

BEGIN;

CREATE SEQUENCE tbl3_tbl3_id_seq;

CREATE TABLE tbl3 AS 
SELECT nextval('tbl3_tbl3_id_seq'::regclass)::int AS tbl3_id, *
FROM  (TABLE tbl1 UNION TABLE tbl2 ) sub;

ALTER TABLE tbl3
   ADD CONSTRAINT tbl3_pkey PRIMARY KEY(tbl3_id)
 , ALTER COLUMN tbl3_id SET DEFAULT nextval('tbl3_tbl3_id_seq'::regclass);

ALTER SEQUENCE tbl3_tbl3_id_seq OWNED BY tbl3.tbl3_id;    

COMMIT;

Replace all occurrences of "tbl3" with our desired table name.

Detailed explanation in this related answer:

What causes large INSERT to slow down and disk usage to explode?

I added a serial column (tbl3_id) as surrogate PK to the target table. Adding the actual PK constraint at the end (of the same session) is the fastest way.

Optimizing bulk update performance in PostgreSQL

Before you do it, test whether double precision is the best data type for all those columns. Chances are, some of them could be integer (cheaper for whole numbers) or must really be numeric (loss-less). If so, adapt your temp tables to begin with.

PostgreSQL Error: Extra Data After Last Expected Column

Turned out to be a minor corruption of the SQL backup file. After re-running the entire process again, the restore worked fine with no errors.

You would think that Postgres would have in-built checking to ensure integrity of exported data!

Best Answer

Related Solutions

PostgreSQL – How to Merge Two Huge Tables Keeping Only Unique Rows

PostgreSQL Error: Extra Data After Last Expected Column

Related Question