agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedReturned row count doesn't match lines in output file
5+ messages / 4 participants
[nested] [flat]
* Returned row count doesn't match lines in output file
@ 2019-11-07 17:57 Tchouante, Merlin <mtchouan@umaryland.edu>
2019-11-07 18:06 ` Re: Returned row count doesn't match lines in output file Tom Lane <tgl@sss.pgh.pa.us>
2019-11-07 18:06 ` Re: Returned row count doesn't match lines in output file Rob Sargent <robjsargent@gmail.com>
2019-11-07 18:06 ` Re: Returned row count doesn't match lines in output file Steve Midgley <science@misuse.org>
0 siblings, 3 replies; 5+ messages in thread
From: Tchouante, Merlin @ 2019-11-07 17:57 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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<mailto:mtchouan@umaryland.edu>
410-706-4489 * 410-706-1500 fax
Please send Blackboard questions to the CITS support email address: DL-CITSBbSupport@umaryland.edu<mailto:dl-citsbbsupport@umaryland.edu>
Please send Mediasite questions to the CITS support email address: DL-CITSMediasiteSupport@umaryland.edu<mailto:DL-CITSMediasiteSupport@umaryland.edu>
[New UMB Logo]
Attachments:
[image/jpeg] image001.jpg (11.5K, ../../BL0PR12MB2562AE54C7A5A8A680421FC7D2780@BL0PR12MB2562.namprd12.prod.outlook.com/3-image001.jpg)
download | view image
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Returned row count doesn't match lines in output file
2019-11-07 17:57 Returned row count doesn't match lines in output file Tchouante, Merlin <mtchouan@umaryland.edu>
@ 2019-11-07 18:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-11-07 18:29 ` RE: Returned row count doesn't match lines in output file Tchouante, Merlin <mtchouan@umaryland.edu>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2019-11-07 18:06 UTC (permalink / raw)
To: Tchouante, Merlin <mtchouan@umaryland.edu>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Returned row count doesn't match lines in output file
2019-11-07 17:57 Returned row count doesn't match lines in output file Tchouante, Merlin <mtchouan@umaryland.edu>
2019-11-07 18:06 ` Re: Returned row count doesn't match lines in output file Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-11-07 18:29 ` Tchouante, Merlin <mtchouan@umaryland.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Tchouante, Merlin @ 2019-11-07 18:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Returned row count doesn't match lines in output file
2019-11-07 17:57 Returned row count doesn't match lines in output file Tchouante, Merlin <mtchouan@umaryland.edu>
@ 2019-11-07 18:06 ` Rob Sargent <robjsargent@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2019-11-07 18:06 UTC (permalink / raw)
To: Tchouante, Merlin <mtchouan@umaryland.edu>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
> 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 <mailto:mtchouan@umaryland.edu>
> 410-706-4489 * 410-706-1500 fax
>
> Please send Blackboard questions to the CITS support email address: DL-CITSBbSupport@umaryland.edu <mailto:dl-citsbbsupport@umaryland.edu>
> Please send Mediasite questions to the CITS support email address: DL-CITSMediasiteSupport@umaryland.edu <mailto:DL-CITSMediasiteSupport@umaryland.edu>
>
> <image001.jpg>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Returned row count doesn't match lines in output file
2019-11-07 17:57 Returned row count doesn't match lines in output file Tchouante, Merlin <mtchouan@umaryland.edu>
@ 2019-11-07 18:06 ` Steve Midgley <science@misuse.org>
2 siblings, 0 replies; 5+ messages in thread
From: Steve Midgley @ 2019-11-07 18:06 UTC (permalink / raw)
To: Tchouante, Merlin <mtchouan@umaryland.edu>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
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
Attachments:
[image/jpeg] image001.jpg (11.5K, ../../CAJexoSL0CJ-VY7Nhfv1Yrg6b4h2Pd=Nsb+QdYTH-s_=YbjJy2Q@mail.gmail.com/3-image001.jpg)
download | view image
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-11-07 18:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-07 17:57 Returned row count doesn't match lines in output file Tchouante, Merlin <mtchouan@umaryland.edu>
2019-11-07 18:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-11-07 18:29 ` Tchouante, Merlin <mtchouan@umaryland.edu>
2019-11-07 18:06 ` Rob Sargent <robjsargent@gmail.com>
2019-11-07 18:06 ` Steve Midgley <science@misuse.org>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox