Re: [GENERAL] [pgsql-advocacy] PostgreSQL professionals group at LinkedIn.com
> > > > Is there an existing Postgres group? > > Yes. Humm.. It never hurts to be more popular on the net. Regards, Gevik Babakhani PostgreSQL NL http://www.postgresql.nl TrueSoftware BV http://www.truesoftware.nl ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] need help optimizing query
Hi all, The situation: there are users in one table, and their access statistics in the other. Now I want to find users whose last access time was more than one month ago. As I've only had to write quite simple queries involving no sub-selects so far, I'd like to ask your opinion if this one scales at all or not. SELECT u.login,last_use_time FROM users u JOIN (SELECT user_id, MAX(stop_time) AS last_use_time FROM stats GROUP BY user_id) AS s ON (u.id=s.user_id) WHERE status='3' AND next_plan_id IS NULL AND last_use_time < now() - interval '1 month' ORDER BY last_use_time; It seems to do the job, but how good is it in the long run? Any way I could tweak it? Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] need help optimizing query
Here's one without a subquery, so may be master: select u.login, s.stop_time from users as a inner join stats as s on s.user_id = u.user_id where status = '3' and next_plan_id is null and stop_time < now() - interval '1 month' group by u.user_id, u.login, s.stop_time order by s.stop_time On 2/2/08, rihad <[EMAIL PROTECTED]> wrote: > > Hi all, > > The situation: there are users in one table, and their access statistics > in the other. Now I want to find users whose last access time was more > than one month ago. As I've only had to write quite simple queries > involving no sub-selects so far, I'd like to ask your opinion if this > one scales at all or not. > > SELECT u.login,last_use_time > FROM users u > JOIN (SELECT user_id, MAX(stop_time) AS last_use_time >FROM stats >GROUP BY user_id) AS s ON (u.id=s.user_id) > WHERE status='3' AND next_plan_id IS NULL >AND last_use_time < now() - interval '1 month' > ORDER BY last_use_time; > > It seems to do the job, but how good is it in the long run? Any way I > could tweak it? > > Thanks. > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- Sincerely yours, Olexandr Melnyk <>< http://omelnyk.net/
[GENERAL] Log query statistics
Hi, Im trying to disable query statistics from log, and I am unable to do it. Cannot find the correct option. I have been cheking google, postgres documentation, i didn't found how to do it so ... I am writting here: I have my logs full of this info: [...] DETAIL: ! system usage stats: ! 0.001546 elapsed 0.001000 user 0.00 system sec ! [0.097985 user 0.008998 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 0/93 [0/3920] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [5/11] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written LOG: QUERY STATISTICS DETAIL: ! system usage stats: ! 0.000610 elapsed 0.000999 user 0.00 system sec ! [0.098984 user 0.008998 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 0/22 [0/3942] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [5/12] voluntary/involuntary context switches ! buffer usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written LOG: QUERY STATISTICS [...] How to disable this output ? Im using postgres 8.2.4 Thanks in advance! -- Ignore Linux at your own risk
[GENERAL] [OT] "advanced" database design (long)
Hello, I think that I understand basic relational theory but then I had an idea. What I would like to know if this is sometimes done or that I am possibly mad... Also, I do not know the terminology for this kind of thing so I do not know where and what to look for. Basically, instead of adding field to a table every time there is a need for it, have a table split in two: one holds identity (id) and one holds the attributes (linked to this id). For example, modelling subject (person): subject: id entered_on (date when entered to the database) sadg (subject attribute group): id value (string - name for group of attributes, e.g. postal address) sad (subject attribute definition): id sadg (references sadg(id)) name (the name of the attribute - e.g. e-mail address, gender) app_type (string - what data type to use in application for sa.value) db_type (string - what cast to use in database for sa.value) sa (subject attribute): id subject (references subject(id)) sad (references sad(id) value (string, holds the attribute value) sads (subject attribute definition set) - what values the sa can have: id sad (references sad(id)) value (string) Basically, if in the future user decides that the subject should have a new attribute, he can simply add "attribute definition" and attribute_definition_set (if any) and the application would handle it without re-designing table, queries, and screens (i.e. the user interface when displaying sadg: "postal address" would always pull all attributes in that group and lay-out would automatically handle the new attributes). The advantage I see, is that to add new fields (here "attributes") no db development would be needed and user could do this. The disadvantages I see is that the model is hard to work with (i.e. how do I see subject (with attributes) as a table - could cross join be used for this?. Also, hand writing the queries for this would be hard (possibly needed if user would like to write custom reports). Do people do this kind of thing (or I took it too far)? If yes, I would be grateful for pointers to examples or any other info on this... Vlad ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [OT] "advanced" database design (long)
Hi, vladimir konrad wrote: I think that I understand basic relational theory but then I had an idea. What I would like to know if this is sometimes done or that I am possibly mad... Also, I do not know the terminology for this kind of thing so I do not know where and what to look for. Basically, instead of adding field to a table every time there is a need for it, have a table split in two: one holds identity (id) and one holds the attributes (linked to this id). For example, modelling subject (person): [example stripped] The advantage I see, is that to add new fields (here "attributes") no db development would be needed and user could do this. The disadvantages I see is that the model is hard to work with (i.e. how do I see subject (with attributes) as a table - could cross join be used for this?. Also, hand writing the queries for this would be hard (possibly needed if user would like to write custom reports). Do people do this kind of thing (or I took it too far)? If yes, I would be grateful for pointers to examples or any other info on this... Yes, this is known as eg. Entity-Attribute-Value model (cf. wikipedia). IMO most times its disadvantages (it can be very hard to write performant queries compared to the traditional row based model) weigh higher than you gain (in flexibility) in relational databases. But it sure has its uses cases. Ciao, Thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [OT] "advanced" database design (long)
> Yes, this is known as eg. Entity-Attribute-Value model (cf. > wikipedia). Thank you for the pointer and term. This will get me started. > IMO most times its disadvantages (it can be very hard to write > performant queries compared to the traditional row based model) weigh > higher than you gain (in flexibility) in relational databases. But it > sure has its uses cases. Obviously, I will have to think it through more... Vlad ---(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] Very long execution time of "select nextval('..');"
Hi Greg, hi Tom, Am Sonntag, 27. Januar 2008 22:44 schrieb Tom Lane: > [EMAIL PROTECTED] writes: > > ok, at the moment i got some traffic and my load is at 1.5. But now with > > logging the timestamp I have seen that the long durations are quite > > regular at intervals of 10 minutes. > > Well, that's pretty suggestive. Tell us about your checkpoint and > bgwriter settings. Also, is there any other service running on the > machine that might have activity spikes every 10 minutes? thanks for your suggestions and the very detail explanation. i pretty much solved my problem. i changed the checkpoint and bg_writer settings, but what was much more important, i droped one Job who did some bad stuff. This job did update some rows on certain situations. these situation came to often so this job was updating all the time. I never ment to blame postgresql for my trouble as i work with it for years and i am pretty sure that postgresql is doing an excelent job. i 've got to blame me for this bad programming. thanks a lot for helping as this checkpoint discussion opened my mind for the real problem which was not easy to see (at least for me). But one more question to my problem: Before i solved it i saw some statements which say "SELECT waiting" in the process table (ps ax) i thought "waiting" means some kind of database lock (row or table lock). Is it true? If not, what other conditions can occur if a process says "SELECT waiting"? Can it mean "Disk/IO Waiting" or "Network IO waiting"? kind regrads Janning ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [OT] "advanced" database design (long)
--- vladimir konrad <[EMAIL PROTECTED]> wrote: > I think that I understand basic relational theory but then I had an > idea. > Basically, instead of adding field to a table every time there is a > need for it, have a table split in two: one holds identity (id) and > one holds the attributes (linked to this id). > Basically, if in the future user decides that the subject should > have a new attribute, he can simply add "attribute definition" and > attribute_definition_set (if any) and the application would handle Basically, you would be creating your own data dictionary (i.e. system catalog) on top of the db data dictionary. The database already comes with a way to easily add columns: ddl. I have seen newbie database designers reinvent this method a hundred times. The performance hits and complexity of querying data would far out weigh any perceived maintenance gain. My .02. LewisC Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ LewisC's Random Thoughts http://lewiscsrandomthoughts.blogspot.com/ ---(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
Re: [GENERAL] [OT] "advanced" database design (long)
vladimir konrad <[EMAIL PROTECTED]> wrote: > > Hello, > > I think that I understand basic relational theory but then I had an > idea. What I would like to know if this is sometimes done or that I am > possibly mad... Also, I do not know the terminology for this kind of > thing so I do not know where and what to look for. > > Basically, instead of adding field to a table every time there is a > need for it, have a table split in two: one holds identity (id) and one > holds the attributes (linked to this id). For example, modelling > subject (person): > > subject: > id > entered_on (date when entered to the database) > > sadg (subject attribute group): > id > value (string - name for group of attributes, e.g. postal > address) > > sad (subject attribute definition): > id > sadg (references sadg(id)) > name (the name of the attribute - e.g. e-mail address, gender) > app_type (string - what data type to use in application for > sa.value) > db_type (string - what cast to use in database for sa.value) > > sa (subject attribute): > id > subject (references subject(id)) > sad (references sad(id) > value (string, holds the attribute value) > > sads (subject attribute definition set) - what values the sa can have: > id > sad (references sad(id)) > value (string) > > Basically, if in the future user decides that the subject should have > a new attribute, he can simply add "attribute definition" and > attribute_definition_set (if any) and the application would handle it > without re-designing table, queries, and screens (i.e. the user > interface when displaying sadg: "postal address" would always pull all > attributes in that group and lay-out would automatically handle the new > attributes). Sounds like LDAP. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
On Thursday 31 January 2008 07:08, [EMAIL PROTECTED] wrote:
> [Following up on my own message.]
>
>
> Also,
>
> > let
> >
> > us
> >
> > know
> >
> > your
> >
> > wal
> >
> > tunning
> >
> > parameters
> >
> > like
> >
> > commit_delay,
> >
> > fsync.
>
> I haven't done any tuning as of yet. I'm running with the default settings
> produced by initdb.
>
Don't even bother trying to tune zfs untill after you've tuned postgres,
otherwise your wasting your time.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(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
Re: [GENERAL] Log query statistics
On Feb 2, 2008 5:51 AM, Rubén Rubio <[EMAIL PROTECTED]> wrote: > > Hi, > > Im trying to disable query statistics from log, and I am unable to do it. > Cannot find the correct option. I have been cheking google, postgres > documentation, i didn't found how to do it so ... I am writting here: > > I have my logs full of this info: > > [...] > DETAIL: ! system usage stats: > ! 0.001546 elapsed 0.001000 user 0.00 system sec > ! [0.097985 user 0.008998 sys total] > ! 0/0 [0/0] filesystem blocks in/out > ! 0/93 [0/3920] page faults/reclaims, 0 [0] swaps > ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent > ! 0/0 [5/11] voluntary/involuntary context switches > ! buffer usage stats: > ! Shared blocks: 0 read, 0 written, buffer > hit rate = 100.00% > ! Local blocks: 0 read, 0 written, buffer > hit rate = 0.00% > ! Direct blocks: 0 read, 0 written > LOG: QUERY STATISTICS > DETAIL: ! system usage stats: > ! 0.000610 elapsed 0.000999 user 0.00 system sec > ! [0.098984 user 0.008998 sys total] > ! 0/0 [0/0] filesystem blocks in/out > ! 0/22 [0/3942] page faults/reclaims, 0 [0] swaps > ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent > ! 0/0 [5/12] voluntary/involuntary context switches > ! buffer usage stats: > ! Shared blocks: 0 read, 0 written, buffer > hit rate = 100.00% > ! Local blocks: 0 read, 0 written, buffer > hit rate = 0.00% > ! Direct blocks: 0 read, 0 written > LOG: QUERY STATISTICS > [...] > > How to disable this output ? Im using postgres 8.2.4 > > Thanks in advance! > -- > Ignore Linux at your own risk In your postgresql.conf file you've got one of these turned on: # - Statistics Monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off just set it to off and reload. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Request for help with database of Kenyan election violence
Hello, The Kenya National Commission for Human Rights is investigating the violence in Kenya. This has led to an urgent request on Groklaw http://www.groklaw.net/article.php?story=20080202013451629 for assistance in setting up a database. I have suggested that a suite of PostgreSQL based tools might be appropriate. http://www.groklaw.net/comment.php?mode=display&sid=20080202013451629&title=Postgresql%20and%20phpPgAdmin&type=article&order=&hideanonymous=0&pid=0#c669544 If you have any comment on my suggestions, suggestions for additional or better suited tools, or otherwise wish to help with such a project please chime in on groklaw. I have not used Postgres Forms and so am not 100% confident in my proposal. Thank you. Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [OT] "advanced" database design (long)
> Basically, you would be creating your own data dictionary (i.e. > system catalog) on top of the db data dictionary. The database > already comes with a way to easily add columns: ddl. I have seen > newbie database designers reinvent this method a hundred times. The > performance hits and complexity of querying data would far out weigh > any perceived maintenance gain. And I thought that this would make me a famous database designer ;-), well, given the deadline for this + other concerns it looks like implementing my own type system is over-kill (+ it would be impossible for "normal" users to write report queries). Thank you all for input, i will do it the "normal" way. Vlad ---(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
Re: [GENERAL] Oracle Analytical Functions
On Jan 31, 2008 8:49 AM, Enrico Sirola <[EMAIL PROTECTED]> wrote: > I'd create a "previousTime" column and manage it using a trigger. > Anyway, it depends on the time-dependancy of the table > Then you can perform "temporal" in a much easier way. > You could be interested in taking a look at the following link > > http://www.cs.arizona.edu/~rts/tdbbook.pdf +1 That book provides many sane examples for handling temporal data. Recommended. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] arrays of floating point numbers / linear algebra operations into the DB
Hi Webb, Joe, Martijn
Webb Sprague ha scritto:
On Feb 1, 2008 2:31 AM, Enrico Sirola <[EMAIL PROTECTED]> wrote:
Hello,
I'd like to perform linear algebra operations on float4/8 arrays
Having avoided a bunch of real work wondering about linear algebra and
PG, did you consider the Gnu Scientific Library ? We would still need
to hook everything together, but it seems to do a lot of this, and is
written in C, etc.
I experimented a bit today with cblas, and wrapped the blas function for
scaling a vector. The following session shows the usage:
create or replace function scale(float8, float8[])
returns float8[]
as '$libdir/linalg', 'scale'
language 'C' immutable strict;
sps_dev=# select scale(k, '{1,2,3}') from generate_series(1,10) k;
scale
{1,2,3}
{2,4,6}
{3,6,9}
{4,8,12}
{5,10,15}
{6,12,18}
{7,14,21}
{8,16,24}
{9,18,27}
{10,20,30}
(10 rows)
sps_dev=# create operator * (leftarg=float8, rightarg=float8[],
procedure=scale);
sps_dev=# select k * '{1,2,3}'::float8[] from generate_series(1,10) k;
?column?
{1,2,3}
{2,4,6}
{3,6,9}
{4,8,12}
{5,10,15}
{6,12,18}
{7,14,21}
{8,16,24}
{9,18,27}
{10,20,30}
(10 rows)
I'm quite proud, this is my first C extension function ;-)
I'd gladly post the code if it's ok for the list users. It's more or
less 100 lines of code. This approach seems promising...
By the way, Webb: I took a look at GSL and it seems to me that, from a
linear algebra point of view, it's basically cblas, so I'd use cblas
directly.
Please let me know your thoughts/advices,
e.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] first message: SELECT FROM
Hi, This is my first message, and I need some help. I have just installed Postgresql 8.2. (Windows). At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all columns correctly. But when I try to get only one column, SELECT senha FROM USUARIOS, I get the error message: ERROR: column "senha" does not exist SQL state: 42703 Character: 8 What is the problem ? -- -- Aílsom F. Heringer Rio de Janeiro, Brasil [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [OT] "advanced" database design (long)
On Sat, Feb 02, 2008 at 01:38:19PM +0100, Thomas Pundt wrote: > Hi, > > vladimir konrad wrote: >> I think that I understand basic relational theory but Clearly, you'll have to revisit that thought. > [example stripped] > > Yes, this is known as eg. Entity-Attribute-Value model (cf. > wikipedia). > > IMO most times its disadvantages (it can be very hard to write > performant queries compared to the traditional row based model) Make that, "impossible." The "flexibility" stems from fear of making a design decision. The second and smaller price is having the system bog down entirely and have to be scrapped, whether it's 3 months down the line, or 3 years. The math beneath this is that query complexity goes up like O(E!A!V!) for Entity, Attribute and Value. The first price, though, and by far the biggest, is that it's impossible to maintain any kind of data integrity in such a system, as such constraints, by their nature, are application-dependent. Two applications means you're violating the SPOT (Single Point of Truth) Rule, and that in turn means your data turns quickly into incomprehensible gibberish. > weigh higher than you gain (in flexibility) in relational databases. > But it sure has its uses cases. Why, yes. I encourage all my competitors to use it. ;) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] first message: SELECT FROM
On Sat, 2 Feb 2008 15:43:15 -0200 "Aílsom F. Heringer" <[EMAIL PROTECTED]> wrote: > Hi, > This is my first message, and I need some help. I have just installed > Postgresql 8.2. (Windows). > > At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all > columns correctly. But when I try to get only one column, SELECT senha > FROM USUARIOS, I get the error message: > > ERROR: column "senha" does not exist > SQL state: 42703 > Character: 8 > > What is the problem ? The problem is that the column doesn't exist. Just like the error message says. Without some more information (such as the table definition) I can only speculate on exactly why that is the case, and my speculation is that you created the table with the column names in uppercase and pgAdmin preserved that. PostgreSQL is case-sensative, so try matching the column name exactly and putting "" around it. If that doesn't work, provide some more information in your question. -Bill ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] first message: SELECT FROM
On 02/02/2008 17:43, Aílsom F. Heringer wrote: At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all columns correctly. But when I try to get only one column, SELECT senha FROM USUARIOS, I get the error message: ERROR: column "senha" does not exist SQL state: 42703 Character: 8 Can you show us the columns in your schema? That message means exactly what it says - the column you specified doesn't exist. One gotcha is that if you created the table using double-quoted MiXeD-CaSe or UPPER-CASE column names, then select senha . won't be the same as select "Senha" . Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] first message: SELECT FROM
On lau, 2008-02-02 at 15:43 -0200, Aílsom F. Heringer wrote: > At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all > columns correctly. But when I try to get only one column, SELECT senha > FROM USUARIOS, I get the error message: > > ERROR: column "senha" does not exist > SQL state: 42703 > Character: 8 if you created the column name mixedcase with quotes, you need to access it the same way. so if the column name is really "Senha" do: SELECT "Senha" FROM USUARIOS; if that is not the problem, can you repeat this in the command-line tool psql ? gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] first message: SELECT FROM
On Feb 2, 2008 11:43 AM, Aílsom F. Heringer <[EMAIL PROTECTED]> wrote:
> Hi,
> This is my first message, and I need some help. I have just installed
> Postgresql 8.2. (Windows).
>
> At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
> columns correctly. But when I try to get only one column, SELECT senha
> FROM USUARIOS, I get the error message:
>
> ERROR: column "senha" does not exist
> SQL state: 42703
> Character: 8
>
> What is the problem ?
Most likely this is a mixed case issue.
If you create a table like so:
create table abc (Field1 int);
it will create table abc with a field called field1. Notice the
automatic case folding. If you quote it, like so
create table abc ("Field1" int);
It will create Field1 instead. From then on, you'll have to quote
that field to get it by name, because asking for Field1 without
quotes, or field1 with or without quotes, will get folded to lower
case, and won't match Field1
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] first message: SELECT FROM
Bill,
The collumn is defined as "Senha", and I am sending "SELECT Senha FROM
Usuarios".
Below, the table definition from SQL Pane:
CREATE TABLE usuarios
(
"CdUsuario" character(6) NOT NULL,
"NmUsuario" character(15) NOT NULL,
"DtCadastro" timestamp without time zone NOT NULL DEFAULT now(),
"StSuper" boolean DEFAULT false,
"Senha" character(10),
"MudarSenha" boolean DEFAULT true,
"UltimoAcesso" timestamp without time zone,
"Ativo" boolean DEFAULT true,
CONSTRAINT "PkCdusuario" PRIMARY KEY ("CdUsuario")
)
WITHOUT OIDS;
ALTER TABLE usuarios OWNER TO postgres;
GRANT ALL ON TABLE usuarios TO postgres;
Ailsom
2008/2/2, Bill Moran <[EMAIL PROTECTED]>:
> On Sat, 2 Feb 2008 15:43:15 -0200
> "Aílsom F. Heringer" <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> > This is my first message, and I need some help. I have just installed
> > Postgresql 8.2. (Windows).
> >
> > At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all
> > columns correctly. But when I try to get only one column, SELECT senha
> > FROM USUARIOS, I get the error message:
> >
> > ERROR: column "senha" does not exist
> > SQL state: 42703
> > Character: 8
> >
> > What is the problem ?
>
> The problem is that the column doesn't exist. Just like the error message
> says. Without some more information (such as the table definition) I can
> only speculate on exactly why that is the case, and my speculation is that
> you created the table with the column names in uppercase and pgAdmin
> preserved that. PostgreSQL is case-sensative, so try matching the column
> name exactly and putting "" around it. If that doesn't work, provide some
> more information in your question.
>
> -Bill
>
--
--
Aílsom F. Heringer
Rio de Janeiro, Brasil
[EMAIL PROTECTED]
[EMAIL PROTECTED]
---(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] first message: SELECT FROM
Ok. That was the problem. Now SELECT "Senha" FROM USUARIOS is working fine. But, At an application using ODBC connection I will need to send "Senha" too ? Aílsom 2008/2/2, Ragnar <[EMAIL PROTECTED]>: > On lau, 2008-02-02 at 15:43 -0200, Aílsom F. Heringer wrote: > > > At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all > > columns correctly. But when I try to get only one column, SELECT senha > > FROM USUARIOS, I get the error message: > > > > ERROR: column "senha" does not exist > > SQL state: 42703 > > Character: 8 > > if you created the column name mixedcase with quotes, > you need to access it the same way. > so if the column name is really "Senha" do: > SELECT "Senha" FROM USUARIOS; > > if that is not the problem, can you repeat this in > the command-line tool psql ? > > gnari > > > -- -- Aílsom F. Heringer Rio de Janeiro, Brasil [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] first message: SELECT FROM
On 02/02/2008 17:59, Aílsom F. Heringer wrote: The collumn is defined as "Senha", and I am sending "SELECT Senha FROM [] CREATE TABLE usuarios ( [] "Senha" character(10), [] There you go - you need to do select "Senha" Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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] [OT] "advanced" database design (long)
Lewis Cunningham wrote: --- vladimir konrad <[EMAIL PROTECTED]> wrote: I think that I understand basic relational theory but then I had an idea. Basically, instead of adding field to a table every time there is a need for it, have a table split in two: one holds identity (id) and one holds the attributes (linked to this id). Basically, if in the future user decides that the subject should have a new attribute, he can simply add "attribute definition" and attribute_definition_set (if any) and the application would handle Basically, you would be creating your own data dictionary (i.e. system catalog) on top of the db data dictionary. The database already comes with a way to easily add columns: ddl. If you have some part of your app that needs to "select" the list of columns in a table you should look at http://www.postgresql.org/docs/8.2/interactive/catalogs.html particularly pg_class and pg_attribute -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(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
Re: [GENERAL] [OT] "advanced" database design (long)
> If you have some part of your app that needs to "select" the list of > columns in a table you should look at > http://www.postgresql.org/docs/8.2/interactive/catalogs.html > particularly pg_class and pg_attribute Thanks, this could come handy. Vlad ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] first message: SELECT FROM
On Feb 2, 2008, at 6:56 PM, Bill Moran wrote: preserved that. PostgreSQL is case-sensative, so try matching the column name exactly and putting "" around it. If that doesn't work, provide some That is just plain incorrect, PostgreSQL is *not* case sensitive. The real problem here (as Scott pointed out) is that the column in the table is defined so that the case is preserved (by quoting the column name), but the column in the query isn't and thus gets folded (to lowercase) and can't be found. I'm inclined to call this a bug in PgAdmin III (except that I hardly know it), it shouldn't quote identifiers to keep case if the user doesn't explicitly ask it to. This question pops up frequently, so it would be nice if something was done about it (my preference would be by not /implicitly/ quote identifiers). It certainly seems to confuse novice users. My €0.02. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47a4c3e7817485094119420! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL][UMN_MAPSERVER-USERS] query postgres AND oracle
thanks. thought dbi-link could only connect across postgres databases, didn't realize it can connect postgres AND oracle. will look into it. jzs On 1/31/08, Chander Ganesan <[EMAIL PROTECTED]> wrote: > > John Smith wrote: > guys, > i got geometry in postgres, some other data in oracle. is it possible > to filter postgres geometry based on oracle data (without using > services)? > cheers, jzs > > ---(end of > broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > > Have you looked at http://pgfoundry.org/projects/dbi-link/ > ? > -- > Chander Ganesan > Open Technology Group, Inc. > One Copley Parkway, Suite 210 > Morrisville, NC 27560 > Phone: 877-258-8987/919-463-0999 > http://www.otg-nc.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL][UMN_MAPSERVER-USERS] query postgres AND oracle
"John Smith" <[EMAIL PROTECTED]> writes: > thanks. thought dbi-link could only connect across postgres databases, > didn't realize it can connect postgres AND oracle. will look into it. No, you're confusing it with dblink, which is Postgres-specific. dbi-link relies on the Perl DBI layer, so it should be able to talk to anything for which you can find a DBD driver. It's a completely different programming model though, and doesn't expose as much flexibility as dblink does. regards, tom lane ---(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] arrays of floating point numbers / linear algebra operations into the DB
> I'm quite proud, this is my first C extension function ;-) > I'd gladly post the code if it's ok for the list users. It's more or > less 100 lines of code. This approach seems promising... I would definitely like to see it. > By the way, Webb: I took a look at GSL and it seems to me that, from a > linear algebra point of view, it's basically cblas, so I'd use cblas > directly. > Please let me know your thoughts/advices, The only thing about GSL is that it would make it easier to tie into some very sophisticated stuff later, and (I think) the basic linear algebra is probably just as fast as CBLAS, and we could implement it first. It would also be easy to define a big project as : "bring GSL to Postgres", and then people could work on pieces. But if you actually write it, you get to decide :) GSL licensing is GNU ish, so may be that is a deal breaker, too. w > e. > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [OT] "advanced" database design (long)
David Fetter wrote: > The math beneath this is that query complexity goes up like O(E!A!V!) > for Entity, Attribute and Value. Makes sense. > The first price, though, and by far the biggest, is that it's > impossible to maintain any kind of data integrity in such a system, as > such constraints, by their nature, are application-dependent. Two > applications means you're violating the SPOT (Single Point of Truth) > Rule, and that in turn means your data turns quickly into > incomprehensible gibberish. Starts making sense, too. Could you give advice (or pointers what I should look at) on how one would (roughly) "properly" schemafy the following requirements: - unknown number of differing paper forms to print data on - user fills in on-screen masks to aggregate data for printing Intent: use the database to store a) definitions for on-screen masks, b) definitions for printout (how to place data), c) the denormalized data eventually put into form instances (the normalized source data already is in the database). There seem to be three basic approaches: - one table per form def plus one per form type holding content - one table holding form defs as, say, XML to be parsed client-side plus another table holding form data as XML, too - EAV: tables holding form defs, field defs, form instances pointing to form defs, and field data pointing to field defs and form instances each with all the relevant foreign keys The first requires DDL whenever a form is added by a user. The second requires client-side logic making form reuse across clients a lot harder (SPOT violation ?). The third sounds OK -- but seems to be of the apparently dreaded EAV type. What am I missing ? Where should I get a clue ? Thanks, Karsten Hilbert, MD wiki.gnumed.de -- Der GMX SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen! Ideal für Modem und ISDN: http://www.gmx.net/de/go/smartsurfer ---(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
Re: [GENERAL] first message: SELECT FROM
On Saturday 02 February 2008 17:43, Aílsom F. Heringer wrote: > Hi, > This is my first message, and I need some help. I have just installed > Postgresql 8.2. (Windows). > > At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all > columns correctly. But when I try to get only one column, SELECT senha > FROM USUARIOS, I get the error message: > > ERROR: column "senha" does not exist > SQL state: 42703 > Character: 8 > > What is the problem ? Please post your table definition so that we can see what you are selecting from. If you were one of my students I would say "check your table definition or your spelling". Regards Garry ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Request for help with database of Kenyan election violence
On Feb 2, 2008 4:51 PM, Karl O. Pinc <[EMAIL PROTECTED]> wrote: > Hello, > > The Kenya National Commission for Human Rights is investigating > the violence in Kenya. This has led to an urgent request on Groklaw > http://www.groklaw.net/article.php?story=20080202013451629 > for assistance in setting up a database. > > I have suggested that a suite of PostgreSQL based tools might > be appropriate. > http://www.groklaw.net/comment.php?mode=display&sid=20080202013451629&title=Postgresql%20and%20phpPgAdmin&type=article&order=&hideanonymous=0&pid=0#c669544 > > If you have any comment on my suggestions, suggestions for > additional or better suited tools, or otherwise > wish to help with such a project please chime in on > groklaw. > > I have not used Postgres Forms and so am not 100% confident > in my proposal. > > Thank you. I hope that discussions are raging *MORE* about what the kinds of functionality actually needed are, notably with respect to - what specific sorts of data needs to be collected - what known correlations there are (which would tend to imply the shape of the DB schema) as THAT sort of detail is what will allow technical choices to become defensible. As it stands, I have no idea whether the tools you suggest are suitable for the requirements, because I really don't know what the requirements are. One thing that I *do* see in browsing the Groklaw discussion is that there seems to be some risk of inconvenient things disappearing, so that it seems likely that part of the design needs to include a regular "escrow" process whereby data is transmitted to a remote (and preferably outside-Kenya) location. That's a requirement - not a technology choice. The longer you can keep attention focused on what is needed, in technology-agnostic terms, the better. -- http://linuxfinances.info/info/linuxdistributions.html "The definition of insanity is doing the same thing over and over and expecting different results." -- assortedly attributed to Albert Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL/PHP Application Server
On Thu, 2008-01-24 at 13:10 -0500, John DeSoi wrote: > . The user/login system is extensible, so you could write your own. I'm not sure if I follow: Are you suggestion that the CMS system, Drupal, is an example of an application server model because of its framework extension? ~BAS IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. ---(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
Re: [GENERAL] Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
Don't even bother trying to tune zfs untill after you've tuned postgres,
otherwise your wasting your time.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
As it turns out, I think the ZFS-on-RAID setup I had is the problem[1]. After
running some more I/O tests with "fsync" turned on, I noticed that filesystem
"writes" were about 400% slower. Running PostgreSQL without "fsync" made the
application run about 1.5 times faster on ZFS versus UFS. So, now it's back to
the drawing board to figure out if I can make the synchronous writes faster.
Bob
[1] the suggestions made previously helped a bit, but not nearly enough to
overcome the 50% drop originally noted.
[GENERAL] temp sequence
"PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC i386-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1)" I am creating a temporary sequence in a function and it seems like it is not going away after the function finishes. The front end is in MS Access 2000 and I have a single connection. When I call the function once it works, when I call it a second time, it gives me an error that the sequence already exists. When I restart the application, I can call the function again. I solved the problem by using: alter sequence seq_linenum restart with 1; The manual states: If specified, the sequence object is created only for this session, and is automatically dropped on session exit. Existing permanent sequences with the same name are not visible (in this session) while the temporary sequence exists, unless they are referenced with schema-qualified names. I thought that a function would be considered its own session, is that incorrect? Thank you Sim ---(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] temp sequence
On Feb 3, 2008 1:39 AM, Sim Zacks <[EMAIL PROTECTED]> wrote:
> "PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC i386-pc-linux-gnu-gcc
> (GCC) 4.1.1 (Gentoo 4.1.1)"
>
> I am creating a temporary sequence in a function and it seems like it is not
> going away after the function finishes.
> The front end is in MS Access 2000 and I have a single connection. When I call
> the function once it works, when I call it a second time, it gives me an error
> that the sequence already exists. When I restart the application, I can call
> the
> function again.
>
create, and use the sequence through EXECUTE
EXECUTE 'create temp sequence seq1';
EXECUTE 'select nextval(' || quote_literal('seq') || ') ';
the same apply for all kind of temp objects, in 8.3 this no longer
will be an issue
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
