[SQL] setting the where clause
Hi, I'm am programming in python using the Dabo modules. www.dabodev.com if your interested. Dabo is a framework that provides an easy way to build desktop app's. To clear a data entry form. I have been setting the where clause to "where 1=0". This of course retrieves 0 records and my form will display nothing. It has been suggested that it might be better to set the where clause to a primary key value that does not exist in the table "where PK = -999". I wonder what you guys think. How might it effect performance? -- John Fabiani -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Information schema permissions
Assume a standard install on Linux (what ever that means). Would a normal user have access to information schema? I have a front end written in python that accesses tables and permissions are set based on roles. But I do nothing with respect to the information schema view. I would like my program to access the view. Would there be anything preventing a standard user from 'SELECT' access? -- John Fabiani ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Information schema permissions
On Wednesday 06 February 2008 03:37:22 pm Tom Lane wrote: > johnf <[EMAIL PROTECTED]> writes: > > Assume a standard install on Linux (what ever that means). Would a > > normal user have access to information schema? I have a front end > > written in python that accesses tables and permissions are set based on > > roles. But I do nothing with respect to the information schema view. I > > would like my program to access the view. Would there be anything > > preventing a standard user from 'SELECT' access? > > The information schema views are accessible to anyone, but be aware that > they show different sets of rows to different people --- you can only > see rows that are about tables that you have some access permissions > for. This might or might not confuse your app ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org Thanks. Actually it would benefit my app. -- John Fabiani ---(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: [SQL] accounting schema
On Wednesday 06 February 2008 06:54:36 pm Medi Montaseri wrote: > I suppose instead of AR and AP tables, I can just have one table called > Entry (thanks Joe) with an attribute indicating AR vs AP. I recommend you not do have only one table for transaction. AR and AP are different animals and each can get very complex quickly. Adding a field that only applies to AP and not AR is a very simple example of what goes wrong. -- John Fabiani ---(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: [SQL] postgresql function not accepting null values inselect statement
On Friday 22 February 2008 01:35:47 am Bart Degryse wrote: > Can you try this... > > CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer) > RETURNS SETOF t_functionaries AS > $BODY$ > DECLARE > rec t_functionaries%ROWTYPE; > BEGIN > FOR rec IN ( > SELECT f.functionaryid, f.category, f.description > FROM functionaries f > WHERE f.statecd IS NOT DISTINCT FROM p_statecd) > LOOP > return next rec; > END LOOP; > return; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > a newbie question. Could you explain why yours works? I don't understand how it works if p_statecd = NULL -- John Fabiani ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Is this possible?
Hi, I'm not to sure this is possible. I need to replace a primary key (pkid) with the value of a different field. I have pkid = 200 attendid = 301 I need the pkid = 301 But there may or may not be a pkid that already exist that has the value of 301. The attendid is unique and the pkid data type is serial (has a sequence). Thanks for the help. -- John Fabiani -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Is this possible?
On Monday 16 February 2009 10:32:26 pm A. Kretschmer wrote:
> In response to johnf :
> > Hi,
> > I'm not to sure this is possible.
> >
> > I need to replace a primary key (pkid) with the value of a different
> > field. I have
> > pkid = 200
> > attendid = 301
> >
> > I need the pkid = 301
> >
> > But there may or may not be a pkid that already exist that has the value
> > of 301. The attendid is unique and the pkid data type is serial (has a
> > sequence).
> >
> > Thanks for the help.
>
> You can do that within a transaction and dropping the pk-constraint:
>
> test=*# \d foo
> Table "public.foo"
> Column | Type |Modifiers
> +-+--
> id | integer | not null default nextval('foo_id_seq'::regclass)
> i | integer |
> Indexes:
> "foo_pkey" PRIMARY KEY, btree (id)
> "foo_i_key" UNIQUE, btree (i)
>
> test=*# select * from foo;
> id | i
> +---
> 1 | 2
> 2 | 1
> 3 | 3
> (3 rows)
>
> test=*# alter table foo drop constraint foo_pkey;
> ALTER TABLE
> test=*# update foo set id=i;
> UPDATE 3
> test=*# alter table foo add primary key(id);
> NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "foo_pkey" for table "foo" ALTER TABLE
> test=*# \d foo
> Table "public.foo"
> Column | Type |Modifiers
> +-+--
> id | integer | not null default nextval('foo_id_seq'::regclass)
> i | integer |
> Indexes:
> "foo_pkey" PRIMARY KEY, btree (id)
> "foo_i_key" UNIQUE, btree (i)
>
> test=*# select * from foo;
> id | i
> +---
> 2 | 2
> 1 | 1
> 3 | 3
> (3 rows)
>
>
> HTH, Andreas
Wow that looks like it will work - thanks.
When you say 'within a transaction' do you mean starting with
"Begin" and using "commit"?
--
John Fabiani
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Best practices for geo-spatial city name searches?
On Tuesday 24 February 2009 08:19:56 am Mark Stosberg wrote: > Hello, > > I use PostgreSQL and the "cube" type to perform geo-spatial zipcode > proximity searches. I'm wondering about the best practices also supporting > a geo-spatial distance search based on a city name rather than zipcode. > > In our original data model, we used a 'zipcodes' table, with the zipcode as > the primary key. This can of course contain a "City Name" column, but > there is a problem with this, illustrated a "Nome, Alaska" case. Nome's > zipcode is 99762. It maps to multiple cities including Diomede, Alaska and > Nome, Alaska. > > In the data model described, only the "Diomede" row is imported, and the > other rows, including the "Nome, Alaska" row are dropped. So if you try to > search for Nome, Alaska, you won't find anything. > > One solution would be to have a "cities" table, with the city/state as the > primary key, and a zipcode as an additional column. Then, by joining on the > zipcodes table, the coordinates for a city could be found. > > Is there any other way I should be considering data modelling to support > searches on zipcodes and cities? > > Thanks! > > Mark > > > -- > . . . . . . . . . . . . . . . . . . . . . . . . . . . >Mark StosbergPrincipal Developer >m...@summersault.com Summersault, LLC >765-939-9301 ext 202 database driven websites > . . . . . http://www.summersault.com/ . . . . . . . . I don't know if this is any help. I recently used google to obtain the longitude and latitude and then used simple math to determine the distance between the locations to determine proximity searches. Like finding the closes store. -- John Fabiani -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] using a list to query
I have a list (or array) of primary keys stored in a field (data type text). I would like to use the list to retrieve all the data from a table based on the list. my text field contains: '123,134,343,345' I would to do something like the following: Select * from table1 where table1.pkid in (select myTextfield from table2) So the question is there a simple way to use the list to retrieve my data? -- John Fabiani -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using a list to query
On Saturday 02 May 2009 06:34:57 pm Craig Ringer wrote: > johnf wrote: > > I have a list (or array) of primary keys stored in a field (data type > > text). I would like to use the list to retrieve all the data from a table > > based on the list. > > > > my text field contains: > > '123,134,343,345' > > > > I would to do something like the following: > > > > Select * from table1 where table1.pkid in (select myTextfield from > > table2) > > > > So the question is there a simple way to use the list to retrieve my > > data? > > http://www.postgresql.org/docs/8.3/static/functions-matching.html > > SELECT * FROM table1 > WHERE table1.pkid IN ( > SELECT x FROM regexp_split_to_table(myTextfield, ',') AS x > ); > > ... but you should consider storing your list in an array instead, or > using a more conventional child table with a (pkid, refid) pair list. > > -- > Craig Ringer Thanks - I think this will work very well. I considered an array but at the moment I don't have an easy way of retrieving data from an array. I'm working on that as I type. The other solution would be a table but that seems over kill for one field. Thanks again -- John Fabiani -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
