Обсуждение: Returned row count doesn't match lines in output file

Поиск
Список
Период
Сортировка

Returned row count doesn't match lines in output file

От
"Tchouante, Merlin"
Дата:

Hello group,

 

I’m new to this group so please bear with me.

 

 

select count (u.user_id)

from users u, course_main cm, course_users cu

where cu.crsmain_pk1 = cm.pk1

and cu.users_pk1 = u.pk1

and cm.course_id = 'Org.dent.Training';

 

(4915 rows)

 

 

I’m executing an .sql file which looks like this:

 

\o /home/bbuser/banner/gradeload/sodorgusers.txt

\t on

select u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.student_id

from users u, course_main cm, course_users cu

where cu.crsmain_pk1 = cm.pk1

and cu.users_pk1 = u.pk1

and cm.course_id = 'Org.dent.Training'

order by u.lastname, u.firstname;

\t off

\o

 

When I look at the output file, it has a bunch of blank lines in between the records but displays a line count of 4916.  What is causing the blank lines?  When I strip away the blank lines from the file, I get 3525 lines.  Why aren’t all 4,915 records writing to the file?

 

 

Any and all help would be greatly appreciated.

 

Thanks,

  -- Merlin

 

 

Merlin D. Tchouante, Sr. IT Enterprise Application Developer
Center for Information Technology Services (CITS)
601 West Lombard Street
Baltimore, Maryland 21201-1512
mtchouan@umaryland.edu 
410-706-4489 * 410-706-1500 fax

 

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu

Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu

 

New UMB Logo

 

Вложения

Re: Returned row count doesn't match lines in output file

От
Tom Lane
Дата:
"Tchouante, Merlin" <mtchouan@umaryland.edu> writes:
> I'm executing an .sql file which looks like this:

> \o /home/bbuser/banner/gradeload/sodorgusers.txt
> \t on
> select u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.student_id
> from users u, course_main cm, course_users cu
> where cu.crsmain_pk1 = cm.pk1
> and cu.users_pk1 = u.pk1
> and cm.course_id = 'Org.dent.Training'
> order by u.lastname, u.firstname;
> \t off
> \o

> When I look at the output file, it has a bunch of blank lines in between the records but displays a line count of
4916. What is causing the blank lines? 

Null values in one or more of the columns you're concatenating, perhaps?
Concatenating a null with something else yields null.  (See
coalesce() for one ad-hoc way to fix that.)

            regards, tom lane

Re: Returned row count doesn't match lines in output file

От
Rob Sargent
Дата:


On Nov 7, 2019, at 10:57 AM, Tchouante, Merlin <mtchouan@umaryland.edu> wrote:

Hello group,
 
I’m new to this group so please bear with me.
 
 
select count (u.user_id)
from users u, course_main cm, course_users cu
where cu.crsmain_pk1 = cm.pk1
and cu.users_pk1 = u.pk1
and cm.course_id = 'Org.dent.Training';
 
(4915 rows)
 
 
I’m executing an .sql file which looks like this:
 
\o /home/bbuser/banner/gradeload/sodorgusers.txt
\t on
select u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.student_id
from users u, course_main cm, course_users cu
where cu.crsmain_pk1 = cm.pk1
and cu.users_pk1 = u.pk1
and cm.course_id = 'Org.dent.Training'
order by u.lastname, u.firstname;
\t off
\o
 
When I look at the output file, it has a bunch of blank lines in between the records but displays a line count of 4916.  What is causing the blank lines?  When I strip away the blank lines from the file, I get 3525 lines.  Why aren’t all 4,915 records writing to the file?
 

Are there any null values in any of the fields you’re concatenating?  Try the same query but return the separate columns.  Maybe add \pset null nil to get other than whitespace for null.

 
Any and all help would be greatly appreciated.
 
Thanks,
  -- Merlin
 
 
Merlin D. Tchouante, Sr. IT Enterprise Application Developer
Center for Information Technology Services (CITS) 
601 West Lombard Street 
Baltimore, Maryland 21201-1512 
mtchouan@umaryland.edu  
410-706-4489 * 410-706-1500 fax
 
Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu
Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu
 
<image001.jpg>

Re: Returned row count doesn't match lines in output file

От
Steve Midgley
Дата:


On Thu, Nov 7, 2019 at 9:58 AM Tchouante, Merlin <mtchouan@umaryland.edu> wrote:

Hello group,

 

I’m new to this group so please bear with me.

 

 

select count (u.user_id)

from users u, course_main cm, course_users cu

where cu.crsmain_pk1 = cm.pk1

and cu.users_pk1 = u.pk1

and cm.course_id = 'Org.dent.Training';

 

(4915 rows)

 

 

I’m executing an .sql file which looks like this:

 

\o /home/bbuser/banner/gradeload/sodorgusers.txt

\t on

select u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.student_id

from users u, course_main cm, course_users cu

where cu.crsmain_pk1 = cm.pk1

and cu.users_pk1 = u.pk1

and cm.course_id = 'Org.dent.Training'

order by u.lastname, u.firstname;

\t off

\o

 

When I look at the output file, it has a bunch of blank lines in between the records but displays a line count of 4916.  What is causing the blank lines?  When I strip away the blank lines from the file, I get 3525 lines.  Why aren’t all 4,915 records writing to the file?

 

 

If you just run a simple select on user_id (none of the pipe concat stuff) from the file, what results do you get? Also if you run the count statement from the file, what results do you get? (Just want to make sure you're not accidentally running against a different server, database or default schema as the source of the problem..)

Steve

Вложения

RE: Returned row count doesn't match lines in output file

От
"Tchouante, Merlin"
Дата:
Thank you so much for responding.

Yes, one of the selected columns is null; when I only select one column that I know can't be a null, it returns all of
them. I changed it to the below and it worked just fine, thank you so much. 

select
u.user_id||'|'||coalesce(u.firstname,'')||'|'||coalesce(u.lastname,'')||'|'||coalesce(u.email,'')||'|'||coalesce(u.student_id,'')

Thanks,
  -- Merlin
 
 
Merlin D. Tchouante, Sr. IT Enterprise Application Developer
Center for Information Technology Services (CITS)
601 West Lombard Street
Baltimore, Maryland 21201-1512
mtchouan@umaryland.edu 
410-706-4489 * 410-706-1500 fax

Please send Blackboard questions to the CITS support email address:  DL-CITSBbSupport@umaryland.edu
Please send Mediasite questions to the CITS support email address:  DL-CITSMediasiteSupport@umaryland.edu




-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, November 7, 2019 1:07 PM
To: Tchouante, Merlin <mtchouan@umaryland.edu>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Returned row count doesn't match lines in output file

"Tchouante, Merlin" <mtchouan@umaryland.edu> writes:
> I'm executing an .sql file which looks like this:

> \o /home/bbuser/banner/gradeload/sodorgusers.txt
> \t on
> select
> u.user_id||'|'||u.firstname||'|'||u.lastname||'|'||u.email||'|'||u.stu
> dent_id from users u, course_main cm, course_users cu where
> cu.crsmain_pk1 = cm.pk1 and cu.users_pk1 = u.pk1 and cm.course_id =
> 'Org.dent.Training'
> order by u.lastname, u.firstname;
> \t off
> \o

> When I look at the output file, it has a bunch of blank lines in between the records but displays a line count of
4916. What is causing the blank lines? 

Null values in one or more of the columns you're concatenating, perhaps?
Concatenating a null with something else yields null.  (See
coalesce() for one ad-hoc way to fix that.)

            regards, tom lane