[SQL] How to query information schema from shell script
Hey, I'm trying to figure out how i can query the postgres information schema from a bourne shell script on linux. I need to know if a user/table exists. Does someone mind giving me a quick example of how this works, is this possible? Thanks, Jon. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to query information schema from shell script
> I'm trying to figure out how i can query the postgres information > schema from a bourne shell script on linux. I need to know if a > user/table exists. Does someone mind giving me a quick example of how > this works, is this possible? % for tn in `psql -Umnp -dmnp_gp -hstgdb0 -tA -c"select table_name from information_schema.tables where table_name like 'employee%'"`; do for> echo FOUND TABLE $tn for> done FOUND TABLE employee_role FOUND TABLE employee hope this helps, george ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to query information schema from shell script
am Fri, dem 27.10.2006, um 9:31:03 -0400 mailte Jon Horsman folgendes: > Hey, > > I'm trying to figure out how i can query the postgres information > schema from a bourne shell script on linux. I need to know if a > user/table exists. Does someone mind giving me a quick example of how You can do something like this: bla=`echo "select bla from fasel" | psql -t` You have then the result in $bla. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to query information schema from shell script
You can use "-c" option of psql client. Ex.: psql -p 5000 -d database -c "SELECT * FROM information_schema.tables WHERE table_schema = 'public'" 2006/10/27, Jon Horsman <[EMAIL PROTECTED]>: Hey,I'm trying to figure out how i can query the postgres informationschema from a bourne shell script on linux. I need to know if auser/table exists. Does someone mind giving me a quick example of how this works, is this possible?Thanks,Jon.---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster -- William Leite Araújo
Re: [SQL] How to query information schema from shell script
Thanks for the suggestions guys, i'll give this a try. Jon ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] not able to execute query on spatial database.
Hello all, I am working with spatial tables. i have two spatial data tables, one represents the whole state and another table represents the damaged locations in the state. My aim is to find number of damaged locations in every county or parish. Here i am giving the query i used to do this, could you please tell me whether i am doing right thing or not. select count(*) from damagedlocations l1,county l2 where (l2.the_geom and l1.the_geom) and l2.parishid=particular_parishid; thanks in advance. vamsee movva
Re: [SQL] How to query information schema from shell script
In my original email i forgot to mentioned i need to know if the database exists or not from the shell script. If it doesn't exist i would then create it. Currently i was just creating the db everytime our db script is run (since it doesn't hurt the db) but this generates the 'db already exists' log and I now have the requirement that the users not see that log. ie, don't run createdb if it already exists. In looking through the postgres docs i can see how to check if a table exists but not how a db exists. Again, thanks for the help. Jon ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to query information schema from shell script
For more information, i know how this is done using the pg_catalog but have yet to find out how its done with the information schema. select * from pg_catalog.pg_database where datname='test' will work for me but i can't find a solution with the IS. I'd prefer to use the IS if its as easily done as with pg_catalog Jon ---(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: [SQL] not able to execute query on spatial database.
On 10/27/06, vamsee movva <[EMAIL PROTECTED]> wrote: Hello all, I am working with spatial tables. i have two spatial data tables, one represents the whole state and another table represents the damaged locations in the state. My aim is to find number of damaged locations in every county or parish. Here i am giving the query i used to do this, could you please tell me whether i am doing right thing or not. select count(*) from damagedlocations l1,county l2 where (l2.the_geom and l1.the_geom) and l2.parishid=particular_parishid; thanks in advance. vamsee movva What is your table structure? I think you mean (l2.the_geom = l1.the_geom) . If you use l2.parishid=particular_parishid then you are not going to get EVERY country or parish. Have you run this query? If so, what are you getting, is it right and if so why not? -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] How to query information schema from shell script
simplest might be psql -l. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Jon Horsman > Sent: Friday, October 27, 2006 8:13 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] How to query information schema from shell script > > In my original email i forgot to mentioned i need to know if the > database exists or not from the shell script. If it doesn't exist i > would then create it. Currently i was just creating the db everytime > our db script is run (since it doesn't hurt the db) but this generates > the 'db already exists' log and I now have the requirement that the > users not see that log. ie, don't run createdb if it already exists. > > In looking through the postgres docs i can see how to check if a table > exists but not how a db exists. > > Again, thanks for the help. > > Jon ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Function to reset sequence.....
I am a real newbie with PL/pgSQL, and with postgres, but here goes. The problem: when you import data into postgresql, there is no way to set the proper sequence on the primary key. For example, I could have a table with 2000 rows, some of which have been added/deleted in time, so the nextval for the sequence should be 3301. When you set the table up with sql as: CREATE TABLE "public"."tblcompany"( "intcompany" SERIAL PRIMARY KEY, "txtcompanyname_1" varchar(255) , ... ); The nextval is set by default to 1. To overcome this, and avoid manually resetting the key (which is error prone), my strategy is (1) create the structure, (2) import the data, and then (3) reset the primary key. I have written sql to accomplish the first two which I have working well; the third is more complicated. What I would like to try is pasted below, after messing around - I haven't really got it even close to working: CREATE OR REPLACE FUNCTION fixsequence() returns void AS $$ BEGIN x RECORD; temp int; -- set up a loop for the tables in the database FOR x IN SELECT table_name, column_name FROM information_schema.key_column_usage NATURAL JOIN information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY' AND ordinal_position = 1 ORDER BY 1; -- begin loop LOOP -- get the max value of the primary key and add 1 select max(x.column_name)+1 as temp from x.tablename; -- get the seqence name for the table, sequence name always includes the table name of the pimary key select relname as seq_name from pg_class where relkind = 'S' and relname like x.table_name'%'; -- now reset the sequence for that table SELECT setval(seq_name, temp); END LOOP; END; $LANGUAGE 'plpgsql'; Before I mess up my data, will this (or something like it work) as I have little confidence? I am having trouble with combining variables with wildcards (middle of the loop). Thanks for any support. Doug ---(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: [SQL] How to query information schema from shell script
On Fri, 2006-10-27 at 10:12, Jon Horsman wrote: > In my original email i forgot to mentioned i need to know if the > database exists or not from the shell script. If it doesn't exist i > would then create it. Currently i was just creating the db everytime > our db script is run (since it doesn't hurt the db) but this generates > the 'db already exists' log and I now have the requirement that the > users not see that log. ie, don't run createdb if it already exists. > > In looking through the postgres docs i can see how to check if a table > exists but not how a db exists. > > Again, thanks for the help. Oh, and read this: http://www.gnu.org/software/bash/manual/bashref.html It's a good starters manual for bash / shell programming. For more advanced tutorials, look at: www.deadman.org/bash.html www.tldp.org/LDP/abc/html/index.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Case Preservation disregarding case sensitivity?
Hello all.
As this is my first post to this particular mailing list, let me preface this
with a couple of items:
1) I have, indeed, searched far and wide for the answer to my question. I have
read and understood the responses to similar posts posing similar questions.
My next stop after this is the postgresql source code.
2) I develop database driven applications for telephony and mobile
applications in C/C++, perl and python. The databases I am working with
exceed 100GB. We have several production database systems.
3) I am pushing hard to replace our MySQL installations with PostgreSQL
(open-source and/or commercial) because Postgres has performed better by
several orders of magnitude and has been more stable and as consumed half the
resources in my testing.
The behavior I am seeing in Postgres in my development and migration process
is a showstopper. The manner in which Postgres handles case-sensitivity
breaks the current tools that I use and the applications that have been
written.
I am aware of the double-quote 'feature' which indicates that an element
should be treated in a case-sensitive way. This as been the 'answer' to every
question of this sort. This 'feature' does not solve the problem and
introduces other problems.
I have a data set that is designed in such a way as:
CREATE TABLE user_profile (
userProfileId int8 not null primary key,
userProfileTypeId int8 not null
);
CREATE TABLE user_profile_type (
userProfileTypeId int8 not null primary key,
userProfileType varchar not null
);
Ok, the description of the table from the database:
Table "public.user_profile"
Column | Type | Modifiers
---++---
userprofileid | bigint | not null
userprofiletypeid | bigint | not null
Indexes:
"user_profile_pkey" PRIMARY KEY, btree (userprofileid)
Ok, adding a foreign key:
ALTER table user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
(userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
...works successfully.
Works as it is supposed to work. Postgres is supposed to be case sensitive,
which is not a problem, but I want it to preserve the case of the column
names.
Every search has turned up the suggestion of using double-quotes around
identifiers in the creation of the tables. So we'll try that:
DROP TABLE user_profile;
DROP TABLE user_profile_type;
CREATE TABLE user_profile (
"userProfileId" int8 not null primary key,
"userProfileTypeId" int8 not null
);
CREATE TABLE user_profile_type (
"userProfileTypeId" int8 not null primary key,
"userProfileType" varchar not null
);
The table description:
Table "public.user_profile"
Column | Type | Modifiers
---++---
userProfileId | bigint | not null
userProfileTypeId | bigint | not null
Indexes:
"user_profile_pkey" PRIMARY KEY, btree ("userProfileId")
Case was preserved. Now lets add the foreign key just as we did before (note
that the case in the table definition and the ALTER TABLE query is the same):
ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
(userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
ERROR: column "userprofiletypeid" referenced in foreign key constraint does
not exist
The case was preserved, but the case-insensitivity only works one way. The
case of the input identifier was 'normalized' in a way, but not matched
against the column names in a case-insensitive way. This is the behavior that
breaks the tools that I use. I cannot modify these tools to add quotes around
the identifiers, and in my mind I should not have to.
OK, another query (perfectly valid SQL):
insert into user_profile_type
(userProfileTypeId,userProfileType) VALUES(1,'ABNORMAL');
ERROR: column "userprofiletypeid" of relation "user_profile_type" does not
exist
I am hoping that there is an easy way to obtain case-preservation with
case-insensitivity, or at the very least, case-preservation and complete
case-sensitivity, or case-preservation and a consistant case-conversion
strategy.
The case of the column names need to be preserved because that is the way the
schema is designed and most importantly (VERY, VERY IMPORTANT), column names
are used in apps as hash values, or as named references which are case
sensitive and as such need to be delivered to the client in exactly in the
manner specified at the time of table creation.
Again, I am looking for a way (magic, patches, whiskey, etc) that will give me
case-preservation with EITHER case-sensitivity OR case-insensitivity, but not
both as I am seeing.
Thanks in advance. I am hoping to find a solution to this so I can actually
convert one of our databases to use Postgres. And I can say that little
issues like this are precisely why Postgres was never used in this
organization before, even though several of the other database developers
like the features, stability and perf
Re: [SQL] Case Preservation disregarding case sensitivity?
At 07:23 PM 10/27/06, beau hargis wrote:
I am aware of the double-quote 'feature' which indicates that an element
should be treated in a case-sensitive way. This as been the 'answer' to every
question of this sort. This 'feature' does not solve the problem and
introduces other problems.
If you use double-quotes when creating the table, you need to use
double-quotes EVERY time you access those elements. Neither of your two
examples (that produced errors) have double quotes.
ALTER TABLE user_profile ADD CONSTRAINT fk_uproftype FOREIGN KEY
(userProfileTypeId) REFERENCES user_profile_type (userProfileTypeId);
ERROR: column "userprofiletypeid" referenced in foreign key constraint does
not exist
insert into user_profile_type
(userProfileTypeId,userProfileType) VALUES(1,'ABNORMAL');
ERROR: column "userprofiletypeid" of relation "user_profile_type" does not
exist
The second query should be:
insert into user_profile_type ("userProfileTypeId","userProfileType")
VALUES(1,'ABNORMAL');
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Case Preservation disregarding case sensitivity?
Hi Beau, On Fri, 2006-10-27 at 16:23 -0700, beau hargis wrote: > I am hoping that there is an easy way to obtain case-preservation with > case-insensitivity, or at the very least, case-preservation and complete > case-sensitivity, or case-preservation and a consistant case-conversion > strategy. > > The case of the column names need to be preserved because that is the way the > schema is designed and most importantly (VERY, VERY IMPORTANT), column names > are used in apps as hash values, or as named references which are case > sensitive and as such need to be delivered to the client in exactly in the > manner specified at the time of table creation. > > Again, I am looking for a way (magic, patches, whiskey, etc) that will give > me > case-preservation with EITHER case-sensitivity OR case-insensitivity, but not > both as I am seeing. > > Thanks in advance. I am hoping to find a solution to this so I can actually > convert one of our databases to use Postgres. And I can say that little > issues like this are precisely why Postgres was never used in this > organization before, even though several of the other database developers > like the features, stability and performance of Postgres. I went through the same issue in my conversion from MySQL to Postgres and (since I had a small application) I ended up changing up all my tables and columns "UserProfile" to user_profile. I'm afraid however, that it's MySQL that is the odd man out. I haven't researched this completely but I believe PG follows either the FIPS-127 or SQL-92 standard with respect to what are called "delimited identifiers". Basically, this says if you want case sensitivity in identifier names, you have to use double quotes wherever you refer to the identifier. Without the double quotes, the SQL implementor can either use UPPERCASE (as I believe Oracle and DB2 do) or lowercase (as PG does) when it displays those identifiers. Joe ---(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: [SQL] How to query information schema from shell script
> In my original email i forgot to mentioned i need to know if the > database exists or not from the shell script. If it doesn't exist i > would then create it. Currently i was just creating the db everytime > our db script is run (since it doesn't hurt the db) but this generates > the 'db already exists' log and I now have the requirement that the > users not see that log. ie, don't run createdb if it already exists. > > In looking through the postgres docs i can see how to check if a table > exists but not how a db exists. > > Again, thanks for the help. psql -l Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
