FW: Undelivered Mail Returned to Sender

[stan]

I apologize for asking, what I suspect will turn out to be a newbie
question, but I have managed to get myself quite confused on this.

I am defining a view as follows


CREATE OR REPLACE view purchase_view as 
select 
project.proj_no ,
qty ,
mfg_part.mfg_part_no ,
mfg.name as m_name ,
mfg_part.descrip as description ,
( 
SELECT 
name
FROM
vendor
WHERE
bom_item.vendor_key =
(
SELECT
vendor_key
FROM
mfg_vendor_relationship
WHERE
bom_item.mfg_key = mfg_key
AND
prefered = TRUE
AND
bom_item.project_key = project_key

)
) 
as v_name ,
/*
vendor.name as v_name ,
*/
cost_per_unit ,
costing_unit.unit,
need_date ,
order_date ,
recieved_date ,
po_no ,
po_line_item 
from 
bom_item
right join project on 
project.project_key = bom_item.project_key
inner join mfg_part on 
mfg_part.mfg_part_key = bom_item.mfg_part_key
inner join vendor on 
vendor.vendor_key = bom_item.vendor_key
inner join costing_unit on 
costing_unit.costing_unit_key = bom_item.costing_unit_key
inner join mfg on 
mfg.mfg_key = bom_item.mfg_key 
WHERE bom_item is NOT NULL  
ORDER BY 
project.proj_no ,
mfg_part
;

Most of the tables are pretty much simple key -> value relationships for
normalization. I can add the create statements to this thread if it adds
clarity.

The exception is:



CREATE TABLE mfg_vendor_relationship (
mfg_vendor_relationship_key_serial integer DEFAULT 
nextval('mfg_vendor_relationship_key_serial')
PRIMARY KEY ,
mfg_key   integer NOT NULL,
vendor_keyinteger NOT NULL,
project_key   integer NOT NULL,
prefered  boolean NOT NULL ,
modtime   timestamptz DEFAULT current_timestamp ,
FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
FOREIGN KEY (project_key) references project(project_key) ,
CONSTRAINT mfg_vendor_constraint 
UNIQUE (
mfg_key , 
vendor_key , 
project_key
)
);


I am down to having a single row in the mfg_vendor_relationship as follows:

 mfg_vendor_relationship_key_serial | mfg_key | vendor_key | project_key |
 prefered |modtime
 
+-++-+--+---
 164 |   1 |  1 |   2 |
 t| 2019-08-10 14:21:04.896619-04

But trying to do a select * from this view returns:

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

Can someone please enlighten me as to the error of my ways?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin


- End forwarded message -

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Probably a newbie question

[stan]

Sorry, I got the list address wrong the first time, and when I corected it,
I forget to fix the subject line.

I apologize for asking, what I suspect will turn out to be a newbie
question, but I have managed to get myself quite confused on this.

I am defining a view as follows


CREATE OR REPLACE view purchase_view as 
select 
project.proj_no ,
qty ,
mfg_part.mfg_part_no ,
mfg.name as m_name ,
mfg_part.descrip as description ,
( 
SELECT 
name
FROM
vendor
WHERE
bom_item.vendor_key =
(
SELECT
vendor_key
FROM
mfg_vendor_relationship
WHERE
bom_item.mfg_key = mfg_key
AND
prefered = TRUE
AND
bom_item.project_key = project_key

)
) 
as v_name ,
/*
vendor.name as v_name ,
*/
cost_per_unit ,
costing_unit.unit,
need_date ,
order_date ,
recieved_date ,
po_no ,
po_line_item 
from 
bom_item
right join project on 
project.project_key = bom_item.project_key
inner join mfg_part on 
mfg_part.mfg_part_key = bom_item.mfg_part_key
inner join vendor on 
vendor.vendor_key = bom_item.vendor_key
inner join costing_unit on 
costing_unit.costing_unit_key = bom_item.costing_unit_key
inner join mfg on 
mfg.mfg_key = bom_item.mfg_key 
WHERE bom_item is NOT NULL  
ORDER BY 
project.proj_no ,
mfg_part
;

Most of the tables are pretty much simple key -> value relationships for
normalization. I can add the create statements to this thread if it adds
clarity.

The exception is:



CREATE TABLE mfg_vendor_relationship (
mfg_vendor_relationship_key_serial integer DEFAULT 
nextval('mfg_vendor_relationship_key_serial')
PRIMARY KEY ,
mfg_key   integer NOT NULL,
vendor_keyinteger NOT NULL,
project_key   integer NOT NULL,
prefered  boolean NOT NULL ,
modtime   timestamptz DEFAULT current_timestamp ,
FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
FOREIGN KEY (project_key) references project(project_key) ,
CONSTRAINT mfg_vendor_constraint 
UNIQUE (
mfg_key , 
vendor_key , 
project_key
)
);


I am down to having a single row in the mfg_vendor_relationship as follows:

 mfg_vendor_relationship_key_serial | mfg_key | vendor_key | project_key |
 prefered |modtime
 
+-++-+--+---
 164 |   1 |  1 |   2 |
 t| 2019-08-10 14:21:04.896619-04

But trying to do a select * from this view returns:

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

Can someone please enlighten me as to the error of my ways?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin


- End forwarded message -

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin



-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Probably a newbie question

[Ron]

On 8/10/19 1:57 PM, stan wrote:

Sorry, I got the list address wrong the first time, and when I corected it,
I forget to fix the subject line.

I apologize for asking, what I suspect will turn out to be a newbie
question, but I have managed to get myself quite confused on this.

I am defining a view as follows


CREATE OR REPLACE view purchase_view as
select
project.proj_no ,
qty ,
mfg_part.mfg_part_no ,
mfg.name as m_name ,
mfg_part.descrip as description ,
(
SELECT
name
FROM
vendor
WHERE
bom_item.vendor_key =
(
SELECT
vendor_key
FROM
mfg_vendor_relationship
WHERE
bom_item.mfg_key = mfg_key
AND
prefered = TRUE
AND
bom_item.project_key = project_key

)
)
as v_name ,
/*
vendor.name as v_name ,
*/
cost_per_unit ,
costing_unit.unit,
need_date ,
order_date ,
recieved_date ,
po_no ,
po_line_item
from
bom_item
right join project on
project.project_key = bom_item.project_key
inner join mfg_part on
mfg_part.mfg_part_key = bom_item.mfg_part_key
inner join vendor on
vendor.vendor_key = bom_item.vendor_key
inner join costing_unit on
costing_unit.costing_unit_key = bom_item.costing_unit_key
inner join mfg on
mfg.mfg_key = bom_item.mfg_key
WHERE bom_item is NOT NULL
ORDER BY
project.proj_no ,
mfg_part
;

Most of the tables are pretty much simple key -> value relationships for
normalization. I can add the create statements to this thread if it adds
clarity.

The exception is:



CREATE TABLE mfg_vendor_relationship (
 mfg_vendor_relationship_key_serial integer DEFAULT 
nextval('mfg_vendor_relationship_key_serial')
 PRIMARY KEY ,
 mfg_key   integer NOT NULL,
 vendor_keyinteger NOT NULL,
 project_key   integer NOT NULL,
 prefered  boolean NOT NULL ,
 modtime   timestamptz DEFAULT current_timestamp ,
 FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
 FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
 FOREIGN KEY (project_key) references project(project_key) ,
 CONSTRAINT mfg_vendor_constraint
 UNIQUE (
mfg_key ,
vendor_key ,
project_key
)
);


I am down to having a single row in the mfg_vendor_relationship as follows:

  mfg_vendor_relationship_key_serial | mfg_key | vendor_key | project_key |
  prefered |modtime
  
+-++-+--+---
 164 |   1 |  1 |   2 |
 t| 2019-08-10 14:21:04.896619-04

But trying to do a select * from this view returns:

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

Can someone please enlighten me as to the error of my ways?




I'd look here:

SELECT
name
FROM
vendor
WHERE
bom_item.vendor_key =
(
SELECT
vendor_key
FROM
mfg_vendor_relationship
WHERE
bom_item.mfg_key = mfg_key
AND
prefered = TRUE
AND
bom_item.project_key = project_key

)


--
Angular momentum makes the world go 'round.

Re: Probably a newbie question

[Rob Sargent]

> 
> I'd look here:
> 
>   SELECT
>   name
>   FROM
>   vendor
>   WHERE
>   bom_item.vendor_key =
>   (
>   SELECT
>   vendor_key
>   FROM
>   mfg_vendor_relationship
>   WHERE
>   bom_item.mfg_key = mfg_key
>   AND
>   prefered = TRUE
>   AND
>   bom_item.project_key = project_key
>   
>   )
> 
> 
> -- 
> Angular momentum makes the world go ‘round.

You might get away with adding
group by vendor_key
if it turns out you’re simply getting many copies of vendor key from that inner 
select.
Run it alone to see.

Re: FW: Undelivered Mail Returned to Sender

[Melvin Davidson]

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

Well, we really do not have any insight as to the contents of your data, but
have you thought about using *SELECT DISTINCT i*n your subquerys?

On Sat, Aug 10, 2019 at 2:53 PM stan  wrote:

> I apologize for asking, what I suspect will turn out to be a newbie
> question, but I have managed to get myself quite confused on this.
>
> I am defining a view as follows
>
>
> CREATE OR REPLACE view purchase_view as
> select
> project.proj_no ,
> qty ,
> mfg_part.mfg_part_no ,
> mfg.name as m_name ,
> mfg_part.descrip as description ,
> (
> SELECT
> name
> FROM
> vendor
> WHERE
> bom_item.vendor_key =
> (
> SELECT
> vendor_key
> FROM
> mfg_vendor_relationship
> WHERE
> bom_item.mfg_key = mfg_key
> AND
> prefered = TRUE
> AND
> bom_item.project_key = project_key
>
> )
> )
> as v_name ,
> /*
> vendor.name as v_name ,
> */
> cost_per_unit ,
> costing_unit.unit,
> need_date ,
> order_date ,
> recieved_date ,
> po_no ,
> po_line_item
> from
> bom_item
> right join project on
> project.project_key = bom_item.project_key
> inner join mfg_part on
> mfg_part.mfg_part_key = bom_item.mfg_part_key
> inner join vendor on
> vendor.vendor_key = bom_item.vendor_key
> inner join costing_unit on
> costing_unit.costing_unit_key = bom_item.costing_unit_key
> inner join mfg on
> mfg.mfg_key = bom_item.mfg_key
> WHERE bom_item is NOT NULL
> ORDER BY
> project.proj_no ,
> mfg_part
> ;
>
> Most of the tables are pretty much simple key -> value relationships for
> normalization. I can add the create statements to this thread if it adds
> clarity.
>
> The exception is:
>
>
>
> CREATE TABLE mfg_vendor_relationship (
> mfg_vendor_relationship_key_serial integer DEFAULT
> nextval('mfg_vendor_relationship_key_serial')
> PRIMARY KEY ,
> mfg_key   integer NOT NULL,
> vendor_keyinteger NOT NULL,
> project_key   integer NOT NULL,
> prefered  boolean NOT NULL ,
> modtime   timestamptz DEFAULT current_timestamp ,
> FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
> FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
> FOREIGN KEY (project_key) references project(project_key) ,
> CONSTRAINT mfg_vendor_constraint
> UNIQUE (
> mfg_key ,
> vendor_key ,
> project_key
> )
> );
>
>
> I am down to having a single row in the mfg_vendor_relationship as follows:
>
>  mfg_vendor_relationship_key_serial | mfg_key | vendor_key | project_key |
>  prefered |modtime
>
>  
> +-++-+--+---
>  164 |   1 |  1 |   2 |
>  t| 2019-08-10 14:21:04.896619-04
>
> But trying to do a select * from this view returns:
>
> ERROR:  more than one row returned by a subquery used as an expression
>
> Can someone please enlighten me as to the error of my ways?
>
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
> - End forwarded message -
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Probably a newbie question

[Melvin Davidson]

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

Without knowledge as to the contents of your data, the best I can suggest is
to use *SELECT DISTINCT* in your subqueries.

On Sat, Aug 10, 2019 at 3:42 PM Rob Sargent  wrote:

>
>
> I'd look here:
>
> SELECT
> name
> FROM
> vendor
> WHERE
> bom_item.vendor_key =
> (
> SELECT
> vendor_key
> FROM
> mfg_vendor_relationship
> WHERE
> bom_item.mfg_key = mfg_key
> AND
> prefered = TRUE
> AND
> bom_item.project_key = project_key
>
> )
>
>
> --
> Angular momentum makes the world go ‘round.
>
>
> You might get away with adding
> group by vendor_key
> if it turns out you’re simply getting many copies of vendor key from that
> inner select.
> Run it alone to see.
>
>
>

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Probably a newbie question

[David G. Johnston]

On Saturday, August 10, 2019, stan  wrote:

> Sorry, I got the list address wrong the first time, and when I corected it,
> I forget to fix the subject line.
>
>
This subject line isn’t materially better...subjects should reflect the
technical content of the message, not its sender.

David J.

Re: Bulk Inserts

[Souvik Bhattacherjee]

Hi Adrian,

Thanks for the response.

> Yes, but you will some code via client or function that batches the
> inserts for you.

Could you please elaborate a bit on how EXP 1 could be performed such that
it uses bulk inserts?

Best,
-SB

On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver 
wrote:

> On 8/9/19 3:06 PM, Souvik Bhattacherjee wrote:
> > Hi,
> >
> > I'm trying to measure the performance of the following: Multiple txns
> > inserting tuples into a table concurrently vs single txn doing the whole
> > insertion.
> >
> > *new table created as:*
> > create table tab2 (
> > id serial,
> > attr1 integer not null,
> > attr2 integer not null,
> > primary key(id)
> > );
> >
> > *EXP 1: inserts with multiple txn:*
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
> > attr2 = 10);
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
> > attr2 = 20);
> >
> > note: attr2 has only two values 10 and 20
> >
> > *EXP 2: inserts with a single txn:*
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1);
> >
> > I also performed another experiment as follows:
> > *EXP 3:* select attr1, attr2 into tab2 from tab1;
> >
> > The observation here is EXP 3  is much faster than EXP 2 probably due to
> > bulk inserts used by Postgres. However I could not find a way to insert
> > id values in tab2 using EXP 3. Also select .. into .. from .. throws an
> > error if we create a table first and then populate the tuples using the
> > command.
>
> Yes as SELECT INTO is functionally the same as CREATE TABLE AS:
>
> https://www.postgresql.org/docs/11/sql-selectinto.html
>
> >
> > I have the following questions:
> > 1. Is it possible to have an id column in tab2 and perform a bulk insert
> > using select .. into .. from .. or using some other means?
>
> Not using SELECT INTO for reasons given above.
> Though it is possible to SELECT INTO as you show in EXP 3 and then:
> alter table tab2 add column id serial primary key;
> EXP 2 shows the other means.
>
> > 2. If a table is already created, is it possible to do bulk inserts via
> > multiple txns inserting into the same table (EXP 3)?
>
> Yes, but you will some code via client or function that batches the
> inserts for you.
>
> >
> > Best,
> > -SB
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Bulk Inserts

[Rob Sargent]

> On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee  wrote:
> 
> Hi Adrian,
> 
> Thanks for the response.
> 
> > Yes, but you will some code via client or function that batches the 
> > inserts for you.
> 
> Could you please elaborate a bit on how EXP 1 could be performed such that it 
> uses bulk inserts?
> 
> Best,
> -SB
> 
> On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver  > wrote:
> On 8/9/19 3:06 PM, Souvik Bhattacherjee wrote:
> > Hi,
> > 
> > I'm trying to measure the performance of the following: Multiple txns 
> > inserting tuples into a table concurrently vs single txn doing the whole 
> > insertion.
> > 
> > *new table created as:*
> > create table tab2 (
> > id serial,
> > attr1 integer not null,
> > attr2 integer not null,
> > primary key(id)
> > );
> > 
> > *EXP 1: inserts with multiple txn:*
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where 
> > attr2 = 10);
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where 
> > attr2 = 20);
> > 
> > note: attr2 has only two values 10 and 20
> > 
> > *EXP 2: inserts with a single txn:*
> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1);
> > 
> > I also performed another experiment as follows:
> > *EXP 3:* select attr1, attr2 into tab2 from tab1;
> > 
> > The observation here is EXP 3  is much faster than EXP 2 probably due to 
> > bulk inserts used by Postgres. However I could not find a way to insert 
> > id values in tab2 using EXP 3. Also select .. into .. from .. throws an 
> > error if we create a table first and then populate the tuples using the 
> > command.
> 
> Yes as SELECT INTO is functionally the same as CREATE TABLE AS:
> 
> https://www.postgresql.org/docs/11/sql-selectinto.html 
> 
> 
> > 
> > I have the following questions:
> > 1. Is it possible to have an id column in tab2 and perform a bulk insert 
> > using select .. into .. from .. or using some other means?
> 
> Not using SELECT INTO for reasons given above.
> Though it is possible to SELECT INTO as you show in EXP 3 and then:
> alter table tab2 add column id serial primary key;
> EXP 2 shows the other means.
> 
> > 2. If a table is already created, is it possible to do bulk inserts via 
> > multiple txns inserting into the same table (EXP 3)?
> 
> Yes, but you will some code via client or function that batches the 
> inserts for you.
> 
> > 
> > Best,
> > -SB
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 
Top-posting (i.e. putting your reply at the top is discouraged here)
Does this appeal to you:
COPY (SELECT * FROM relation) TO ... 
(https://www.postgresql.org/docs/10/sql-copy.html 
)

Re: Bulk Inserts

[Souvik Bhattacherjee]

> Does this appeal to you:
> COPY (SELECT * FROM relation) TO ... (
https://www.postgresql.org/docs/10/sql-copy.html)

Not sure if COPY can be used to transfer data between tables.

On Sat, Aug 10, 2019 at 11:01 PM Rob Sargent  wrote:

>
>
> On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee 
> wrote:
>
> Hi Adrian,
>
> Thanks for the response.
>
> > Yes, but you will some code via client or function that batches the
> > inserts for you.
>
> Could you please elaborate a bit on how EXP 1 could be performed such that
> it uses bulk inserts?
>
> Best,
> -SB
>
> On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver 
> wrote:
>
>> On 8/9/19 3:06 PM, Souvik Bhattacherjee wrote:
>> > Hi,
>> >
>> > I'm trying to measure the performance of the following: Multiple txns
>> > inserting tuples into a table concurrently vs single txn doing the
>> whole
>> > insertion.
>> >
>> > *new table created as:*
>> > create table tab2 (
>> > id serial,
>> > attr1 integer not null,
>> > attr2 integer not null,
>> > primary key(id)
>> > );
>> >
>> > *EXP 1: inserts with multiple txn:*
>> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
>> > attr2 = 10);
>> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
>> > attr2 = 20);
>> >
>> > note: attr2 has only two values 10 and 20
>> >
>> > *EXP 2: inserts with a single txn:*
>> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1);
>> >
>> > I also performed another experiment as follows:
>> > *EXP 3:* select attr1, attr2 into tab2 from tab1;
>> >
>> > The observation here is EXP 3  is much faster than EXP 2 probably due
>> to
>> > bulk inserts used by Postgres. However I could not find a way to insert
>> > id values in tab2 using EXP 3. Also select .. into .. from .. throws an
>> > error if we create a table first and then populate the tuples using the
>> > command.
>>
>> Yes as SELECT INTO is functionally the same as CREATE TABLE AS:
>>
>> https://www.postgresql.org/docs/11/sql-selectinto.html
>>
>> >
>> > I have the following questions:
>> > 1. Is it possible to have an id column in tab2 and perform a bulk
>> insert
>> > using select .. into .. from .. or using some other means?
>>
>> Not using SELECT INTO for reasons given above.
>> Though it is possible to SELECT INTO as you show in EXP 3 and then:
>> alter table tab2 add column id serial primary key;
>> EXP 2 shows the other means.
>>
>> > 2. If a table is already created, is it possible to do bulk inserts via
>> > multiple txns inserting into the same table (EXP 3)?
>>
>> Yes, but you will some code via client or function that batches the
>> inserts for you.
>>
>> >
>> > Best,
>> > -SB
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
> Top-posting (i.e. putting your reply at the top is discouraged here)
> Does this appeal to you:
>
> COPY (SELECT * FROM relation) TO ... (
> https://www.postgresql.org/docs/10/sql-copy.html)
>
>
>

Re: FW: Undelivered Mail Returned to Sender

[rob stone]

Hello,

On Sat, 2019-08-10 at 14:53 -0400, stan wrote:
> I apologize for asking, what I suspect will turn out to be a newbie
> question, but I have managed to get myself quite confused on this.
> 
> I am defining a view as follows
> 
> 
> CREATE OR REPLACE view purchase_view as 
> select 
>   project.proj_no ,
>   qty ,
>   mfg_part.mfg_part_no ,
>   mfg.name as m_name ,
>   mfg_part.descrip as description ,
>   ( 
>   SELECT 
>   name
>   FROM
>   vendor
>   WHERE
>   bom_item.vendor_key =
>   (
>   SELECT
>   vendor_key
>   FROM
>   mfg_vendor_relationship
>   WHERE
>   bom_item.mfg_key = mfg_key
>   AND
>   prefered = TRUE
>   AND
>   bom_item.project_key = project_key
>   
>   )
>   ) 
>   as v_name ,
>   /*
>   vendor.name as v_name ,
>   */
>   cost_per_unit ,
>   costing_unit.unit,
>   need_date ,
>   order_date ,
>   recieved_date ,
>   po_no ,
>   po_line_item 
> from 
>   bom_item
> right join project on 
>   project.project_key = bom_item.project_key
> inner join mfg_part on 
>   mfg_part.mfg_part_key = bom_item.mfg_part_key
> inner join vendor on 
>   vendor.vendor_key = bom_item.vendor_key
> inner join costing_unit on 
>   costing_unit.costing_unit_key = bom_item.costing_unit_key
> inner join mfg on 
>   mfg.mfg_key = bom_item.mfg_key 
> WHERE bom_item is NOT NULL  
> ORDER BY 
>   project.proj_no ,
>   mfg_part
>   ;
> 
> Most of the tables are pretty much simple key -> value relationships
> for
> normalization. I can add the create statements to this thread if it
> adds
> clarity.
> 
> The exception is:
> 
> 
> 
> CREATE TABLE mfg_vendor_relationship (
> mfg_vendor_relationship_key_serial integer DEFAULT
> nextval('mfg_vendor_relationship_key_serial')
> PRIMARY KEY ,
> mfg_key   integer NOT NULL,
> vendor_keyinteger NOT NULL,
> project_key   integer NOT NULL,
> prefered  boolean NOT NULL ,
> modtime   timestamptz DEFAULT current_timestamp ,
> FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
> FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
> FOREIGN KEY (project_key) references project(project_key) ,
> CONSTRAINT mfg_vendor_constraint 
> UNIQUE (
>   mfg_key , 
>   vendor_key , 
>   project_key
>   )
> );
> 
> 
> I am down to having a single row in the mfg_vendor_relationship as
> follows:
> 
>  mfg_vendor_relationship_key_serial | mfg_key | vendor_key |
> project_key |
>  prefered |modtime
>  +-++--
> ---+--+---
>164 |   1 |  1
> |   2 |
>t| 2019-08-10 14:21:04.896619-
> 04
> 
> But trying to do a select * from this view returns:
> 
> ERROR:  more than one row returned by a subquery used as an
> expression
> 
> Can someone please enlighten me as to the error of my ways?
> 
> 
> -- 
> "They that would give up essential liberty for temporary safety
> deserve
> neither liberty nor safety."
>   -- Benjamin Franklin
> 
> 
> - End forwarded message -
> 


You are selecting from a table named bom_item, but further down you
have

WHERE bom_item is NOT NULL

Shouldn't that be WHERE bom_item.some_column_name IS NOT NULL?

Cheers,
Rob