Re: [SQL] SUM() & GROUP BY
Martin Kuria wrote: Hi again I have two tables I would like to query i.e. service table and division table SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; OUTPUT: pd_geo | count -- 1| 49 2| 39 3|6 4|54 SELECT d.divisions_name, d.divisions_id) FROM ser s, ser_divisions d WHERE d.divisions_id = s.ser_divisions; division_name | divisions_id -- DEC| 6 DEPI | 7 DRC|8 How can I create a query that displays How the divisions answered the question please do assist. Martin - you'll need to explain exactly what you want. Can you show what outputs you would like given the above data? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] EXPORT / IMPORT
Hello, Is there a simple way to generate an export / import script in PostgreSQL (i.e. a script that contains INSERT statements for every row in a table) ??? COPY is NOT a good option (personal opinion). I’m using pgAdmin3 but it does not offer this (quite disappointing, may I say). Thanx, Freddy.
Re: [SQL] EXPORT / IMPORT
On Fri, May 07, 2004 at 10:20:52AM +0200, Freddy Villalba Arias wrote: > > Is there a simple way to generate an export / import script in > PostgreSQL (i.e. a script that contains INSERT statements for every row > in a table) ??? pg_dump bye Jerome Alet ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Display format for column
Hi there! How do we format column size for displaying data in postgresql. for example my data type for customername is varchar(100) and I want to display the first 25 chars. How should I do that? can I do something like: psql > column customername format a25 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Display format for column
On Fri, May 07, 2004 at 05:08:21PM +0800, Abdul Wahab Dahalan wrote:
> Hi there!
>
> How do we format column size for displaying data in postgresql.
> for example my data type for customername is varchar(100) and I want to
> display the first 25 chars.
> How should I do that?
"display" a data is a client problem only, but you can select part of
string:
# select substring('qwertyuiopasdfg' from 0 for 8);
substring
---
qwertyu
or for your case:
select substring(customername from 0 for 25) FROM yourtab;
http://www.postgresql.org/docs/7.4/static/functions-string.html
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] EXPORT / IMPORT
On Fri, May 07, 2004 at 10:20:52AM +0200, Freddy Villalba Arias wrote: > > Is there a simple way to generate an export / import script in > PostgreSQL (i.e. a script that contains INSERT statements for every row > in a table) ??? > > COPY is NOT a good option (personal opinion). pg_dump -d or -D. Note that restoring from this is going to be a whole lot slower than restoring from a COPY based dump. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(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: [SQL] not really SQL but I need info on BLOBs
In the same vein ... We are at present using BLOBs to store images (but not very many) and generated output (mostly HTML and XML). The data being stored doesn't require BLOB use because of size; it's mostly the binary nature of the data. Lack of satisfaction with the BLOB support (generally difficult to dump and reload the database; strange problems such as OIDs exceeding 2G causing some interface code to break; very difficult to search the BLOB for specific strings) has led us to consider whether we might be better off with a different solution. The one being considered is to convert the BLOBs to BYTEA fields. Does anyone have any experience with this approach? Is there anything in upcoming versions of Pg that would make BLOBs more useful for us? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SUM() & GROUP BY
Thanks Huxton, Sorry for not explaining fully here is what I would like to achieve: When I do: SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; OUTPUT: pd_geo | count -- 1| 49 2| 39 3|6 4|54 It outputs the number of entries the Divisions have made Here is what Division table contains: SELECT * FROM ser_divisions; divisions_name | divisions_id --- DEE | 3131 DEPI | 3133 DED | 3134 GBH | 3136 Now I would like to get to know how each Division answered i.e. SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s WHERE s.ser_divisions = '3131' GROUP BY s.pd_geo; output: pd_geo | count -- 1 | 9 2 | 2 3 | 6 4 | 5 But this is the output I intend to get: divisions_name | pd_geo | count --- DEE | 1 | 9 DEE | 2 | 2 DEE | 3 | 6 DEE | 4 | 5 How do I achieve the above results please do advice thanks again. Kind Regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ >From: Richard Huxton <[EMAIL PROTECTED]> >To: Martin Kuria <[EMAIL PROTECTED]> >CC: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] >Subject: Re: [SQL] SUM() & GROUP BY >Date: Fri, 07 May 2004 09:00:43 +0100 > >Martin Kuria wrote: >>Hi again I have two tables I would like to query i.e. service table >>and division table >> >>SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; >>OUTPUT: >>pd_geo | count >>-- >> 1| 49 >> 2| 39 >> 3|6 >> 4|54 >> >>SELECT d.divisions_name, d.divisions_id) >>FROM ser s, ser_divisions d >>WHERE d.divisions_id = s.ser_divisions; >> >>division_name | divisions_id >>-- >> DEC| 6 >> DEPI | 7 >> DRC|8 >> >>How can I create a query that displays How the divisions answered >>the question please do assist. > >Martin - you'll need to explain exactly what you want. Can you show >what outputs you would like given the above data? > >-- > Richard Huxton > Archonet Ltd _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SUM() & GROUP BY
Martin Kuria wrote: Here is what Division table contains: SELECT * FROM ser_divisions; divisions_name | divisions_id --- DEE | 3131 ... Now I would like to get to know how each Division answered i.e. SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s WHERE s.ser_divisions = '3131' GROUP BY s.pd_geo; divisions_name | pd_geo | count --- DEE | 1 | 9 DEE | 2 | 2 DEE | 3 | 6 DEE | 4 | 5 SELECT d.divisions_name, s.pd_geo, COUNT(s.pd_geo) FROM ser_divisions d, ser s WHERE d.divisions_id = s.ser_divisions GROUP BY d.divisions_name, s.pd_geo ORDER BY d.divisions_name, s.pd_geo ; It's called a join, and any good SQL book should cover it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] SELECT - ORDER BY Croatian characters ....
Hi everybody, I'm using PostgreSQL and my database contains Croatian Characters (čć...) so when I pose a query, and I use order by clause, the result is not sorted properly. I was using once VB for aplications and there I've used a function which changed Croatian characters with some equivalents and sorting order was ok; since now I'm using PHP, is it feasible to write such a function or is there a better solution? thanks Kornelije
Re: [SQL] COUNT on a DISTINCT query
SELECT COUNT(xxx.*) FROM () xxx This cause an error for me: ERROR: cannot pass result of subquery or join "xxx" to a function Try this instead: SELECT COUNT(*) FROM () xxx It worked for me. Regards, Suller Andras ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SUM() & GROUP BY
Try: SELECT d.divisions_name, s.pd_geo, COUNT(s.pd_geo) FROM ser s, ser_divisions d WHERE s.ser_divisions = '3131' AND s.ser_divisions = d.divisions_id GROUP BY d.divisions_name, s.pd_geo; Martin Kuria wrote: Thanks Huxton, Sorry for not explaining fully here is what I would like to achieve: When I do: SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; OUTPUT: pd_geo | count -- 1| 49 2| 39 3|6 4|54 It outputs the number of entries the Divisions have made Here is what Division table contains: SELECT * FROM ser_divisions; divisions_name | divisions_id --- DEE | 3131 DEPI | 3133 DED | 3134 GBH | 3136 Now I would like to get to know how each Division answered i.e. SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s WHERE s.ser_divisions = '3131' GROUP BY s.pd_geo; output: pd_geo | count -- 1 | 9 2 | 2 3 | 6 4 | 5 But this is the output I intend to get: divisions_name | pd_geo | count --- DEE | 1 | 9 DEE | 2 | 2 DEE | 3 | 6 DEE | 4 | 5 How do I achieve the above results please do advice thanks again. Kind Regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ >From: Richard Huxton <[EMAIL PROTECTED]> >To: Martin Kuria <[EMAIL PROTECTED]> >CC: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] >Subject: Re: [SQL] SUM() & GROUP BY >Date: Fri, 07 May 2004 09:00:43 +0100 > >Martin Kuria wrote: >>Hi again I have two tables I would like to query i.e. service table >>and division table >> >>SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; >>OUTPUT: >>pd_geo | count >>-- >> 1| 49 >> 2| 39 >> 3|6 >> 4|54 >> >>SELECT d.divisions_name, d.divisions_id) >>FROM ser s, ser_divisions d >>WHERE d.divisions_id = s.ser_divisions; >> >>division_name | divisions_id >>-- >> DEC| 6 >> DEPI | 7 >> DRC|8 >> >>How can I create a query that displays How the divisions answered >>the question please do assist. > >Martin - you'll need to explain exactly what you want. Can you show >what outputs you would like given the above data? > >-- > Richard Huxton > Archonet Ltd _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SELECT - ORDER BY Croatian characters ....
Kornelije wrote: > I'm using PostgreSQL and my database contains Croatian Characters > (ccz...) so when I pose a query, and I use order by clause, the > result is not sorted properly. You need to initdb your database with the proper locale (hr_HR, probably). Also, choose the right encoding to go along with the locale (LATIN2?). ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] \df
Hi, What sql statement equal to \df function_name I want to know the result data type for a given function within plpgsql. Thanks. Jie Liang ---(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: [SQL] \df
On Saturday, 08 May 2004 01:15, Jie Liang wrote: > What sql statement equal to \df function_name > I want to know the result data type for a given function within plpgsql. Try the -E switch for pgsql: [EMAIL PROTECTED]:~ $ psql -E Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit spaceman=# \df alt_to_iso * QUERY ** SELECT CASE WHEN p.proretset THEN 'setof ' ELSE '' END || pg_catalog.format_type(p.prorettype, NULL) as "Result data type", n.nspname as "Schema", p.proname as "Name", pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype AND NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND p.proname ~ '^alt_to_iso$' ORDER BY 2, 3, 1, 4; ** List of functions Result data type | Schema |Name| Argument data types --+++- void | pg_catalog | alt_to_iso | integer, integer, cstring, ... (1 row) HTH, stefan weiss ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] \df
Thanks. Jie Liang -Original Message- From: Stefan Weiss [mailto:[EMAIL PROTECTED] Sent: Friday, May 07, 2004 4:40 PM To: [EMAIL PROTECTED] Subject: Re: [SQL] \df On Saturday, 08 May 2004 01:15, Jie Liang wrote: > What sql statement equal to \df function_name > I want to know the result data type for a given function within > plpgsql. Try the -E switch for pgsql: [EMAIL PROTECTED]:~ $ psql -E Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit spaceman=# \df alt_to_iso * QUERY ** SELECT CASE WHEN p.proretset THEN 'setof ' ELSE '' END || pg_catalog.format_type(p.prorettype, NULL) as "Result data type", n.nspname as "Schema", p.proname as "Name", pg_catalog.oidvectortypes(p.proargtypes) as "Argument data types" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype AND NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND p.proname ~ '^alt_to_iso$' ORDER BY 2, 3, 1, 4; ** List of functions Result data type | Schema |Name| Argument data types --+++--- --+++-- void | pg_catalog | alt_to_iso | integer, integer, cstring, ... (1 row) HTH, stefan weiss ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CONTEXT on PL/pgSQL
Martin Marques wrote: > El Vie 23 Abr 2004 11:18, Tom Lane escribió: > >>Martin Marques <[EMAIL PROTECTED]> writes: >> >>>I have a simple function made with PL/pgSQL and when I call it I get this >>>in the logs: >>> >>>2004-04-23 10:15:32 [30669] LOG: statement: SELECT nodoSuperior(22) AS >>>sup 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 >>>CONTEXT: PL/pgSQL function "nodosuperior" line 7 at assignment >> >>log_statement currently logs everything the parser sees, which includes >>SQL commands generated by plpgsql. >> >>Arguably these facilities should be separated, but until someone >>makes a serious effort to provide plpgsql debugging features, >>it's likely that nothing will be done about it. Right now this is >>almost the only technique available for seeing what's going on inside >>a plpgsql function, and crummy as it is, it's better than nothing... > > > So the CONTEXT line just tells where the statement was made? U'r right, you can get rid of that CONTEXT configuring postgresql with log_error_verbosity = terse Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
