pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to query by column names
12+ messages / 4 participants
[nested] [flat]
* How to query by column names
@ 2007-01-22 22:25 Richard Ray <rray@mstc.state.ms.us>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Ray @ 2007-01-22 22:25 UTC (permalink / raw)
To: pgsql-sql
This may be a simple but can I create a query such as
select (select attname from pg_attribute where attrelid = (select
relfilenode from pg_class where relname = 't1') and attisdropped =
false and attnum > 0) from t1;
I get
ERROR: more than one row returned by a subquery used as an expression
Thanks
Richard
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-22 23:29 Jeff Frost <jeff@frostconsultingllc.com>
parent: Richard Ray <rray@mstc.state.ms.us>
0 siblings, 1 reply; 12+ messages in thread
From: Jeff Frost @ 2007-01-22 23:29 UTC (permalink / raw)
To: Richard Ray <rray@mstc.state.ms.us>; +Cc: pgsql-sql
I think this is what you're looking for Richard:
SELECT attname FROM pg_attribute pa, pg_class pc
WHERE pc.relname = 't1'
AND pa.attrelid = pc.relfilenode
AND pa.attisdropped IS FALSE
AND pa.attnum > 0;
Let me know if it doesn't do what you intended.
On Mon, 22 Jan 2007, Richard Ray wrote:
> This may be a simple but can I create a query such as
>
> select (select attname from pg_attribute where attrelid = (select relfilenode
> from pg_class where relname = 't1') and attisdropped = false and attnum > 0)
> from t1;
>
> I get
> ERROR: more than one row returned by a subquery used as an expression
>
> Thanks
> Richard
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-22 23:58 Richard Ray <rray@mstc.state.ms.us>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Ray @ 2007-01-22 23:58 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
This is not exactly what I need
I want to return the data in t1
On Mon, 22 Jan 2007, Jeff Frost wrote:
> I think this is what you're looking for Richard:
>
> SELECT attname FROM pg_attribute pa, pg_class pc
> WHERE pc.relname = 't1'
> AND pa.attrelid = pc.relfilenode
> AND pa.attisdropped IS FALSE
> AND pa.attnum > 0;
>
> Let me know if it doesn't do what you intended.
>
> On Mon, 22 Jan 2007, Richard Ray wrote:
>
>> This may be a simple but can I create a query such as
>>
>> select (select attname from pg_attribute where attrelid = (select
>> relfilenode from pg_class where relname = 't1') and attisdropped = false
>> and attnum > 0) from t1;
>>
>> I get
>> ERROR: more than one row returned by a subquery used as an expression
>>
>> Thanks
>> Richard
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>
>>
>
> --
> Jeff Frost, Owner <jeff@frostconsultingllc.com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-23 00:20 Jeff Frost <jeff@frostconsultingllc.com>
parent: Richard Ray <rray@mstc.state.ms.us>
0 siblings, 1 reply; 12+ messages in thread
From: Jeff Frost @ 2007-01-23 00:20 UTC (permalink / raw)
To: Richard Ray <rray@mstc.state.ms.us>; +Cc: pgsql-sql
Perhaps I should have asked this earlier. What information are you trying to
extract?
On Mon, 22 Jan 2007, Richard Ray wrote:
> This is not exactly what I need
> I want to return the data in t1
>
> On Mon, 22 Jan 2007, Jeff Frost wrote:
>
>> I think this is what you're looking for Richard:
>>
>> SELECT attname FROM pg_attribute pa, pg_class pc
>> WHERE pc.relname = 't1'
>> AND pa.attrelid = pc.relfilenode
>> AND pa.attisdropped IS FALSE
>> AND pa.attnum > 0;
>>
>> Let me know if it doesn't do what you intended.
>>
>> On Mon, 22 Jan 2007, Richard Ray wrote:
>>
>>> This may be a simple but can I create a query such as
>>>
>>> select (select attname from pg_attribute where attrelid = (select
>>> relfilenode from pg_class where relname = 't1') and attisdropped = false
>>> and attnum > 0) from t1;
>>>
>>> I get
>>> ERROR: more than one row returned by a subquery used as an expression
>>>
>>> Thanks
>>> Richard
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 5: don't forget to increase your free space map settings
>>>
>>>
>>
>> --
>> Jeff Frost, Owner <jeff@frostconsultingllc.com>
>> Frost Consulting, LLC http://www.frostconsultingllc.com/
>> Phone: 650-780-7908 FAX: 650-649-1954
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that your
>> message can get through to the mailing list cleanly
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-23 00:30 Richard Ray <rray@mstc.state.ms.us>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Ray @ 2007-01-23 00:30 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
All attributes of t1
Where (select attname from pg_attribute where attrelid = (select
relfilenode from pg_class where relname = 't1') and attisdropped = false and attnum > 0)
is a substitute for *
On Mon, 22 Jan 2007, Jeff Frost wrote:
> Perhaps I should have asked this earlier. What information are you trying to
> extract?
>
> On Mon, 22 Jan 2007, Richard Ray wrote:
>
>> This is not exactly what I need
>> I want to return the data in t1
>>
>> On Mon, 22 Jan 2007, Jeff Frost wrote:
>>
>>> I think this is what you're looking for Richard:
>>>
>>> SELECT attname FROM pg_attribute pa, pg_class pc
>>> WHERE pc.relname = 't1'
>>> AND pa.attrelid = pc.relfilenode
>>> AND pa.attisdropped IS FALSE
>>> AND pa.attnum > 0;
>>>
>>> Let me know if it doesn't do what you intended.
>>>
>>> On Mon, 22 Jan 2007, Richard Ray wrote:
>>>
>>>> This may be a simple but can I create a query such as
>>>>
>>>> select (select attname from pg_attribute where attrelid = (select
>>>> relfilenode from pg_class where relname = 't1') and attisdropped = false
>>>> and attnum > 0) from t1;
>>>>
>>>> I get
>>>> ERROR: more than one row returned by a subquery used as an expression
>>>>
>>>> Thanks
>>>> Richard
>>>>
>>>> ---------------------------(end of broadcast)---------------------------
>>>> TIP 5: don't forget to increase your free space map settings
>>>>
>>>>
>>>
>>> --
>>> Jeff Frost, Owner <jeff@frostconsultingllc.com>
>>> Frost Consulting, LLC http://www.frostconsultingllc.com/
>>> Phone: 650-780-7908 FAX: 650-649-1954
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>> subscribe-nomail command to majordomo@postgresql.org so that your
>>> message can get through to the mailing list cleanly
>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>>
>
> --
> Jeff Frost, Owner <jeff@frostconsultingllc.com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-23 00:36 Jeff Frost <jeff@frostconsultingllc.com>
parent: Richard Ray <rray@mstc.state.ms.us>
0 siblings, 1 reply; 12+ messages in thread
From: Jeff Frost @ 2007-01-23 00:36 UTC (permalink / raw)
To: Richard Ray <rray@mstc.state.ms.us>; +Cc: pgsql-sql
So why are you avoiding "SELECT * FROM t1;" ?
You'd probably also be happier using information_schema to get the column
names.
On Mon, 22 Jan 2007, Richard Ray wrote:
> All attributes of t1
> Where (select attname from pg_attribute where attrelid = (select relfilenode
> from pg_class where relname = 't1') and attisdropped = false and attnum > 0)
> is a substitute for *
>
> On Mon, 22 Jan 2007, Jeff Frost wrote:
>
>> Perhaps I should have asked this earlier. What information are you trying
>> to extract?
>>
>> On Mon, 22 Jan 2007, Richard Ray wrote:
>>
>>> This is not exactly what I need
>>> I want to return the data in t1
>>>
>>> On Mon, 22 Jan 2007, Jeff Frost wrote:
>>>
>>>> I think this is what you're looking for Richard:
>>>>
>>>> SELECT attname FROM pg_attribute pa, pg_class pc
>>>> WHERE pc.relname = 't1'
>>>> AND pa.attrelid = pc.relfilenode
>>>> AND pa.attisdropped IS FALSE
>>>> AND pa.attnum > 0;
>>>>
>>>> Let me know if it doesn't do what you intended.
>>>>
>>>> On Mon, 22 Jan 2007, Richard Ray wrote:
>>>>
>>>>> This may be a simple but can I create a query such as
>>>>>
>>>>> select (select attname from pg_attribute where attrelid = (select
>>>>> relfilenode from pg_class where relname = 't1') and attisdropped = false
>>>>> and attnum > 0) from t1;
>>>>>
>>>>> I get
>>>>> ERROR: more than one row returned by a subquery used as an expression
>>>>>
>>>>> Thanks
>>>>> Richard
>>>>>
>>>>> ---------------------------(end of broadcast)---------------------------
>>>>> TIP 5: don't forget to increase your free space map settings
>>>>>
>>>>>
>>>>
>>>> --
>>>> Jeff Frost, Owner <jeff@frostconsultingllc.com>
>>>> Frost Consulting, LLC http://www.frostconsultingllc.com/
>>>> Phone: 650-780-7908 FAX: 650-649-1954
>>>>
>>>> ---------------------------(end of broadcast)---------------------------
>>>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>>> subscribe-nomail command to majordomo@postgresql.org so that your
>>>> message can get through to the mailing list cleanly
>>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>> choose an index scan if your joining column's datatypes do not
>>> match
>>>
>>>
>>
>> --
>> Jeff Frost, Owner <jeff@frostconsultingllc.com>
>> Frost Consulting, LLC http://www.frostconsultingllc.com/
>> Phone: 650-780-7908 FAX: 650-649-1954
>>
>
>
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-23 01:25 Richard Ray <rray@mstc.state.ms.us>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 12+ messages in thread
From: Richard Ray @ 2007-01-23 01:25 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
On Mon, 22 Jan 2007, Jeff Frost wrote:
> So why are you avoiding "SELECT * FROM t1;" ?
>
I was affeared that if I brought my total ignorance to light I would be
band from the list but here goes.
I work in UNIX/Linux environments.
It's my habit to record my scripts.
A simple example:
#!/bin/bash
CMD="psql -d test \"select * from t1\""
echo $CMD >> my_log
eval $CMD |
while read x; do
do_something_with_x
done
In this example * expands to all files in the current working directory.
I was attempting to get around this by enumerating the table attributes.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-23 01:32 Jeff Frost <jeff@frostconsultingllc.com>
parent: Richard Ray <rray@mstc.state.ms.us>
0 siblings, 1 reply; 12+ messages in thread
From: Jeff Frost @ 2007-01-23 01:32 UTC (permalink / raw)
To: Richard Ray <rray@mstc.state.ms.us>; +Cc: pgsql-sql
On Mon, 22 Jan 2007, Richard Ray wrote:
> On Mon, 22 Jan 2007, Jeff Frost wrote:
>
>> So why are you avoiding "SELECT * FROM t1;" ?
>>
> I was affeared that if I brought my total ignorance to light I would be band
> from the list but here goes.
> I work in UNIX/Linux environments.
> It's my habit to record my scripts.
> A simple example:
>
> #!/bin/bash
> CMD="psql -d test \"select * from t1\""
> echo $CMD >> my_log
> eval $CMD |
> while read x; do
> do_something_with_x
> done
>
> In this example * expands to all files in the current working directory.
> I was attempting to get around this by enumerating the table attributes.
Oh! Why didn't you just say that in the first place. You just need quotes.
Try this:
psql -c 'select * from t1' test
or
psql -c "select * from t1" test
or
echo "select * from t1" | psql test
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-23 05:03 Josh Williams <drykath@runbox.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 12+ messages in thread
From: Josh Williams @ 2007-01-23 05:03 UTC (permalink / raw)
To: jeff@frostconsultingllc.com; +Cc: rray@mstc.state.ms.us; pgsql-sql
From: Jeff Frost <jeff@frostconsultingllc.com>
> On Mon, 22 Jan 2007, Richard Ray wrote:
...
> > #!/bin/bash
> > CMD="psql -d test \"select * from t1\""
> > echo $CMD >> my_log
> > eval $CMD |
> > while read x; do
> > do_something_with_x
> > done
> >
> > In this example * expands to all files in the current working directory.
> > I was attempting to get around this by enumerating the table attributes.
>
> Oh! Why didn't you just say that in the first place. You just need quotes.
...
That's definitely part of it. I'm assuming the above is an abridged example and the OP is doing something dynamic with the query. The real trouble is Bash likes to expand the asterisk into a list of every file in the current directory when you try to push the command through a variable. So it's just a matter of finding a way to escape the * character to keep Bash from globbing, which unfortunately right now is escaping me (no pun intended.)
Two reasonable workarounds come to mind:
1. Turn off Bash's pathname expansion: #!/bin/bash -f
This will of course disable it script-wide, and thus will break any place you actually are trying to use this feature, if at all.
2. Don't put an * in the variable.
If all you're really doing is replacing the table name then only stick that into a variable, say tablename, and directly execute the rest:
psql -d test -c "SELECT * FROM $tablename" | while etc
Worst case, you'll end up with a messy $leftside and $rightside variable set.
To answer the original question, the field must be hard coded either as a list or that perhaps over-used(?) asterisk. If you really need to pull and use that from the table definition you'll need two round trips to the server.
Best of luck,
- Josh Williams
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-23 05:29 Jeff Frost <jeff@frostconsultingllc.com>
parent: Josh Williams <drykath@runbox.com>
0 siblings, 1 reply; 12+ messages in thread
From: Jeff Frost @ 2007-01-23 05:29 UTC (permalink / raw)
To: Josh Williams <drykath@runbox.com>; +Cc: rray@mstc.state.ms.us; pgsql-sql
On Tue, 23 Jan 2007, Josh Williams wrote:
> From: Jeff Frost <jeff@frostconsultingllc.com>
>> On Mon, 22 Jan 2007, Richard Ray wrote:
> ...
>
> That's definitely part of it. I'm assuming the above is an abridged example
> and the OP is doing something dynamic with the query. The real trouble is
> Bash likes to expand the asterisk into a list of every file in the current
> directory when you try to push the command through a variable. So it's just
> a matter of finding a way to escape the * character to keep Bash from
> globbing, which unfortunately right now is escaping me (no pun intended.)
Oh...you just need to put quotes around the variable like this:
#!/bin/bash
CMD="psql -c 'select * from products;' jefftest"
echo "$CMD" >> my_log
eval "$CMD" |
while read x; do
echo $x
done
discord:~ $ /tmp/test.sh
productid | name | price
-----------+-----------+-------
1 | Notepad | 1.99
3 | Legal Pad | 2.99
(2 rows)
discord:~ $ cat my_log
psql -c 'select * from products;' jefftest
At any rate, if that's still a problem, you can turn off globbing, do what
needs doing, then turn on globbing like the following:
#!/bin/bash
#
# Turn off globbing
#
set -o noglob
# ...
# Do your * laden work here
# ...
# Turn on globbing
set +o noglob
>
> Two reasonable workarounds come to mind:
> 1. Turn off Bash's pathname expansion: #!/bin/bash -f
> This will of course disable it script-wide, and thus will break any place you actually are trying to use this feature, if at all.
>
> 2. Don't put an * in the variable.
> If all you're really doing is replacing the table name then only stick that into a variable, say tablename, and directly execute the rest:
> psql -d test -c "SELECT * FROM $tablename" | while etc
> Worst case, you'll end up with a messy $leftside and $rightside variable set.
>
> To answer the original question, the field must be hard coded either as a list or that perhaps over-used(?) asterisk. If you really need to pull and use that from the table definition you'll need two round trips to the server.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-23 11:56 John Summerfield <postgres@herakles.homelinux.org>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 12+ messages in thread
From: John Summerfield @ 2007-01-23 11:56 UTC (permalink / raw)
To: pgsql-sql
Jeff Frost wrote:
> On Tue, 23 Jan 2007, Josh Williams wrote:
>
>> From: Jeff Frost <jeff@frostconsultingllc.com>
>>> On Mon, 22 Jan 2007, Richard Ray wrote:
>> ...
>>
>> That's definitely part of it. I'm assuming the above is an abridged
>> example and the OP is doing something dynamic with the query. The
>> real trouble is Bash likes to expand the asterisk into a list of every
>> file in the current directory when you try to push the command through
>> a variable. So it's just a matter of finding a way to escape the *
Oh, good grief Charlie Brown:
backslash it:
\*
so:
echo select \* from whatsit | psql test
>> character to keep Bash from globbing, which unfortunately right now is
>> escaping me (no pun intended.)
--
Off-list replies unwelcome
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: How to query by column names
@ 2007-01-23 12:01 John Summerfield <postgres@herakles.homelinux.org>
parent: John Summerfield <postgres@herakles.homelinux.org>
0 siblings, 0 replies; 12+ messages in thread
From: John Summerfield @ 2007-01-23 12:01 UTC (permalink / raw)
To: pgsql-sql
John Summerfield wrote:
> Jeff Frost wrote:
>> On Tue, 23 Jan 2007, Josh Williams wrote:
>>
>>> From: Jeff Frost <jeff@frostconsultingllc.com>
>>>> On Mon, 22 Jan 2007, Richard Ray wrote:
>>> ...
>>>
>>> That's definitely part of it. I'm assuming the above is an abridged
>>> example and the OP is doing something dynamic with the query. The
>>> real trouble is Bash likes to expand the asterisk into a list of
>>> every file in the current directory when you try to push the command
>>> through a variable. So it's just a matter of finding a way to escape
>>> the *
>
> Oh, good grief Charlie Brown:
>
> backslash it:
> \*
>
> so:
> echo select \* from whatsit | psql test
Oh
echo select \* from whatsit \; | psql test
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2007-01-23 12:01 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-22 22:25 How to query by column names Richard Ray <rray@mstc.state.ms.us>
2007-01-22 23:29 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-01-22 23:58 ` Richard Ray <rray@mstc.state.ms.us>
2007-01-23 00:20 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-01-23 00:30 ` Richard Ray <rray@mstc.state.ms.us>
2007-01-23 00:36 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-01-23 01:25 ` Richard Ray <rray@mstc.state.ms.us>
2007-01-23 01:32 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-01-23 05:03 ` Josh Williams <drykath@runbox.com>
2007-01-23 05:29 ` Jeff Frost <jeff@frostconsultingllc.com>
2007-01-23 11:56 ` John Summerfield <postgres@herakles.homelinux.org>
2007-01-23 12:01 ` John Summerfield <postgres@herakles.homelinux.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox