Display of text fields

[Ennio-Sr]

[Sorry for possible pluri-repeat: I'm not putting up very well with the
technicalities of ... your Majordomo ... -:)]

Hi all!

[Using PG 7.2.1 on Debian/Woody, k. 2.2.22]

After a very long struggle I finally succeded in transferring my old
*.dbf file and the relating *.dbt (alias memo fields) to a pg table.
For the time being I put the memo field in a separate table having two
fields only (i.e.: n_memo integer, memo text) which can be related to
the main table in a view.

Now, the first problem is that when I do a select * from this table it
will scroll up to the end, no matter whether \x or \t or \pset pager are
set or not! [For information: if I put the text fields in the main
ex.dbf table - which has more columns - or do a select from a view
connecting the 2 tables the page control works fine].

Second problem: the display of each text field looks like this:

# ---------- quote ---------
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
col1 | Anatomical Diagrams for the use of the Art Students Arranged
with analytical Notes and drawn out by James M. Dunlop, A.R.C.A.,
...... [ cut ] ....

[Example taken from a single column table, but the result is the same
for the 2 cols table]

# ----------- unquote --------

My questions are then:

1. Is there a way to avoid displaying the '---------' lines, apart from
the \a flag ?
2. In case I put the memos in the main table, would it be possible to
control someway the display of memo-text fields? [Only some of the
records have a memo, and a referring col set to 'T', so I'm looking
for a selective instruction, such as: if ctl_memo='T' display memo,
else, display the other cols only].
3. Does the pager work better on version 7.4.3?

I realize that the version (7.2.1) I'm working with is old as compared
to the running 8.0, so your help will be particularly appreciated.
Regards,
Ennio.
--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Stephan Szabo]

On Fri, 10 Sep 2004, Ennio-Sr wrote:

* Stephan Szabo <ss****@megazon e.bigpanda.com> [100904, 07:10]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:

I slightly modified your queries and the result gets nearer my goals,
but ...
Here is what I tried:
[ ... ]
As an explanation of the duplicate rows:

FROM bib_lt t0, bibbt t1 with no WHERE condition that constrains the join
[ ... ]
If you're not using any other fields from t1, I would wonder if something
like:

SELECT t0.n_prog, ..., t0.scheda_ltr, coalesce(t1.not e, 'n/a') as note
FROM bib_lt t0 left outer join t1 on (t0.scheda_ltr= 'T' and
t0.n_prog=t1.n_ prog) where t0._nprog<>0;

would be closer to what you want from the query. The join should give
output with either t0 extended by NULLs or t0 joined by t1 dependant on
whether t0.scheda_ltr=' T' and if it finds a matching row in t1.

Thank you Stephen, for your contribution: I'll study it in due course
... as I'm interested to learn as much as possible ...
However, in the meantime, I think I found the solution. What helped me
was the construction of these two testing tables:

Table "foo"
Column | Type | Modifiers
--------+-------------------+-----------
a | integer |
b | character varying |
c | character varying |
has_d | character(1) |

# which I filled with:

a | b | c | has_d
---+------+--------+-------
1 | one | number | Y
2 | two | number | Y
3 | tree | name | Y
4 | blue | color | N
5 | john | person | N
(5 rows)

# and:

Table "foo_d"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
d | text |

# bearing my 'would-be' memo field:

a | d
---+----------------------------------
1 | is the first natural
2 | follows 1 in the seq of natural
3 | there are various qualities of -
(3 rows)

# Then I launched an 'nth' variant of my query:

SELECT DISTINCT
--- t0.a, t0.b, t0.c, t0.has_d, -- t1.d,
-- t0.has_d, -- ## ok, mostr prima i due 'N' e poi due volte quelli Y
-- ## se tolgo 't0.has_d', cambia ordine ma sempre 8
-- ## sono
CASE
WHEN t0.has_d = 'Y' AND t0.a=t1.a
THEN t0.a || ' - ' || t0.b || ' - ' || t0.c || ' - ' || t1.d
ELSE
CASE
WHEN t0.has_d = 'N'
THEN t0.a || ' / ' || t0.b || ' / ' || t0.c -- || ' / ' || t0.has_d
END
END AS "The result is: "
FROM foo t0, foo_d t1;

Note however, that this may very well perform poorly compared to other
solutions because as foo and foo_d get large, you're going to be
evaluating the case clause alot. In addition, this gives an extra NULL
row AFAICS (see below where you get a "blank" row and the rowcount is 1
higher than the meaningful number of rows.
The result is:
----------------------------------------------------
1 - one - number - is the first natural
2 - two - number - follows 1 in the seq of natural
3 - tree - name - there are various qualities of -
4 / blue / color
5 / john / person

(6 rows)

# which is exaclty what I was looking for :-)))))

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ennio-Sr]

* Stephan Szabo <ss****@megazon e.bigpanda.com> [100904, 09:05]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:

* Stephan Szabo <ss****@megazon e.bigpanda.com> [100904, 07:10]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:
[ big cut ]

Note however, that this may very well perform poorly compared to other
solutions because as foo and foo_d get large, you're going to be
evaluating the case clause alot. In addition, this gives an extra NULL
row AFAICS (see below where you get a "blank" row and the rowcount is 1
higher than the meaningful number of rows.

Stephan,
I just tested my query on the main tables (bibl_lt and bidbt) and it
seems to work reasonably quickly (my tables are not all that large:
around 10.000 rows only!). But, if it is possible to get a better
result, why not?
So, when you say '..compared to other solutions..' are you thinking
about 'COALESCE' (which I have not studied yet) or some other type
of instruction, other than psql's?
TIA,
Ennio

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

On Fri, 10 Sep 2004, Ennio-Sr wrote:

* Stephan Szabo <ss****@megazon e.bigpanda.com> [100904, 09:05]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:

* Stephan Szabo <ss****@megazon e.bigpanda.com> [100904, 07:10]:
> On Fri, 10 Sep 2004, Ennio-Sr wrote:
> [ big cut ]

Note however, that this may very well perform poorly compared to other
solutions because as foo and foo_d get large, you're going to be
evaluating the case clause alot. In addition, this gives an extra NULL
row AFAICS (see below where you get a "blank" row and the rowcount is 1
higher than the meaningful number of rows.

Stephan,
I just tested my query on the main tables (bibl_lt and bidbt) and it
seems to work reasonably quickly (my tables are not all that large:
around 10.000 rows only!). But, if it is possible to get a better
result, why not?
So, when you say '..compared to other solutions..' are you thinking
about 'COALESCE' (which I have not studied yet) or some other type
of instruction, other than psql's?

Well, I'd expect that for large tables the outer join type solution would
tend to be faster than joining every row to every other row and then using
a unique step (probably after a sort) to basically remove the ones you
don't want. If you try different solutions, you can use EXPLAIN ANALYZE to
compare query plans.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ennio-Sr]

* Stephan Szabo <ss****@megazon e.bigpanda.com> [100904, 10:39]:

On Fri, 10 Sep 2004, Ennio-Sr wrote:

> * Stephan Szabo <ss****@megazon e.bigpanda.com> [100904, 07:10]:
> > On Fri, 10 Sep 2004, Ennio-Sr wrote:
> > [ big cut ]

Well, I'd expect that for large tables the outer join type solution would
tend to be faster than joining every row to every other row and then using
a unique step (probably after a sort) to basically remove the ones you
don't want. If you try different solutions, you can use EXPLAIN ANALYZE to
compare query plans.

Please forget my previous message: I've just finished trying your
'COALESCE' solution (prior to studying it ;) ) and it seems to work
greatly!

---------
SELECT
t0.a, t0.b, t0.c, COALESCE(t1.d, ' ') as note from foo t0 left OUTER
JOIN foo_d t1 on (t0.has_d = 'Y' AND t0.a=t1.a);

# the result is:

a | b | c | note
---+------+--------+----------------------------------
1 | one | number | is the first natural
2 | two | number | follows 1 in the seq of natural
3 | tree | name | there are various qualities of -
4 | blue | color |
5 | john | person |
(5 rows)
^^^
---------
Perfect, I would say :-)
Thank you again so much indeed, Stephan.
Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that. )=(
Do something you aren't good at!" (used to say Henry Miller) ]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster