Postgresql row count not working accurately

Question

I have a table with 2.44 million rows, and after loading it into server:

copy sample_table
from 'C:\sample_table.txt'
delimiter E'\t'
csv header

if I do

select count(*) from sample_table

pgAdmin 4 will return count as only 1.35 million rows

I found it odd, so I exported this table and looked at the number of rows in Notepad++, and it is still 2.44 million rows (in fact there is 1 row count difference and not sure why, but guess will worry about that later)

As recommended by Adrian in comments, I verified this in psql, and still only see 1.35M.

Any advice please? Thank you!

The one row difference would be the header row. Are you sure you are connected to the same database in pgAdmin as the database you imported the CSV into? Or is there more then one sample_table in the database and you dealing with versions in different schema? Have you logged in with psql to do the count(*) to verify? — Adrian Klaver
– Adrian Klaver, Commented Jul 25, 2021 at 23:52
Thank you@AdrianKlaver. The table name is not sample_table - I used it here for simplicity. The real name is quite unique, which I only started to use today. Also verified in psql, please see the screenshot above. — Helene
– Helene, Commented Jul 26, 2021 at 0:40
Hi. I'd still suspect something wrong with the input that is being undone when the table is dumped. Maybe the final column is a sometimes improperly escaped string that causes psql to gobble the next line or two as part of the column. Dumping might produce a correct-looking text file even though the number of records is wrong. You could check for this problem by filtering the input file to get a list of primary keys. Then write a little script to seek keys with no corresponding row. The only other possibility I see is a broken psql... a bad build. That's fairly unlikely. — Gene
– Gene, Commented Jul 26, 2021 at 1:24
Are there string fields with linebreaks in the data? (Not sure how escaping works in CSV) — Bergi
– Bergi, Commented Jul 26, 2021 at 1:39
If there are line breaks in the input data, such as text with carriage returns, the number of rows will be different from the number of lines. — Jeremy
– Jeremy, Commented Jul 26, 2021 at 1:41

jjanes · Accepted Answer · 2021-07-26 01:51:50Z

2

When using CSV format, literal newlines enclosed in quotes do not terminate a row.

"How now
Brown cow"

Is 2 "lines", but only 1 row.

If you re-export in the default text format, then the number of lines should match the number of rows, with the literal newlines turned into the two-character escape \n

answered Jul 26, 2021 at 1:51

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Helene Over a year ago

Thank you @jjanes - you and many who left comments are right. The table indeed has quotes at the end of the line. Appreciate the help!

Collectives™ on Stack Overflow

Postgresql row count not working accurately

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related