Обсуждение: Use of '&' as table prefix in query
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;
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?
TIA,
Rich
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