Use of '&' as table prefix in query

Rich Shepard <rshepard@appl-ecosys.com> writes:

Long ago I wrote a query which was greatly improved (i.e., it actually
worked as intended) by help here):

SELECT ...
WHERE p.lname = &p.lname AND p.fname = &p.fname;

I did not save the reason why the ampersand is used in the WHERE row selection
phrase and want now to learn why it's there. Probably needed to concatenate
separate names?

AFAICS this is invoking a prefix operator named "&". There is no such
operator built into Postgres. Maybe psql's "\do+ &" would jog your
memory about where yours came from.

regards, tom lane

On 7/12/21 11:25 AM, Rich Shepard wrote:

Long ago I wrote a query which was greatly improved (i.e., it actually
worked as intended) by help here):

/* This query selects all activity information for a named person */

SELECT p.lname, p.fname, p.loc_nbr, p.job_title, p.direct_phone,
p.active,
       o.org_name,
       l.loc_nbr, l.loc_name,
       a.act_date, a.act_type, a.notes, a.next_contact
FROM People AS p
     JOIN Organizations AS o ON o.org_nbr = p.org_nbr
     JOIN Locations AS l ON l.org_nbr = o.org_nbr and l.loc_nbr =
p.loc_nbr
     JOIN Activities AS a ON a.person_nbr = p.person_nbr
WHERE p.lname = &p.lname AND p.fname = &p.fname;

These look like value substitutions, usually done on the client at it
sends the sql.  How is this sql getting to the server (presumably after
substitution).

On Mon, 12 Jul 2021, Tom Lane wrote:

AFAICS this is invoking a prefix operator named "&". There is no such
operator built into Postgres. Maybe psql's "\do+ &" would jog your memory
about where yours came from.

tom,

I thought it wasn't part of postgres. I've no idea why it's there, but I'll
remove it and see what happens.

Thanks!

Rich

On Mon, 12 Jul 2021, Rob Sargent wrote:

These look like value substitutions, usually done on the client at it
sends the sql. How is this sql getting to the server (presumably after
substitution).

Rob,

I was running queries from the psql back then. Now I'm adding a GUI
(tkinter) and using psycopg2.

Thanks,

Rich

On 7/12/21 10:45 AM, Rich Shepard wrote:

On Mon, 12 Jul 2021, Tom Lane wrote:

AFAICS this is invoking a prefix operator named "&". There is no such
operator built into Postgres. Maybe psql's "\do+ &" would jog your memory
about where yours came from.

tom,

I thought it wasn't part of postgres. I've no idea why it's there, but I'll
remove it and see what happens.

Before you do that see Rob's post.

Thanks!

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/12/21 10:45 AM, Rich Shepard wrote:

On Mon, 12 Jul 2021, Tom Lane wrote:

AFAICS this is invoking a prefix operator named "&". There is no such
operator built into Postgres. Maybe psql's "\do+ &" would jog your memory
about where yours came from.

tom,

I thought it wasn't part of postgres. I've no idea why it's there, but I'll
remove it and see what happens.

You should also follow Tom's suggestion and do:

\do+ &

in psql.

Thanks!

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

On 7/12/21 11:47 AM, Rich Shepard wrote:

On Mon, 12 Jul 2021, Rob Sargent wrote:

These look like value substitutions, usually done on the client at it
sends the sql. How is this sql getting to the server (presumably after
substitution).

Rob,

I was running queries from the psql back then. Now I'm adding a GUI
(tkinter) and using psycopg2.

Thanks,

Rich

Having a hard time seeing the value in p.lname = p.lname and straight sql.

On Mon, 12 Jul 2021, Adrian Klaver wrote:

You should also follow Tom's suggestion and do:
\do+ &
in psql.

It's the bitwise 'and':
# \do+ &
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Function | Description
------------+------+---------------+----------------+-------------+--------------+-------------
pg_catalog | & | bigint | bigint | bigint | int8and | bitwise and
pg_catalog | & | bit | bit | bit | bitand | bitwise and
pg_catalog | & | inet | inet | inet | inetand | bitwise and
pg_catalog | & | integer | integer | integer | int4and | bitwise and
pg_catalog | & | macaddr | macaddr | macaddr | macaddr_and | bitwise and
pg_catalog | & | macaddr8 | macaddr8 | macaddr8 | macaddr8_and | bitwise and
pg_catalog | & | smallint | smallint | smallint | int2and | bitwise and
(7 rows)

which doesn't seem appropriate in this context.

Thanks,

Rich

On Mon, 12 Jul 2021, Rob Sargent wrote:

Having a hard time seeing the value in p.lname = p.lname and straight sql.

Me, too. That's why I wanted an explanation. Seems to me I added it sometime
for no valid reason. As I've not run that query in a very long time I'll
clean up the query and test it with the next opportunity.

Rich

On Mon, 12 Jul 2021, Rich Shepard wrote:

Long ago I wrote a query which was greatly improved (i.e., it actually
worked as intended) by help here):

Ah! It finally came back to me as I looked to revise it. What I want both
&p.lname and &p.fname are specific names passed in from the user. Now I know
what they represent I'll find just how to specify them using python and
psycopg2.

Rich