Incomplete Information from Query on Pg_Views

incomplete information from query on pg_views

You want pg_get_viewdef, but I suspect you'll have the same issue there. The problem is probably that the client application is truncating the returned query.

If you're using PgAdmin-III this is in the FAQ.

If you're using psql this shouldn't happen.

pgAdmin III faulty behavior?

You cannot reproduce the same effect on SQL Fiddle.

I recreated your table in Postgres 9.1.13 (always upgrade to the latest point release!) and ran the queries in pgAdmin (current version 1.18.1). I cannot reproduce the problem.

pgAdmin?

I don't see how pgAdmin could play a role in this - unless you have been selecting only a part of your query, unaware of this effect:

pgAdmin shortcuts to execute scripts

Or you might be fooled by the "Max. characters per column" setting, which truncates long values in the display, hiding the match in the truncated part, like @IMSoP suggested in his comment. Check File -> Options ...

If that's not it and unless we are dealing with typos or circumstances not in your question, this would indicate something is broken in your database.

Corruption?

In simple cases with just a corrupted index, a REINDEX TABLE might do the trick:

REINDEX TABLE messages;

However, on a closer look, I don't see an index that could possibly be the culprit here.

Corrupted system catalog? Read this first:

http://wiki.postgresql.org/wiki/Corruption

Then read the Notes section for REINDEX and run from the shell:

$ export PGOPTIONS="-P"
$ psql broken_db
...
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q

Corruption often indicates a problem with your hardware. A failing disk or something. Follow up on that ...

Related question:

Repair Corrupt database postgresql

INSERT INTO string has to many characters, how can I bypass it? Postgresql

I suppose you are using pgAdmin3 to run your query and copy from result grid, right?

Go to "File -> Options -> Query tool -> Max. characters per column " and set it to 1000000000.

pgAdmin: How to see complete value in a cell in output

There is a setting for that in the options: Max characters per column - useful when dealing with big columns.

Details:

• Why are query results shortened in pgAdmin 1.18.1?

More in the manual here.

Is there a maximum length when storing into PostgreSQL TEXT

There were some justified remarks about the lack of examples. While playing with the examples I stumbled upon some remarkable behavior of pgAdmin: it does not display the information which is present. Just select some rows in the output from the pgAdmin data editor which is shown in the original question and paste it in an editor. It then does display the information which is not visible in the data display of pgAdmin, see below.

3;0;"Appingedam";"GM";2014;53.3172;6.84957;"53.345131 6.885408  53.344925 6.885422  ...
5;0;"Bedum";"GM";2014;53.2949;6.59717;"53.255638 6.587573  53.255646 6.587514  53.25...
7;0;"Bellingwedde";"GM";2014;53.0986;7.12118;"53.097076 7.038233  53.097099 7.038254...
9;0;"Ten Boer";"GM";2014;53.2788;6.69145;"53.312183 6.740470  53.312176 6.740486  53...
10;0;"Delfzijl";"GM";2014;53.3227;7.09945;"53.322491 7.100943  53.322414 7.100940  5...
14;0;"Groningen";"GM";2014;53.2222;6.5634;"53.198223 6.515755  53.198238 6.515728  5...
15;0;"Grootegast";"GM";2014;53.2086;6.27322;"53.203838 6.318381  53.203819 6.318180 ...
17;0;"Haren";"GM";2014;53.1545;6.63106;"53.165165 6.560897  53.165207 6.560884  53.1...
18;0;"Hoogezand-Sappemeer";"GM";2014;53.1473;6.7468;"53.116516 6.743022  53.116371 6...
22;0;"Leek";"GM";2014;53.1633;6.36926;"53.211250 6.362241  53.211262 6.362327  53.21...

So it seems that pgAdmin behaves like what you don't see is what you don't get. Java, jdbc and Postgres behave as expected, pgAdmin does not.

---

Related Topics