Re: [GENERAL] Update two tables returning id from insert CTE Query

[David G. Johnston]

On Tue, Sep 27, 2016 at 2:31 PM, Patrick B  wrote:

> 2016-09-28 10:25 GMT+13:00 Patrick B :
>
>>
>> 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
>

​​

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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 10:25 GMT+13:00 Patrick B :

>
>
> 2016-09-28 10:11 GMT+13:00 Kevin Grittner :
>
>> On Tue, Sep 27, 2016 at 3:33 PM, Patrick B 
>> 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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 10:11 GMT+13:00 Kevin Grittner :

> On Tue, Sep 27, 2016 at 3:33 PM, Patrick B 
> 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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Kevin Grittner]

On Tue, Sep 27, 2016 at 3:33 PM, Patrick B  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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 9:23 GMT+13:00 Kevin Grittner :

> On Tue, Sep 27, 2016 at 2:59 PM, Patrick B 
> 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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Kevin Grittner]

On Tue, Sep 27, 2016 at 2:59 PM, Patrick B  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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[David G. Johnston]

On Tue, Sep 27, 2016 at 1:10 PM, Igor Neyman  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.

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Patrick B
Sent: Tuesday, September 27, 2016 4:00 PM
To: David G. Johnston <david.g.johns...@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.johns...@gmail.com<mailto:david.g.johns...@gmail.com>>:
On Mon, Sep 26, 2016 at 9:06 PM, Patrick B 
<patrickbake...@gmail.com<mailto:patrickbake...@gmail.com>> wrote:

I'm doing this now:


sel AS (
SELECT i.id<http://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.


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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 8:54 GMT+13:00 David G. Johnston :

> On Mon, Sep 26, 2016 at 9:06 PM, Patrick B 
> 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.

Re: [GENERAL] Update two tables returning id from insert CTE Query

[David G. Johnston]

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B  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.​

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 0:29 GMT+13:00 Vitaly Burovoy :

> On 9/26/16, Patrick B  wrote:
> > 2016-09-27 16:22 GMT+13:00 Patrick B :
> > 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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Vitaly Burovoy]

On 9/26/16, Patrick B  wrote:
> 2016-09-27 16:22 GMT+13:00 Patrick B :
> 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


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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Vitaly Burovoy]

On 9/26/16, Patrick B  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

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-27 16:22 GMT+13:00 Patrick B :

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