[SQL] Query a select that returns all the fields of an specific value of primary key without knownig the name of the single column's primary key?
Hi! I need to query a select that returns all the fields of an specific primary key, but I don't have the single column's name that is constrained as primary key. How can I do that? Something like: SELECT * FROM myTable WHERE myTable.pkey = 'foo'; Thanks Carlos Henrique Iazzetti Santos Compels Informática Santa Rita do Sapucaí - MG www.compels.net ___ O Yahoo! está de cara nova. Venha conferir! http://br.yahoo.com
Re: [SQL] Autovaccum
Hi, Ezequias, "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> wrote: > What is the interval of time the vacuum will run on my database ? That depends on your settings and load. Autovacuum looks at every table, and checks the number of modifications, as well as the total number of transactions since the last vacuum, and decides whether to VACUUM and/or ANALYZE. The thresholds for those settings can be configured per table, with the defaults in the postgresql.conf. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] null values in non-nullable column
Hi, George, "George Pavlov" <[EMAIL PROTECTED]> wrote: > In 8.1 I have a situation where nullability of user defined datatypes > does not seem to be enforced. Using the following steps I end up with a > table that has a column that should not be nullable, but has nulls in > it. Ouch. That hurts! Now all those MySQL freaks can argue "Well, PostgreSQL does allow constraint violation, too.", despite the fact that most of them don't even basically understand the special case we have here. So, please, keep silent about it :-) How difficult is it to fix this (e. G. by disallowing NULL-generating JOINs on NOT NULL domains?) Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Query a select that returns....
Στις Τρίτη 19 Δεκέμβριος 2006 16:01, ο/η Carlos Santos έγραψε: > Hi! > I need to query a select that returns all the fields of an specific primary > key, but I don't have the single column's name that is constrained as > primary key. How can I do that? > Something like: > SELECT * FROM myTable WHERE myTable.pkey = 'foo'; First by SELECT pgc.conkey from pg_class pgcl,pg_constraint pgc where pgcl.relname='your table name' and pgcl.oid=pgc.conrelid and pgc.contype='p'; you get the attribute numbers of the primary key. Then you have to lookup pg_attribute to find the column names. In the simplified case where the primary key is consisted of only one attribute (column), then SELECT pgat.attname from pg_class pgcl,pg_constraint pgc,pg_attribute pgat where pgcl.relname='your table name' and pgcl.oid=pgc.conrelid and pgc.contype='p' and pgat.attrelid=pgcl.oid and attnum=pgc.conkey[1]; should give you the attribute name of the primary key. Then you build your query from your program accordingly. > > Thanks > > Carlos Henrique Iazzetti Santos > Compels Inform�tica > Santa Rita do Sapuca� - MG > www.compels.net > > > > > > > ___ > O Yahoo! est� de cara nova. Venha conferir! > http://br.yahoo.com -- Achilleas Mantzios ---(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] null values in non-nullable column
Yes, the thread did not seem to go very far. The SQL standard does seem inconsistent in this area, but that is not an argument for allowing data constraint violation. Until the standard is modified I think it would be good for the reputation of the DBMS we all love to come up with a fix... Even though, as Tom Lane explained, CREATE TABLE AS is not the problem here, it seems to me that might be the cleanest, least obtrusive place to add validation. If C.T.A failed at the table creation step because of the JOIN-produces NULLs that would be an early and decent warning. Fixing it from the JOIN side (e.g. disallowing NULL-generating JOINs on NOT NULL domains) seems too strict -- JOINs are mostly used for result sets that are not materialized and you hardly have the potential for a problem until they are materialized as a table. Similarly, removing the domain-ness of the JOIN column strikes me as too drastic and as having the potential of breaking existing functionality. I am sure I am missing something, just my two cents... George ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] null values in non-nullable column
George Pavlov wrote: Even though, as Tom Lane explained, CREATE TABLE AS is not the problem here, it seems to me that might be the cleanest, least obtrusive place to add validation. If C.T.A failed at the table creation step because of the JOIN-produces NULLs that would be an early and decent warning. Fixing it from the JOIN side (e.g. disallowing NULL-generating JOINs on NOT NULL domains) seems too strict -- JOINs are mostly used for result sets that are not materialized and you hardly have the potential for a problem until they are materialized as a table. Similarly, removing the domain-ness of the JOIN column strikes me as too drastic and as having the potential of breaking existing functionality. I am sure I am missing something, just my two cents... I think it has to go in the join... If a result-set has nulls in a particular column, that column can't be NOT NULL (by definition). Therefore, either the column has its not-null constraint removed (through type-casting away the domain) or the query fails on that NOT NULL constraint. Any query could result in this sort of problem, not just an explicit JOIN with NULLs. Imagine a domain "even_numbers_only" and a "SELECT my_even_numbers+1 FROM foo". Hmm - it strikes me that any result-set should perhaps have the domain removed and substituted with its parent type, except perhaps in the simplest "pass column through" case. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Desc table
Hi ALL I have a Quick/small Question. Well today is the first day for me in the Postgresql. I want to "describe table" can any one tell me how to do it in postgresql. thanks Suma Bommagani
Re: [SQL] Desc table
[EMAIL PROTECTED] wrote: Hi ALL I have a Quick/small Question. Well today is the first day for me in the Postgresql. I want to "describe table" can any one tell me how to do it in postgresql. If in psql, try \d See the psql section of the manual for details. Most GUI tools (e.g. pgadmin) off a column-view of a table. The system-catalogues and information_schema views are a way to describe tables programmatically. HTH -- Richard Huxton Archonet Ltd ---(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
[SQL] Help with quotes in plpgsql
How should this be properly quoted create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - interval '$1 day'; return next a; return; end $$ language 'plpgsql'; I'm not having a lot of luck Thanks Richard ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Help with quotes in plpgsql
Try select into a now() - interval ($1 || ' day') -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Ray Sent: Tuesday, December 19, 2006 3:10 PM To: pgsql-sql@postgresql.org Subject: [SQL] Help with quotes in plpgsql How should this be properly quoted create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - interval '$1 day'; return next a; return; end $$ language 'plpgsql'; I'm not having a lot of luck Thanks Richard ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(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] Help with quotes in plpgsql
Richard Ray написа: > How should this be properly quoted > > create or replace function test(integer) returns setof text as $$ > declare > a record; > begin > select into a now() - interval '$1 day'; > return next a; > return; > end > $$ language 'plpgsql'; > > I'm not having a lot of luck Welcome to psql 8.2.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit milen=> create or replace function test(integer) returns setof text as $$ milen$> declare milen$> a record; milen$> begin milen$> select into a now() - interval '$1 day'; milen$> return next a; milen$> return; milen$> end milen$> $$ language 'plpgsql'; CREATE FUNCTION milen=> No problems here. What version are you using? -- Milen A. Radev ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Help with quotes in plpgsql
Hi Not sure about $1 parms : you may need to use a variable to set $1 to then cast it as interval : create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - ($1::text||'days')::interval; return next a; return; end $$ language 'plpgsql'; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Ray Sent: Tuesday, December 19, 2006 12:10 PM To: pgsql-sql@postgresql.org Subject: [SQL] Help with quotes in plpgsql How should this be properly quoted create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - interval '$1 day'; return next a; return; end $$ language 'plpgsql'; I'm not having a lot of luck Thanks Richard ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Help with quotes in plpgsql
On Tue, 19 Dec 2006, Hector Villarreal wrote: Hi Not sure about $1 parms : you may need to use a variable to set $1 to then cast it as interval : create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - ($1::text||'days')::interval; return next a; return; end $$ language 'plpgsql'; This works quite well I never looked at it that way Thanks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Ray Sent: Tuesday, December 19, 2006 12:10 PM To: pgsql-sql@postgresql.org Subject: [SQL] Help with quotes in plpgsql How should this be properly quoted create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - interval '$1 day'; return next a; return; end $$ language 'plpgsql'; I'm not having a lot of luck Thanks Richard ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(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] Help with quotes in plpgsql
On Tue, 19 Dec 2006, Milen A. Radev wrote: Richard Ray : How should this be properly quoted create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - interval '$1 day'; return next a; return; end $$ language 'plpgsql'; I'm not having a lot of luck Welcome to psql 8.2.0, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit milen=> create or replace function test(integer) returns setof text as $$ milen$> declare milen$> a record; milen$> begin milen$> select into a now() - interval '$1 day'; milen$> return next a; milen$> return; milen$> end milen$> $$ language 'plpgsql'; CREATE FUNCTION milen=> No problems here. What version are you using? I'm using 8.1.0 but I don't think that's the problem I have no problem creating the function but it will only substract 1 day ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Help with quotes in plpgsql
am Tue, dem 19.12.2006, um 14:09:37 -0600 mailte Richard Ray folgendes: > How should this be properly quoted > > create or replace function test(integer) returns setof text as $$ > declare > a record; > begin > select into a now() - interval '$1 day'; > return next a; > return; > end > $$ language 'plpgsql'; > > I'm not having a lot of luck test=# create or replace function test(int) returns date as $$begin return current_date-($1||'days')::interval; end;$$ language plpgsql; CREATE FUNCTION test=# select test(4); test 2006-12-15 (1 row) test=# select test(3); test 2006-12-16 (1 row) 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 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] Help with quotes in plpgsql
Richard Ray написа: > On Tue, 19 Dec 2006, Milen A. Radev wrote: > >> Richard Ray : >>> How should this be properly quoted >>> >>> create or replace function test(integer) returns setof text as $$ >>> declare >>> a record; >>> begin >>> select into a now() - interval '$1 day'; >>> return next a; >>> return; >>> end >>> $$ language 'plpgsql'; >>> >>> I'm not having a lot of luck >> >> >> Welcome to psql 8.2.0, the PostgreSQL interactive terminal. >> >> Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >> milen=> create or replace function test(integer) returns setof text as $$ >> milen$> declare >> milen$> a record; >> milen$> begin >> milen$> select into a now() - interval '$1 day'; >> milen$> return next a; >> milen$> return; >> milen$> end >> milen$> $$ language 'plpgsql'; >> CREATE FUNCTION >> milen=> >> >> >> >> No problems here. What version are you using? >> > > I'm using 8.1.0 but I don't think that's the problem > I have no problem creating the function but it will only substract 1 day Sorry about that - I have not understand your problem. In addition to the solution already proposed you could use "EXECUTE". See more info here - http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN . -- Milen A. Radev ---(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] Help with quotes in plpgsql
In case it is a version issue: This should always work regardless of version: Just cast the $1 variable as text followed by interval: create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - ($1::text||'days')::interval; return next a; return; end $$ language 'plpgsql'; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Milen A. Radev Sent: Tuesday, December 19, 2006 2:54 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Help with quotes in plpgsql Richard Ray написа: > On Tue, 19 Dec 2006, Milen A. Radev wrote: > >> Richard Ray : >>> How should this be properly quoted >>> >>> create or replace function test(integer) returns setof text as $$ >>> declare >>> a record; >>> begin >>> select into a now() - interval '$1 day'; >>> return next a; >>> return; >>> end >>> $$ language 'plpgsql'; >>> >>> I'm not having a lot of luck >> >> >> Welcome to psql 8.2.0, the PostgreSQL interactive terminal. >> >> Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >> milen=> create or replace function test(integer) returns setof text as $$ >> milen$> declare >> milen$> a record; >> milen$> begin >> milen$> select into a now() - interval '$1 day'; >> milen$> return next a; >> milen$> return; >> milen$> end >> milen$> $$ language 'plpgsql'; >> CREATE FUNCTION >> milen=> >> >> >> >> No problems here. What version are you using? >> > > I'm using 8.1.0 but I don't think that's the problem > I have no problem creating the function but it will only substract 1 day Sorry about that - I have not understand your problem. In addition to the solution already proposed you could use "EXECUTE". See more info here - http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN . -- Milen A. Radev ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help with quotes in plpgsql
"Hector Villarreal" <[EMAIL PROTECTED]> writes: >select into a now() - ($1::text||'days')::interval; People keep suggesting variants of that as ways to convert numeric values to intervals, but it's really extremely bad practice. Much better is to use number-times-interval multiplication: select into a now() - $1 * '1 day'::interval; This is less typing, at least as easy to understand, more flexible (you can use any scale factor you want), and considerably more efficient. The first way involves coercing the integer to text, then text-concatenating that with a constant, then applying interval_in which does a fairly nontrivial parsing process. The second way is basically just a multiplication, because '1 day'::interval is already a constant value of type interval. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Fetching BLOBs
Hi team I have a very intersting question to all of you. Pls help me to build this query, I want to fetch more that 70,000 BLOB from different customer servers. the issue is there are some BOLB files with common names on all servers. So I want merge these files into a single BLOB during fetching data. I am able to fetch the BLOB data from all cust servers but unfortunatelly it overwrite previous file. So pls provide any simple query format for the same, assuming two table tab1 & tab 2. Ashish . INDIA
Re: [SQL] Fetching BLOBs
Ashish Ahlawat wrote: Hi team I have a very intersting question to all of you. Pls help me to build this query, I want to fetch more that 70,000 BLOB from different customer servers. the issue is there are some BOLB files with common names on all servers. So I want merge these files into a single BLOB during fetching data. I am able to fetch the BLOB data from all cust servers but unfortunatelly it overwrite previous file. The lo_export() function makes you supply the filename, so you can name the destination file however you like. I must be misunderstanding you - can you provide an example of what you're doing at the moment? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
