Update two tables returning id from insert CTE Query

2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:

Hi guys,

I've got 2k rows in a table:

CREATE TABLE
public.not_monthly
(
id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL,
clientid BIGINT,
name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
);

I want to insert data from public.not_monthly to
public.table_1(clientid,name_first) and public.table_2(client_id,c_id*(FROM
table_1)*,name_last)

table_2.c_id must have the ID from the insert on the table_1 table.

I did this:

WITH rows AS (
SELECT
t1.id,
t1.clientid,
t1.name_first,
t1.name_last
row_number() OVER (ORDER BY t1.id) AS rn
FROM
public.not_monthly t1
),
ins_table_1 AS (
INSERT INTO public.table_1 (clientid,name_first)
SELECT
clientid,
name_first
FROM rows
RETURNING id
),
ins_table_2 AS (
INSERT INTO public.table_2 (name_last,clientid)
SELECT
name_last,
clientid
FROM rows
RETURNING id
)

Then, I was able to select the table_1.id using:

SELECT i.id AS table_1_id, s.id AS not_monthly_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)

So I'd imagine now I would do the update? How can I update table_2.c_id
with the ins_table_1.id value?
I'm using Postgres 9.2

Thanks
Patrick

I'm doing this now:

sel AS (

SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get *ERROR: more than one row returned by a subquery used as an
expression*

On 9/26/16, Patrick B <patrickbakerbr@gmail.com> wrote:

Hi guys,

I've got 2k rows in a table:

...

So I'd imagine now I would do the update? How can I update table_2.c_id
with the ins_table_1.id value?
I'm using Postgres 9.2

Thanks
Patrick

Hello,

It is not possible to change one row more than once by one query.
You try to do so by inserting in ins_table_2 (it is "change" of a row)
and update the inserted row by a final query.

It is hard to understand what you want to do because your query is
very artificial: get non-unique data from a table, split it (to get
less unique data) but then "match" two non-unique data via inserted
unique identifier.

The only common hint I can give you is to use data from ins_table_1 in
SELECT part of the ins_table_2 (which should be now not CTE, but the
final query):
WITH rows AS (
SELECT ...
),
ins_table_1 AS (
INSERT INTO public.table_1 (clientid,name_first)
...
RETURNING id -- may be also clientid and name_first?
)
INSERT INTO public.table_2 (c_id, name_last,clientid)
SELECT
ins.id,
rows.name_last,
rows.clientid
FROM rows
INNER JOIN (
SELECT ..., ... OVER()... FROM ins_table_1
) ins ON (...)

Note than CTEs not have indexes and a join process is not fast (for
bigger number of rows).

--
Best regards,
Vitaly Burovoy

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

On 9/26/16, Patrick B <patrickbakerbr@gmail.com> wrote:

2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:
I'm doing this now:

sel AS (

SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1)
i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get *ERROR: more than one row returned by a subquery used as an
expression*

To update rows of one table by rows from another table you should use
UPDATE ... SET ... FROM ... WHERE ...
clause described in the docs[1]https://www.postgresql.org/docs/devel/static/sql-update.html -- Best regards, Vitaly Burovoy (see example around the sentence "A
similar result could be accomplished with a join:" and note below).

[1]: https://www.postgresql.org/docs/devel/static/sql-update.html -- Best regards, Vitaly Burovoy
--
Best regards,
Vitaly Burovoy

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

2016-09-28 0:29 GMT+13:00 Vitaly Burovoy <vitaly.burovoy@gmail.com>:

On 9/26/16, Patrick B <patrickbakerbr@gmail.com> wrote:

2016-09-27 16:22 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:
I'm doing this now:

sel AS (

SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1)
i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get *ERROR: more than one row returned by a subquery used as an
expression*

To update rows of one table by rows from another table you should use
UPDATE ... SET ... FROM ... WHERE ...
clause described in the docs[1] (see example around the sentence "A
similar result could be accomplished with a join:" and note below).

[1] https://www.postgresql.org/docs/devel/static/sql-update.html
--
Best regards,
Vitaly Burovoy

clientid is the same for all the rows....
Your example doesn't work. And isn't much different than mine.

Patrick

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

I'm doing this now:

sel AS (

SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get *ERROR: more than one row returned by a subquery used as an
expression*

​And this surprises you why?

I'd advise you get whatever it is you are trying to accomplish working
using multiple queries in a transaction, probably with the help of
temporary tables, then post that self-contained working example and ask for
suggestions on how to turn it into a single query using CTEs (if its ever
worth the effort at that point).

David J.​

2016-09-28 8:54 GMT+13:00 David G. Johnston <david.g.johnston@gmail.com>:

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:

I'm doing this now:

sel AS (

SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1)
i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get *ERROR: more than one row returned by a subquery used as an
expression*

​And this surprises you why?

I'd advise you get whatever it is you are trying to accomplish working
using multiple queries in a transaction, probably with the help of
temporary tables, then post that self-contained working example and ask for
suggestions on how to turn it into a single query using CTEs (if its ever
worth the effort at that point).

David J.​

isn't clear what I'm trying to achieve? That's what I need, I just want a
way to do that, as the way I'm doing isn't working.

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Patrick B
Sent: Tuesday, September 27, 2016 4:00 PM
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 8:54 GMT+13:00 David G. Johnston <david.g.johnston@gmail.com<mailto:david.g.johnston@gmail.com>>:
On Mon, Sep 26, 2016 at 9:06 PM, Patrick B <patrickbakerbr@gmail.com<mailto:patrickbakerbr@gmail.com>> wrote:

I'm doing this now:

sel AS (
SELECT i.id<http://i.id&gt; AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

But I get ERROR: more than one row returned by a subquery used as an expression

​And this surprises you why?

I'd advise you get whatever it is you are trying to accomplish working using multiple queries in a transaction, probably with the help of temporary tables, then post that self-contained working example and ask for suggestions on how to turn it into a single query using CTEs (if its ever worth the effort at that point).

David J.​

isn't clear what I'm trying to achieve? That's what I need, I just want a way to do that, as the way I'm doing isn't working.

Patrick,

You need to explain your problems in more “coherent” way, David suggested one.
If you aren’t willing, people will stop responding to your request, they are not obligated to read your mind.

Regards,
Igor Neyman

On Tue, Sep 27, 2016 at 1:10 PM, Igor Neyman <ineyman@perceptron.com> wrote:

Patrick,

You need to explain your problems in more “coherent” way, David suggested
one.

If you aren’t willing, people will stop responding to your request, they
are not obligated to read your mind.

​I'll put it this way - the only conclusion I can draw in reading the
provided code is that the data model is screwed up and may not be capable
of accommodating this goal. Albeit this is based upon a incompletely
specified model...

​If you want table1 and table2 to have a 1-to-1 relationship then the
assignment of the unique key should not occur via next_val() calls in the
default expression​ of either table. When you query "not_monthly" you need
to figure out what the new ID should be and explicitly add it to both
INSERT statements.

David J.

On Tue, Sep 27, 2016 at 2:59 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

[sel is a relation which can have multiple rows; the fact that it
is being generated in a CTE isn't relevant for purposes of the
error.]

UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

ERROR: more than one row returned by a subquery used as an expression

isn't clear what I'm trying to achieve?

Nope.

That's what I need, I just want a way to do that, as the way I'm
doing isn't working.

You are specifying that you want to assign all the "c_id" values
from the "sel" relation to the "c_id" column in "table2" for any
and all rows which have a "clientid" value of 124312. Effectively
the database is complaining that it can only store one value, not a
set of values. I can only guess at what you might be intending to
ask the database to do. Can you explain what you are trying to do?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

2016-09-28 9:23 GMT+13:00 Kevin Grittner <kgrittn@gmail.com>:

On Tue, Sep 27, 2016 at 2:59 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:

[sel is a relation which can have multiple rows; the fact that it
is being generated in a CTE isn't relevant for purposes of the
error.]

UPDATE table_2 SET c_id =
(
SELECT c_id
FROM sel
ORDER BY c_id
)
WHERE clientid = 124312;

ERROR: more than one row returned by a subquery used as an expression

isn't clear what I'm trying to achieve?

Nope.

That's what I need, I just want a way to do that, as the way I'm
doing isn't working.

You are specifying that you want to assign all the "c_id" values
from the "sel" relation to the "c_id" column in "table2" for any
and all rows which have a "clientid" value of 124312. Effectively
the database is complaining that it can only store one value, not a
set of values. I can only guess at what you might be intending to
ask the database to do. Can you explain what you are trying to do?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Sorry.. didn't mean to be rude... just in my thoughts I've already
explained it well.
Let see if the more clear now:

Table has data from a CSV file"

CREATE TABLE
public.not_monthly
(
id BIGINT DEFAULT "nextval"('"not_monthly_id_seq"'::"regclass") NOT
NULL,

clientid BIGINT,

name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
);

Target table number 1 - On this table, I'll get clientid and name_first
from public.not_monthly:

CREATE TABLE
table_1
(
id BIGINT DEFAULT "nextval"('"table_1_id_seq"'::"regclass") NOT
NULL,
clientid BIGINT DEFAULT 0 NOT NULL,
name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
);

Target table number 2 - On this table, I'll get clientid and name_last
from public.not_monthly
and c_id from table_1.id:

CREATE TABLE
table_2
(
id BIGINT DEFAULT "nextval"('"table_2_id_seq"'::"regclass") NOT
NULL,
c_id BIGINT, --This value must be taken from table_1 inserted sql
clientid BIGINT DEFAULT 0 NOT NULL,
name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
);

So, it would be:

*1 - Select the data*

WITH rows AS (
SELECT
t1.clientid,
t1.name_first,
t1.name_last
FROM
public.not_monthly t1
),

*2 - Insert the data into table_1(clientid,name_first)*

ins_table_1 AS (
INSERT INTO public.table_1 (clientid,name_first)
SELECT
clientid,
name_first
FROM rows
RETURNING id
),

*3 - Insert the data into table_2(clientid,name_last)*

ins_table_2 AS (
INSERT INTO public.table_2 (name_last,clientid)
SELECT
name_last,
clientid
FROM rows
RETURNING id
)

*4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into
table_2.c_id* - This is the problem.. how can I get the inserted id from
STEP2 and put it into c_id respecting the order?

clientid is the same for all the rows.. so I can't put a WHERE using
clientid because it won't work.

Patrick

On Tue, Sep 27, 2016 at 3:33 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into table_2.c_id
- This is the problem.. how can I get the inserted id from STEP2 and put it
into c_id respecting the order?

For DML you need to think of the data as being unordered sets, not
ordered lists. The whole concept of a relational database is that
related rows can be associated through their common data values.
You are splitting them apart and then trying to match them up again
to link them back together. You will be better off if you can
leave the relationship intact all the way through -- perhaps by
adding name_last to table_1.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

2016-09-28 10:11 GMT+13:00 Kevin Grittner <kgrittn@gmail.com>:

On Tue, Sep 27, 2016 at 3:33 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:

4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into

table_2.c_id

- This is the problem.. how can I get the inserted id from STEP2 and put

it

into c_id respecting the order?

For DML you need to think of the data as being unordered sets, not
ordered lists. The whole concept of a relational database is that
related rows can be associated through their common data values.
You are splitting them apart and then trying to match them up again
to link them back together. You will be better off if you can
leave the relationship intact all the way through -- perhaps by
adding name_last to table_1.

Can you give me examples please?
Patrick

2016-09-28 10:25 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:

2016-09-28 10:11 GMT+13:00 Kevin Grittner <kgrittn@gmail.com>:

On Tue, Sep 27, 2016 at 3:33 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:

4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into

table_2.c_id

- This is the problem.. how can I get the inserted id from STEP2 and

put it

into c_id respecting the order?

For DML you need to think of the data as being unordered sets, not
ordered lists. The whole concept of a relational database is that
related rows can be associated through their common data values.
You are splitting them apart and then trying to match them up again
to link them back together. You will be better off if you can
leave the relationship intact all the way through -- perhaps by
adding name_last to table_1.

Can you give me examples please?
Patrick

Actually I can't use name_last or name_first because some of the rows have
name_last/name_first = null

I'm inserting more columns that I shown:

CREATE TABLE

public.not_monthly
(
id BIGINT DEFAULT "nextval"('"not_monthly_id_seq"'::"regclass") NOT
NULL,
clientid BIGINT,
name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
company_name CHARACTER VARYING(80)
);

but the only value that is commun between table_1 and table_2 is the
clientid and c_id.
Clientid is the same for all the rows
c_Id is the column I need to update from the inserted on table_1

So.. not many options here

On Tue, Sep 27, 2016 at 2:31 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

2016-09-28 10:25 GMT+13:00 Patrick B <patrickbakerbr@gmail.com>:

Actually I can't use name_last or name_first because some of the rows

have name_last/name_first = null

I'm inserting more columns that I shown:

CREATE TABLE

public.not_monthly
(
id BIGINT DEFAULT "nextval"('"not_monthly_id_seq"'::"regclass") NOT
NULL,
clientid BIGINT,
name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
company_name CHARACTER VARYING(80)
);

but the only value that is commun between table_1 and table_2 is the
clientid and c_id.
Clientid is the same for all the rows
c_Id is the column I need to update from the inserted on table_1

So.. not many options here

​<not tested>​

​ALTER TABLE public.not_monthly ADD COLUMN c_id bigint NULL;

UPDATE public.not_monthly SET c_id = next_val('c_id_sequence')​; --might
need a bit of futzing to make this work, but I hope you get the idea...

INSERT INTO table_1 (clientid, c_id, first_name)
SELECT client_id, c_id, first_name FROM not_monthly;

INSERT INTO table_2 (clientid, c_id, last_name)
SELECT client_id, c_id, last_name FROM not_monthly;

David J.