[GENERAL] Query Problem
Hi Fellows
I still have problem to access my databank.It shows syntax problem,I am using
VC++ 2005 with postgresql 8.3.My table name is Person and it has three
column,Person ID,first name and last name (testing version).whole c++ code is
// Test_postgres.cpp : Defines the entry point for the console application.
//
#include stdafx.h
#include libpq-fe.h
int _tmain(int argc, _TCHAR* argv[])
{
const char *conninfo;
PGconn *conn;
const char *paramtext = server_version;
PGresult *res;
conninfo = hostaddr = 127.0.0.1 dbname = Salman_db user = postgres password =
732047;
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK)
{
printf(Unable to establish connection: %s,
PQerrorMessage(conn));
return 1;
} else
{
res = PQexec(conn, INSERT INTO public.Person VALUES (221,'Siddiqi','Umer'));
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
printf(Problem with command: %s\n, PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return 1;
}
PQclear(res);
}
PQfinish(conn);
return 0;
}
It shows no relation between public and person,if i write only person ,it show
,Person doesn't exist.
hope, u ppl can help me.
Sheikh Salman AhmedDiplomand in Nano Struktur TechnologieDuisburg-Essen
UniversitätMobile : 0049-179-9463738Home :0049-203-3945028
_
Invite your mail contacts to join your friends list with Windows Live Spaces.
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=createwx_url=/friends.aspxmkt=en-us
Re: [GENERAL] Query Problem
On Sun, Jul 6, 2008 at 12:04 AM, Sheikh Salman Ahmed [EMAIL PROTECTED] wrote: res = PQexec(conn, INSERT INTO public.Person VALUES Without quotes around Person, it will be shifted to lower case to match a table called person. I suspect you need to do: res = PQexec(conn, INSERT INTO public.\Person\ VALUES It's almost always easier to use lower case names in Postgres. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query Problem
On lau, 2008-07-05 at 23:04 +, Sheikh Salman Ahmed wrote: Hi Fellows I still have problem to access my databank.It shows syntax problem,I am using VC++ 2005 with postgresql 8.3.My table name is Person and it has three column,Person ID,first name and last name (testing version).whole c++ code is more precise schema definition would be more helpful ... res = PQexec(conn, INSERT INTO public.Person VALUES (221,'Siddiqi','Umer')); ... It shows no relation between public and person,if i write only person ,it show ,Person doesn't exist. and real error messages are preferred. As someone already told you a few days ago, the problem could be that the table was created Person (mixed case with double quotes). you have not confirmed or denied this. If that is the case, you need to quote the name in your SQL: INSERT INTO public.Person VALUES (221,'Siddiqi','Umer') (of course, you need to escape those quotes for your c) If this is not your problem, please suply us with more information, and someone may be able to help you. gnari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query problem
On 10/12/07, Clemens Schwaighofer [EMAIL PROTECTED] wrote: I probably need to approach the problem different. So both are read independent or something like this. Also, email_a, email_b, etc. indicate that you need to consider refactoring your schema. You can find a lot of tutorials on normalization online. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Query problem
2007/10/12, Clemens Schwaighofer [EMAIL PROTECTED]:
hi,
thats the first time I am a bit confused by a query not working.
I have this table:
gullevek=# \d test
Table public.test
Column | Type| Modifiers
-+---+
test_id | integer | not null default
nextval('test_test_id_seq'::regclass)
email_a | character varying |
email_b | character varying |
Indexes:
test_pkey PRIMARY KEY, btree (test_id)
with this content:
gullevek=# select * from test;
test_id |email_a| email_b
-+---+-
2 | [EMAIL PROTECTED] | [EMAIL PROTECTED]
1 | [EMAIL PROTECTED] |
(2 rows)
if I do this select:
select * from (select test_id, email_a FROM test WHERE email_a =
'[EMAIL PROTECTED]') as s, (select test_id, email_b from test where email_b =
'[EMAIL PROTECTED]') as t;
you get 0 rows. [EMAIL PROTECTED] isn't anywhere and [EMAIL PROTECTED] cannot do
pair with any. else 0 * 1 = 0
Pavel
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] Query problem
hi,
thats the first time I am a bit confused by a query not working.
I have this table:
gullevek=# \d test
Table public.test
Column | Type| Modifiers
-+---+
test_id | integer | not null default
nextval('test_test_id_seq'::regclass)
email_a | character varying |
email_b | character varying |
Indexes:
test_pkey PRIMARY KEY, btree (test_id)
with this content:
gullevek=# select * from test;
test_id |email_a| email_b
-+---+-
2 | [EMAIL PROTECTED] | [EMAIL PROTECTED]
1 | [EMAIL PROTECTED] |
(2 rows)
if I do this select:
select * from (select test_id, email_a FROM test WHERE email_a =
'[EMAIL PROTECTED]') as s, (select test_id, email_b from test where email_b =
'[EMAIL PROTECTED]') as t;
I get no rows back. should it return something? subquery a doesn't
return one, because the email does not exist, b returns something.
how do I have to make the query so I get a result back and in one row?
--
[ Clemens Schwaighofer -=:~ ]
[ TEQUILA\ Japan IT Group]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp ]
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Query problem
On 2007/10/12, at 23:22, Pavel Stehule wrote: you get 0 rows. [EMAIL PROTECTED] isn't anywhere and [EMAIL PROTECTED] cannot do pair with any. else 0 * 1 = 0 Thanks, go it now. Basically very simple. I probably need to approach the problem different. So both are read independent or something like this. -- [ Clemens Schwaighofer -=:~ ] [ TEQUILA\ Japan IT Engineer ] [6-17-2 Ginza Chuo-ku, Tokyo 104-0061, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.co.jphttp://www.tbwajapan.co.jp ] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Query problem
Aside from the messy nomenclature, is anyone able to spot why the sum column from the first query is not returning 7, as the second query suggests that it should? I know that this is probably simple, and that It's probably going to jump out at me the minute I hit Send, but if I don't hit send, then I'll never see it hehe. twerl=# SELECT 'contactgroups', contactgroups.siteid, contactgroups.id, contactgroups.name, contactgroups.description, SUM(contacts.id), contactgroups.trashed FROM contactgroups LEFT OUTER JOIN contacts ON (contactgroups.id = contacts.groupid) WHERE contactgroups.trashed IS NOT NULL AND contactgroups.deleted IS NULL GROUP BY contactgroups.siteid, contactgroups.id, contactgroups.name, contactgroups.description, contactgroups.trashed; ?column?| siteid | id |name| description | sum | trashed ---++++-+-+--- contactgroups | 1 | 3 | Telechoice / Optus | | 594 | 2007-10-01 20:08:51.449825+10 (1 row) twerl=# select count(*) from contacts where groupid = 3; count --- 7 (1 row) Thanks, - Naz. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Query problem
Naz Gassiep wrote: Aside from the messy nomenclature, is anyone able to spot why the sum column from the first query is not returning 7, as the second query suggests that it should? I know that this is probably simple, and that It's probably going to jump out at me the minute I hit Send, but if I don't hit send, then I'll never see it hehe. twerl=# SELECT ... SUM(contacts.id) ... FROM ... contacts ... ... | sum | ... ...-+-+-... ... | 594 | ... (1 row) twerl=# select count(*) from contacts where groupid = 3; count --- 7 (1 row) That's an easy one. I assume that there are three rows in table contacts, and the values of the id column are 42, 123 and 429 (or similar). Yours, Laurenz Albe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Query problem...
You may want to take a look at the ltree and tablefunc contrib
modules. They both allow you to do something like this, and the
abstract away the difficulty of query building. ltree will allow you
to precompute the tree, and the tablefunc module has a connectby()
function for runtime parent-child relationship evaluation.
On Sat, 2 Oct 2004 15:12:46 -0700, Net Virtual Mailing Lists
[EMAIL PROTECTED] wrote:
Hello,
I have 3 tables which are joined that I need to create a summation for
and I just cannot get this to work.
Here's an example:
CREATE table1 (
id1INTEGER,
title1 VARCHAR
);
INSERT INTO table1 (1, 'Heading #1');
INSERT INTO table1 (2, 'Heading #2');
CREATE table2 (
id1INTEGER,
id2INTEGER,
title2 VARCHAR
);
INSERT INTO table2 (1, 1, 'Category #1.1');
INSERT INTO table2 (1, 2, 'Category #1.2');
INSERT INTO table2 (2, 1, 'Category #2.1');
INSERT INTO table2 (2, 2, 'Category #2.2');
CREATE table3 (
id1INTEGER,
id2INTEGER,
id3INTEGER,
title3 VARCHAR
);
INSERT INTO table2 (1, 1, 1, 'Sub-Category #1.1.1');
INSERT INTO table2 (1, 1, 2, 'Sub-Category #1.1.2');
INSERT INTO table2 (1, 2, 1, 'Sub-Category #1.2.1');
INSERT INTO table2 (1, 2, 2, 'Sub-Category #1.2.2');
INSERT INTO table2 (2, 1, 1, 'Sub-Category #2.1.1');
INSERT INTO table2 (2, 1, 2, 'Sub-Category #2.1.2');
INSERT INTO table2 (2, 2, 1, 'Sub-Category #2.2.1');
INSERT INTO table2 (2, 2, 2, 'Sub-Category #2.2.2');
What I am trying to represent is some sort of hierarchical data here, for
example:
Heading #1
Category #1.1
Sub-Category #1.1.1
Sub-Category #1.1.2
Cateogry #1.2
Sub-Category #1.2.1
Sub-Category #1.2.2
Heading #2
Category #2.1
Sub-Category #2.2.1
Sub-Category #2.2.2
Cateogry #2.2
Sub-Category #2.2.1
Sub-Category #2.2.2
... I hope that makes sense.. Perhaps I'm going about this the wrong way
to begin with?
In any event, the problem is now I have another table which uses these
tables. For example:
CREATE TABLE blech (
somedata VARCHAR,
id1 INTEGER,
id2 INTEGER,
id3 INTEGER
);
INSERT INTO TABLE blech ('Record #1', 1, 1, 1);
INSERT INTO TABLE blech ('Record #2', 1, 1, 1);
INSERT INTO TABLE blech ('Record #3', 1, 2, 1);
INSERT INTO TABLE blech ('Record #4', 1, 1, 2);
INSERT INTO TABLE blech ('Record #5', 2, 1, 1);
... etc... (NOTE: id1, id2, and id3 cannot be NULL in this table)
What I want is a query that will give me this:
count | id1 | id2 | id3
--
4 | 1| |
3 | 1|1|
1 | 1|1| 1
1 | 1|1| 2
1 | 1|2|
1 | 1|2| 1
1 | 2| |
1 | 2|1|
1 | 2|1| 1
I've tried all manner of LEFT JOINs, GROUP BYs, and even tried using
UNION, but I just can't seem to get the results I need. I'm definitely
not married to this type of schema, if there is a more efficient way of
handling this I'm all for it.
Thanks as always!
- Greg
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Query problem...
Hello,
I have 3 tables which are joined that I need to create a summation for
and I just cannot get this to work.
Here's an example:
CREATE table1 (
id1INTEGER,
title1 VARCHAR
);
INSERT INTO table1 (1, 'Heading #1');
INSERT INTO table1 (2, 'Heading #2');
CREATE table2 (
id1INTEGER,
id2INTEGER,
title2 VARCHAR
);
INSERT INTO table2 (1, 1, 'Category #1.1');
INSERT INTO table2 (1, 2, 'Category #1.2');
INSERT INTO table2 (2, 1, 'Category #2.1');
INSERT INTO table2 (2, 2, 'Category #2.2');
CREATE table3 (
id1INTEGER,
id2INTEGER,
id3INTEGER,
title3 VARCHAR
);
INSERT INTO table2 (1, 1, 1, 'Sub-Category #1.1.1');
INSERT INTO table2 (1, 1, 2, 'Sub-Category #1.1.2');
INSERT INTO table2 (1, 2, 1, 'Sub-Category #1.2.1');
INSERT INTO table2 (1, 2, 2, 'Sub-Category #1.2.2');
INSERT INTO table2 (2, 1, 1, 'Sub-Category #2.1.1');
INSERT INTO table2 (2, 1, 2, 'Sub-Category #2.1.2');
INSERT INTO table2 (2, 2, 1, 'Sub-Category #2.2.1');
INSERT INTO table2 (2, 2, 2, 'Sub-Category #2.2.2');
What I am trying to represent is some sort of hierarchical data here, for
example:
Heading #1
Category #1.1
Sub-Category #1.1.1
Sub-Category #1.1.2
Cateogry #1.2
Sub-Category #1.2.1
Sub-Category #1.2.2
Heading #2
Category #2.1
Sub-Category #2.2.1
Sub-Category #2.2.2
Cateogry #2.2
Sub-Category #2.2.1
Sub-Category #2.2.2
... I hope that makes sense.. Perhaps I'm going about this the wrong way
to begin with?
In any event, the problem is now I have another table which uses these
tables. For example:
CREATE TABLE blech (
somedata VARCHAR,
id1 INTEGER,
id2 INTEGER,
id3 INTEGER
);
INSERT INTO TABLE blech ('Record #1', 1, 1, 1);
INSERT INTO TABLE blech ('Record #2', 1, 1, 1);
INSERT INTO TABLE blech ('Record #3', 1, 2, 1);
INSERT INTO TABLE blech ('Record #4', 1, 1, 2);
INSERT INTO TABLE blech ('Record #5', 2, 1, 1);
... etc... (NOTE: id1, id2, and id3 cannot be NULL in this table)
What I want is a query that will give me this:
count | id1 | id2 | id3
--
4 | 1| |
3 | 1|1|
1 | 1|1| 1
1 | 1|1| 2
1 | 1|2|
1 | 1|2| 1
1 | 2| |
1 | 2|1|
1 | 2|1| 1
I've tried all manner of LEFT JOINs, GROUP BYs, and even tried using
UNION, but I just can't seem to get the results I need. I'm definitely
not married to this type of schema, if there is a more efficient way of
handling this I'm all for it.
Thanks as always!
- Greg
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Query problem - explicit casts
"kurt miller" [EMAIL PROTECTED] writes: Updating table A(fqhcdata) based on values in table B(chn_jmembrm0). Query 1: (the most obvious solution) update fqhcdata set sbrno=( select distinct social_sec_no from chn_jmembrm0 where subscriber_number=fqhcdata.sbrno::varchar ); Result: 000719.08:58:00.243 [20872] FATAL 1: Memory exhausted in AllocSetAlloc() In 7.0 and before, I'd expect the cast here to leak memory, so if the tables are large this result isn't surprising. The leak is fixed for 7.1 but that won't help you today. A more serious objection is that it's not clear the subselect will produce exactly one row. Query 2: (subselect test - explicit cast) select distinct a.social_sec_no as ssn, b.fqhcdata.sbrno from chn_jmembrm0 a,fqhcdata b where a.subscriber_number=b.sbrno::varchar; Result: produces no matching rows I think what's biting you here is that a varchar comparison will consider trailing blanks to be significant --- so unless the subscriber_number values are all pre-padded to 15 chars, they won't match. You could work around this by casting subscriber_number to char(15) instead ... although I tend to think that this is just another demonstration of the principle that using char(n) for variable-length quantities is evil. As an example, US postal abbreviations for states (CA, PA, etc) are good char(2) material. But anything that's even potentially variable length should be varchar(n), NOT char(n). Getting this right to begin with beats the heck out of finding workarounds later. Query 3: (subselect test - flipping cast) select distinct a.social_sec_no as ssn, b.fqhcdata.sbrno from chn_jmembrm0 a,fqhcdata b where a.subscriber_number::char(15)=b.sbrno; Result: ERROR: ExecInitIndexScan: both left and right ops are rel-vars I believe this is fixed in 7.0.2 --- what version are you running? Anyway the bottom line is that changing the char(n) field to be varchar(n) is probably the right answer. regards, tom lane
[GENERAL] Query problem - explicit casts
Query problems. Updating table A(fqhcdata) based on values in table B(chn_jmembrm0). Keys: Table A: sbrno - char(15) Table B: subscriber_number - varchar *both fields have indexes == Query 1: (the most obvious solution) update fqhcdata set sbrno=( select distinct social_sec_no from chn_jmembrm0 where subscriber_number=fqhcdata.sbrno::varchar ); Result: 000719.08:58:00.243 [20872] FATAL 1: Memory exhausted in AllocSetAlloc() == Query 2: (subselect test - explicit cast) select distinct a.social_sec_no as ssn, b.fqhcdata.sbrno from chn_jmembrm0 a,fqhcdata b where a.subscriber_number=b.sbrno::varchar; Result: produces no matching rows == Query 3: (subselect test - flipping cast) select distinct a.social_sec_no as ssn, b.fqhcdata.sbrno from chn_jmembrm0 a,fqhcdata b where a.subscriber_number::char(15)=b.sbrno; Result: ERROR: ExecInitIndexScan: both left and right ops are rel-vars == Query 4: (workaround using temp tables) select *,subscriber_number::char(15) as sbr into tmp1 from chn_jmembrm0; select distinct fqhcdata.sbrno, social_sec_no as ssn into tmp2 from tmp1 where sbr=fqhcdata.sbrno; update fqhcdata set sbrno=(select ssn from tmp2 where fqhcdata.sbrno=sbrno); Result: Updates successfully Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
