[SQL] id and ID in CREATE TABLE
Hello all, It might be not a correct place to post this. I am creating a table from psql. Everything is fine except I got some troubles when trying to create the same table but in a different way and with pgaccess. If I have this sql: CREATE TABLE ttt ( ID int2, name text ); from psql the ID comes into id in the table. The SQL statements work fine then. If I create the same table using pgaccess the table looks like: Table "ttt2" Column | Type | Modifiers +--+--- ID | smallint | name | text | After this if I INSERT and SELECT something the results are not the same anymore: TEST1=# INSERT INTO ttt2 VALUES (1,'l'); INSERT 17001 1 TEST1=# select * from ttt2; ID | name +--- 1 | l (1 row) TEST1=# select ID from ttt2; ERROR: Attribute 'id' not found Can somebody explain me a bit about: 1. As far as I know column names in Tables are not case sensitive. Correct ? So I know if I pickup ID is not a clever idea but for this example it is ok. As well if I have name and Name it should not matter for SQL. 2. Why psql converts from upper case in lower case column name ID ? Like in the first case. 3. How comes that first using psql I cannot create the column name ID but using pgaccess I can ? Is this a bug ? Some ideas ? stefan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] id and ID in CREATE TABLE
I forgot about "" Sorry. So if I would use names quoted then my questions are obsolete. Except one: So actually the only strange part would be PostgreSQL is folding to lower cases a column name ... >From docs: The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it. Why is like this ? Why not letting them upper case if they are not quoted ? stefan On Sat, 20 Jul 2002 [EMAIL PROTECTED] wrote: > > Hello all, > > It might be not a correct place to post this. I am creating a table from > psql. Everything is fine except I got some troubles when trying to create > the same table but in a different way and with pgaccess. > > If I have this sql: > > > CREATE TABLE ttt ( > > ID int2, > name text ); > > from psql the ID comes into id in the table. The SQL statements work fine > then. If I create the same table using pgaccess the table looks like: > > Table "ttt2" > Column | Type | Modifiers > +--+--- > ID | smallint | > name | text | > > > After this if I INSERT and SELECT something the results are not the same > anymore: > > TEST1=# INSERT INTO ttt2 VALUES (1,'l'); > INSERT 17001 1 > TEST1=# select * from ttt2; > ID | name > +--- > 1 | l > (1 row) > > TEST1=# select ID from ttt2; > ERROR: Attribute 'id' not found > > Can somebody explain me a bit about: > > 1. As far as I know column names in Tables are not case sensitive. Correct ? > So I know if I pickup ID is not a clever idea but for this example it is ok. > As well if I have name and Name it should not matter for SQL. > > 2. Why psql converts from upper case in lower case column name ID ? > Like in the first case. > > 3. How comes that first using psql I cannot create the column name ID but > using pgaccess I can ? Is this a bug ? > > > Some ideas ? > stefan > > > ---(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] [GENERAL] id and ID in CREATE TABLE
Thanks all for comments. In Oracle and DB2 as far as I know the upper case column names are used when you have no columns quoted.. Indeed it is recommended to use lower cases to avoid mistakes and confusion during porting. Is that right ? But anyway this is not so important, but why upper cases are bad ? Why then Oracle , IBM is using them and why the SQL standard is not changed ? stefan On Fri, 19 Jul 2002, Martijn van Oosterhout wrote: > On Sat, Jul 20, 2002 at 10:39:52AM +0300, [EMAIL PROTECTED] wrote: > > > > I forgot about "" Sorry. So if I would use names quoted then my questions > > are obsolete. Except one: > > > > So actually the only strange part would be PostgreSQL is folding to lower > > cases a column name ... > > [snip] > > > Why is like this ? Why not letting them upper case if they are not quoted > > ? > > I think it's because many people think that uppercase column names suck. And > I agree with them. If you follow the given advice (either always quote > column names or never) then not only will your program work, it's will be > completely portable. > > So, if you'd used your CREATE TABLE statememnt below, you wouldn't have had > this problem. > > > > CREATE TABLE ttt ( > > > > > > ID int2, > > > name text ); > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL]unsubscribe
unsubscribe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] unsubscribe
unsubscribe Stefan Ionita ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] system maintained keys
Is there any way to get system maintained keys from postgres? e.g. to have a table with a primary key column (varchar or int) and let postgres chose the next unique value for this column? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] system maintained keys
Thank you very much! Thanks to all who answered! An I will never again ask silly questions here :-) "Stefan Lindner" <[EMAIL PROTECTED]> schrieb im Newsbeitrag 9qf06v$20bh$[EMAIL PROTECTED]">news:9qf06v$20bh$[EMAIL PROTECTED]... > Is there any way to get system maintained keys from postgres? e.g. to > have a table with a primary key column (varchar or int) and let postgres > chose the next unique value for this column? > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] loop query results
With the following function I expected to get an iteration through the results. But the loop seems to be performed two times instead. The function (postgres 7.2): create function echt_char_laenge () returns integer as ' declare char_auslese record; begin for char_auslese in select * from varlaenge loop raise notice ''%, %'', char_auslese.id_var, char_auslese.var_text; end loop; return 0; end; ' language 'plpgsql'; the result: select echt_char_laenge (); NOTICE: 1, text 1X NOTICE: 2, text 2X NOTICE: 3, text 3X NOTICE: 4, text 4X NOTICE: 5, text 5X NOTICE: 6, text 6X NOTICE: 1, text 1X NOTICE: 2, text 2X NOTICE: 3, text 3X NOTICE: 4, text 4X NOTICE: 5, text 5X NOTICE: 6, text 6X echt_char_laenge -- 0 (1 row) -- and the result as expected: select * from varlaenge; id_var | var_text +- 1 | text 1X 2 | text 2X 3 | text 3X 4 | text 4X 5 | text 5X 6 | text 6X (6 rows) Any ideas? Thanx Stefan __ Schneller als andere! Die Blitz-SMS von WEB.DE FreeMail! http://freemail.web.de/features/?mc=021167 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] LEAST and GREATEST functions?
Hi, I know the LEAST and GREATEST functions are not part of standard SQL, but they sure were handy where I came from (Oracle-land). Has anyone written user-defined functions that do the same thing? Are there any plans to add these functions as part of a future version Postgres? Thanks, -Stefan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LEAST and GREATEST functions?
> Um, what's wrong with MAX and MIN, exactly? MIN and MAX are aggregate functions, LEAST and GREATEST are not. See the examples on the following table: foo A B - - 1 4 2 3 3 2 > SELECT LEAST(a, b), GREATEST(a, b) FROM foo; LEAST(a, b) GREATEST(a, b) --- -- 1 4 2 3 2 3 > SELECT MIN(a), MAX(b) FROM foo; MIN(a) MAX(b) -- -- 1 4 After further research, I found that the only way to have a function with a variable number of arguments is to create N number of overloaded functions, e.g. CREATE FUNCTION least(int)... CREATE FUNCTION least(int, int)... CREATE FUNCTION least(int, int, int)... ...etc... That sucks, especially since the underlying languages support variable arguments that will scale to who-knows-where (see varargs in C, *args in Python, for starters). Not only that, but I'd have to create another N number of functions for different datatypes (int, float, date, etc.). In addition to adding the LEAST and GREATEST functions, the PostgreSQL developers should add the ability to create user-defined functions with a variable number of arguments. Cheers, -Stefan __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LEAST and GREATEST functions?
It's good to hear this kind of discussion going on!
I solved my problem (for now) by creating a bunch of
overloaded LEAST and GREATEST functions, one for each
datatype. They only take two parameters, but that's
fine for what we're doing.
However, I ran into another, unrelated problem. I
created the LEAST and GREATEST functions as described
above, but when I tried to perform an update statement
comparing two timestamps, I ran into a problem:
UPDATE foo
SET my_timestamp_field =
LEAST(my_timestamp_field,
TO_TIMESTAMP('2003-07-01 12:34:56',
'-MM-DD HH24:MI:SS'))
WHERE ...
My LEAST function would not work because
my_timestamp_field has a datatype of "timestamp
without time zone", and the TO_TIMESTAMP(...) creates
a "timestamp *with* time zone". I could not find
anything in the documentation about this behavior. I
am running v7.3.2.
All help is appreciated, and please keep up the
discussion about the ability to create functions with
variable number of arguments (LEAST, GREATEST, etc.).
I could see the potential for wanting to write these
in the future.
Cheers,
-Stefan
__
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [postgres] Foreign Key
Hallo,
> > In der Tabelle status befinden sich Datensätze. Die Tabelle logins
> ist
> > leer. Nun versuche ich folgenden Befehl:
> >
> > ALTER TABLE "public"."login " ADD CONSTRAINT "test" FOREIGN KEY
> > ("status__id") REFERENCES "public"."status"("id")
> > ON DELETE RESTRICT
> > ON UPDATE RESTRICT
> > NOT DEFERRABLE;
> >
> > Jetzt bekomme ich immer die Fehlermeldung:
> > ERROR: Query was cancelled.
>
> hmmm, die Fehlermeldung ist seltsam und tritt normalerweise bei
> Abbruch zum
> Beispiel bei ^C im Frontent auf.
Ich tippe mal, das der PostgreSQL Manager da selber ein Timeout macht.
Habe es auch mal mit phpPgAdmin versucht. Der läuft sich Tod.
> Bei ALTER TABLE kann Postgres noch nicht alle Features; je älter die
> Version, desto weniger ;-)
Ich verwende die Version 7.3.4. Das sollte doch wohl reichen, oder?
> Evtl. sind auch die Anführungszeichen ein Problem.
Habe es auch ohne versucht, das gleiche Problem.
Ich werde es jetzt mal mit psql, direkt auf dem Server testen.
Vielleicht geht es ja dann.
Grüße,
Stefan
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [postgres] Foreign Key
Sorry für die letzte Mail. Habe bei der falschen Mail auf Antwort
geklickt.
Nochmals Sorry,
Stefan
> -Original Message-
> From: Stefan Sturm [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 13, 2003 12:48 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [SQL] [postgres] Foreign Key
>
> Hallo,
>
> > > In der Tabelle status befinden sich Datensätze. Die Tabelle logins
> > ist
> > > leer. Nun versuche ich folgenden Befehl:
> > >
> > > ALTER TABLE "public"."login " ADD CONSTRAINT "test" FOREIGN KEY
> > > ("status__id") REFERENCES "public"."status"("id")
> > > ON DELETE RESTRICT
> > > ON UPDATE RESTRICT
> > > NOT DEFERRABLE;
> > >
> > > Jetzt bekomme ich immer die Fehlermeldung:
> > > ERROR: Query was cancelled.
> >
> > hmmm, die Fehlermeldung ist seltsam und tritt normalerweise bei
> > Abbruch zum
> > Beispiel bei ^C im Frontent auf.
>
> Ich tippe mal, das der PostgreSQL Manager da selber ein Timeout macht.
> Habe es auch mal mit phpPgAdmin versucht. Der läuft sich Tod.
>
>
> > Bei ALTER TABLE kann Postgres noch nicht alle Features; je älter die
> > Version, desto weniger ;-)
>
> Ich verwende die Version 7.3.4. Das sollte doch wohl reichen, oder?
>
> > Evtl. sind auch die Anführungszeichen ein Problem.
>
> Habe es auch ohne versucht, das gleiche Problem.
>
> Ich werde es jetzt mal mit psql, direkt auf dem Server testen.
> Vielleicht geht es ja dann.
>
> Grüße,
> Stefan
>
>
> Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
> Sie eine E-Mail an:
> [EMAIL PROTECTED]
>
>
>
> Die Nutzung von Yahoo! Groups ist Bestandteil von
> http://de.docs.yahoo.com/info/utos.html
>
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [postgres] Foreign Key
Ich melde mich doch. Ich sitze hier halt uns arbeite. Ich arbeite aber
derzeit (außer der Formel1 Auswertung) nur an meinen Sachen. Und ich
mache das schon ganz schön Fortschritte.
Michelle hat gerade hier geklingelt. Sie kommt heute um ca. 19 Uhr zu
uns.
> -Original Message-
> From: Stefan Sturm [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 13, 2003 12:48 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [SQL] [postgres] Foreign Key
>
> Hallo,
>
> > > In der Tabelle status befinden sich Datensätze. Die Tabelle logins
> > ist
> > > leer. Nun versuche ich folgenden Befehl:
> > >
> > > ALTER TABLE "public"."login " ADD CONSTRAINT "test" FOREIGN KEY
> > > ("status__id") REFERENCES "public"."status"("id")
> > > ON DELETE RESTRICT
> > > ON UPDATE RESTRICT
> > > NOT DEFERRABLE;
> > >
> > > Jetzt bekomme ich immer die Fehlermeldung:
> > > ERROR: Query was cancelled.
> >
> > hmmm, die Fehlermeldung ist seltsam und tritt normalerweise bei
> > Abbruch zum
> > Beispiel bei ^C im Frontent auf.
>
> Ich tippe mal, das der PostgreSQL Manager da selber ein Timeout macht.
> Habe es auch mal mit phpPgAdmin versucht. Der läuft sich Tod.
>
>
> > Bei ALTER TABLE kann Postgres noch nicht alle Features; je älter die
> > Version, desto weniger ;-)
>
> Ich verwende die Version 7.3.4. Das sollte doch wohl reichen, oder?
>
> > Evtl. sind auch die Anführungszeichen ein Problem.
>
> Habe es auch ohne versucht, das gleiche Problem.
>
> Ich werde es jetzt mal mit psql, direkt auf dem Server testen.
> Vielleicht geht es ja dann.
>
> Grüße,
> Stefan
>
>
> Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
> Sie eine E-Mail an:
> [EMAIL PROTECTED]
>
>
>
> Die Nutzung von Yahoo! Groups ist Bestandteil von
> http://de.docs.yahoo.com/info/utos.html
>
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] [postgres] Foreign Key
Hallo zusammen,
ich würde gerne folgenden Foreign Key in meine DB einbauen. Leider
bekomme ich immer eine Fehlermeldung.
Hier die Situation:
Ich habe 2 Tabellen:
status:
ID Numeric
Name Text
logins:
ID Numeric
Name Text
Status__id Numeric
In der Tabelle status befinden sich Datensätze. Die Tabelle logins ist
leer. Nun versuche ich folgenden Befehl:
ALTER TABLE "public"."login " ADD CONSTRAINT "test" FOREIGN KEY
("status__id") REFERENCES "public"."status"("id")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE;
Jetzt bekomme ich immer die Fehlermeldung:
ERROR: Query was cancelled.
Und das war auch schon alles.
Kann mir da einer Helfen?
Danke und Grüße,
Stefan Sturm
PS: Ich mache das ganze mit dem Programm EMS PostgreSQL Manager Version
1.8.0.1
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] [postgres] Foreign Key
Hallo, > Hallo, > > > Ich tippe mal, das der PostgreSQL Manager da selber ein Timeout > macht. > > Habe es auch mal mit phpPgAdmin versucht. Der läuft sich Tod. > > Ist die Tabelle groß? Da kann es natürlich sein, dass da ein ganzer > Haufen > an Updates gemacht werden muss, und dass gerade dies in diesem Falle > überhaupt nicht optimiert ist. > > Zumindest muss ja ein Test auf die References-Tabelle gemacht werden. > Sind > da entsprechende Indexe drauf? Evtl. hilft da auch das Clustern. Die Tabelle ist sehr klein. Max. 20 Datensätze. Ist halt noch in der Entwicklung. Direkt auf der Datenbank mit psql hat es auf Anhieb geklappt. Hat keine Sekunde gedauert. Jetzt frage ich mich, warum? Grüße, Stefan Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Counting rows from two tables in one query
Hi. I have a (simplified) table layout like this: +-+ +-+ | sub_a | +--+ | sub_b | +-+ | main | +-+ | id | +--+ | id | | main_id | > | id | < | main_id | | ... | | ... | | ... | +-+ +--+ +-+ What I am trying to get is a list that shows how many records from 'sub_a' and 'sub_b' are referencing 'main': main_id | count_a | count_b -+-+- 1 | 2 | 1 2 | 12 | 1 3 | 7 | 3 [..] This query obviously does not do what I need, it gives me the product of count_a and count_b in both columns instead: select main.idas main_id, count(sub_a.*) as count_a, count(sub_b.*) as count_b from main, sub_a, sub_b where sub_a.main_id = main.id and sub_b.main_id = main.id group by main.id having count(sub_a.*) > 0 and count(sub_b.*) > 0 ; Is it possible to get a list like the one above with a single query? thanks, stefan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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] Formatting problems with negative intervals, TO_CHAR
On Monday, 07 June 2004 09:52, Karel Zak wrote:
> http://www.postgresql.org/docs/7.4/static/functions-formatting.html
>
> Warning: to_char(interval, text) is deprecated and should not be
> used in newly-written code. It will be removed in the next version.
This is news for me. Are there any suggestions what we should replace
TO_CHAR with? For example, we were using TO_CHAR to print timestamp
values in ISO format without milliseconds ("-MM-DD HH24:MI:SS" style),
regardless of the current datestyle setting.
I see only three solutions to do this without using TO_CHAR, and IMO none
of them are very attractive:
- do the formatting at the application level,
- change the datestyle for this query only
- an ugly construct using 6 EXTRACT functions
I hope there is a more elegant way to solve this...
Why was TO_CHAR deprecated anyway? It seemed to me like a very useful and
flexible way to do date/time formatting.
Tnx,
stefan
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] "=" operator vs. "IS"
Hi. I'm just curious - why is it not possible to use the "=" operator to compare values with NULL? I suspect that the SQL standard specified it that way, but I can't see any ambiguity in an expression like "AND foo.bar = NULL". Is it because NULL does not "equal" any value, and the expression should be read as "foo.bar is unknown"? Or is there something else I'm missing? If it's the "unknown" part, then why can't I use "=" to compare with TRUE or FALSE? cheers, stefan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: FW: [SQL] "=" operator vs. "IS"
Re, thanks for all the replies. On Tuesday, 29 June 2004 00:17, Dmitri Bichko wrote: > As far as TRUE and FALSE go, from what I know you can use = to compare > them with boolean columns, unless I misunderstood your question. Sorry, I must have remembered that incorrectly, or maybe I've been thinking of a different DBMS or version. "table.col = TRUE" seems to work fine for me now. I understand that the result of "(anything) = NULL" will always NULL, so it's a waste of breath. That's where the "IS" operator(?) comes in, which allows for comparison with NULL. The only question left is why "IS" can also be used to compare with the TRUE/FALSE keywords (when a simple "=" should be sufficient here), but not to compare two boolean columns. cheers, stefan BTW, I really liked Mike's explanation that "it just IS" ;-) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] A transaction in transaction? Possible?
On Wednesday, 10 November 2004 18:28, Tom Lane wrote: > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > Just a very naive thought > > Wouldn't make more sense to allow nested begin/commit/rollback blocks? > > We actually had it working that way initially, but changed to the > spec-defined behavior, because (a) it wasn't standard, and (b) it > was confusing. See the pghackers archives. We used to run into problems with nested transactions in scenarios like this: Imagine a database where you have a table for customers, and each customer can have (in a seperate table) several contacts; a contact can have one or more addresses, phone numbers, etc. These tables are connected by foreign keys, but without "on delete" triggers. The frontend application has a function for deleting a contact, which works something like this: * begin transaction * delete the contact's addresses, phone numbers, etc * ... * delete the contact record itself * commit Then there is a function for deleting a customer: * begin transaction * for all contacts, call the "delete contact" function * ... * delete the customer record itself * commit At the moment the application is "simulating" support for nested transactions: We use a wrapper for the BEGIN and COMMIT calls, and an internal counter, which is incremented for each BEGIN. Only the first BEGIN gets sent to the backend. When COMMIT has been called as many times as BEGIN, we send a real commit (errors and ROLLBACK are handled too, of course). It's not perfect, but it does what we need. Savepoints are a nice feature, but I don't think they could help us here. cheers, stefan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] A transaction in transaction? Possible?
On Thursday, 11 November 2004 09:23, Gaetano Mendola wrote: > Stefan Weiss wrote: > > These tables are connected by foreign keys, but without "on delete" > > triggers. > > Why "without" ? Are you looking to solve a problem introduced by > yourself ? There are numerous checks involved before a customer (or a contact) can be deleted, and not all of these checks can be done by querying the database. Sometimes triggers aren't enough. > You can handle this task using the new functionality introduced with > savepoint: the exception. For more information look at: > http://candle.pha.pa.us/main/writings/pgsql/sgml/plpgsql-control-structures >.html#PLPGSQL-ERROR-TRAPPING The "delete contact" and "delete customer" functions are not stored procedures, so I'm not sure how this can help. thanks, stefan ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Links between rows in a table
Hi. We are currently designing a web-based application in which users can add other users as "friends". These links are bi-directional, meaning that when A adds B to his friends, he is automatically one of B's friends. Eventually we will have to add a feature that shows how A is is related to some other user E (via B->C->D->...) - similar to the way Friendster, Orkut and others work, but on a much smaller scale (some 5000 users). Probably the most annoying part is that it has to work with different database vendors, including MySQL4 (default install, MyISAM tables, no foreign keys, no stored procedures, no triggers, no views etc). Most of the logic will have to live in the application, and I won't be able to use anything beyond plain SQL. I can see several ways how such links could be modeled in a relational database, but I was wondering if there was some tried-and-true recipe that would spare me from reinventing the wheel. Putting aside for the moment everything but the links, the simplest way of connecting users would be a "friends" table (user_id int, friend_id int). We could get a user's friends with a simple query like this: SELECT friend_id FROM friends WHERE user_id = X UNION SELECT user_id FROM friends WHERE friend_id = X; Is there a better way, or any reason why we should not go that way, especially considering other likely queries such as "friends of friends" or the connection chain mentioned above? We are also thinking of precalculating possible connection chains, or trees, at night (to a certain depth) in order to avoid performance problems in the peak hours. Any ideas on how such precalculated results could be stored and queried efficiently? Thanks in advance, Stefan Weiss ---(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] Links between rows in a table
On 2005-03-06 18:42, Bruno Wolff III wrote:
>> We are currently designing a web-based application in which users can
>> add other users as "friends". These links are bi-directional, meaning
>> that when A adds B to his friends, he is automatically one of B's
>> friends. Eventually we will have to add a feature that shows how A is
>
> This doesn't seem like a good idea unless the person getting linked to
> gets to confirm he wants the link creator as a friend.
Yes, we have an invitation/pending/confirm process, and users are also
able to block other users. I haven't mentioned this because I did not
think it relevant to the storage question. There is a different system
for unilateral friendships ("favorites/fans").
>> SELECT friend_id FROM friends WHERE user_id = X
>> UNION SELECT user_id FROM friends WHERE friend_id = X;
>
> It would probably be better to always have either both or neither of
> the symmetric relationships in the table. You could make a set of triggers
> to enforce this.
We have also considered this, but since "friendship" in this application
is mutual by definition, wouldn't that just lead to data duplication? We
might still insert two rows instead of one, if we find that the union
slows things down more than the larger table, or if the "connection
finder" feature will be easier to implement that way.
By the way, according to the MySQL documentation, "Rudimentary support
for triggers is included beginning with MySQL 5.0.2". The MySQL
compatibility requirement is none of my doing, I have given up trying to
educate my customers about the benefits of a real database...
regards,
stefan weiss
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Links between rows in a table
On 2005-03-06 20:26, PFC wrote: > Because your relation is symmetric, you should not name them "user" and > > "friend". A good point, thank you. > user_id_1 < user_id_2 means : > - a user can't be his own friend > - only one row per friend > - when you want to know if A is friend of B, no need to make two > selects, > just select where user_id_1 = min(user_id_A, user_id_B) AND user_id_2 = > max(user_id_A, user_id_B) This is what we were planning to do on the application side, but a CHECK constraint is even better. It will be used and enforced by those DB engines that understand it, and ignored by the one engine that doesn't. > To get the list of friends for a user, you still need the union, but > that > is no real problem. Making two queries will be marginally slower than one > query on a bigger table, but youu save precious cache space, so in the end > it could be faster. Thank you for your insight. We will rename the columns, add the CHECK and go ahead with this setup. regards, stefan weiss ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] customising serial type
Am Dienstag, 21. Juni 2005 12:01 schrieben Sie:
> hi,in a table with a serial datatype, how do i get the sequence to start at
> a specific number like 10?
Use START in the create sequence statement.
#
create sequence seq_xeingang increment 1 start 1000;
;
CREATE TABLE xeingang
(
idinteger default nextval('seq_xeingang'),
buchchar not null,
eingdt date not null,
jnr integer not null,
grp integer,
code integer not null
);
my best regards..
Stefan
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] ids from grouped rows
Lindsay schrieb: > Lets say i do this: > > SELECT name, MAX(age) > FROM Person > GROUP BY name > > This will group people who have the same name together, and return the > highest age for each group. I want to be able to find the id for the > person with the highest age in each group - > > Ideally, it would be something like this > > SELECT name, MAX(age), id_for_row_with_max_age > FROM Person > GROUP BY name SELECT name, age, id FROM Person RIGHT JOIN (SELECT name, MAX(age) AS m FROM Person GROUP BY name) AS t ON (Person.name=t.name AND Person.age=t.m) Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Avoid querying a specific field - how?
Hi, does anyone know how to build a query that SELECTs all fields but not a specific one? I have a couple of tables, holding statistical information as well as the geospatial one (via Postgis). When I query the table I'd like to query only the statistical part - not the geospatial, which is not necessary and slows done incredibly the query. I searched a bit within the Postgres SQL domain. It seems there is an EXCEPT statement, but it seems that it works differently... Thanks for any help, Stef ---(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] rowcount for all tables
i have a beginner question and i have read the FAQs. is there a faster ways than select count(*) from foo; to get the rowcount for the tables in my db ? Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] group by function, make SQL cleaner?
this should work,
#
SELECT date_trunc('day',endtime),count(*)
FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
GROUP BY 1
ORDER BY 1;
best regards,
Stefan
Am Donnerstag, 16. März 2006 06:18 schrieb Bryce Nesbitt:
> I've got a working query:
>
> stage=# SELECT date_trunc('day',endtime),count(*)
> FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
> GROUP BY date_trunc('day',endtime)
> ORDER BY date_trunc('day',endtime);
>
> date_trunc | count
> -+---
> 2006-02-01 00:00:00 | 253
> 2006-02-02 00:00:00 | 245
> 2006-02-03 00:00:00 | 231
> 2006-02-04 00:00:00 | 313
> 2006-02-05 00:00:00 | 285
> 2006-02-06 00:00:00 | 194
> 2006-02-07 00:00:00 | 229
> 2006-02-08 00:00:00 | 239
> 2006-02-09 00:00:00 | 250
> 2006-02-10 00:00:00 | 245
> 2006-02-11 00:00:00 | 275
>
> Is there a way to eliminate the ugly repeated use of
> date_trunc('day',endtime)?
>
>
> ---(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
--
email: [EMAIL PROTECTED]
tel : +49 (0)6232-497631
http://www.yukonho.de
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] regarding join
given this.
create table AA (id serial,name varchar(15));
create table BB (id serial,name varchar(15));
insert into AA (name) values ('1243f');
insert into AA (name) values ('asdfef');
insert into AA (name) values ('fdbsfd');
insert into AA (name) values ('btgrt');
insert into AA (name) values ('crregsewf');
insert into AA (name) values ('xedrgeef');
insert into BB (name) values ('243f');
insert into BB (name) values ('sdfef');
insert into BB (name) values ('dbsfd');
insert into BB (name) values ('tgrt');
insert into BB (name) values ('rregsewf');
insert into BB (name) values ('edrgeef');
you could try: (if you just need the one column "name")
select name from AA union select name from BB order by name;
a real nice way to go about this is:
create table CC (id serial,name varchar(15));
create table AA() inherits(CC);
create table BB() inherits(CC);
insert into AA (name) values ('1243f');
insert into AA (name) values ('asdfef');
insert into AA (name) values ('fdbsfd');
insert into AA (name) values ('btgrt');
insert into AA (name) values ('crregsewf');
insert into AA (name) values ('xedrgeef');
insert into BB (name) values ('243f');
insert into BB (name) values ('sdfef');
insert into BB (name) values ('dbsfd');
insert into BB (name) values ('tgrt');
insert into BB (name) values ('rregsewf');
insert into BB (name) values ('edrgeef');
=> select * from AA;
id | name
+---
1 | 1243f
2 | asdfef
3 | fdbsfd
4 | btgrt
5 | crregsewf
6 | xedrgeef
(6 rows)
=> select * from BB;
id | name
+--
7 | 243f
8 | sdfef
9 | dbsfd
10 | tgrt
11 | rregsewf
12 | edrgeef
(6 rows)
=> select * from CC order by name;
id | name
+---
1 | 1243f
7 | 243f
2 | asdfef
4 | btgrt
5 | crregsewf
9 | dbsfd
12 | edrgeef
3 | fdbsfd
11 | rregsewf
8 | sdfef
10 | tgrt
6 | xedrgeef
(12 rows)
by best regards,
Stefan
--
email: [EMAIL PROTECTED]
tel : +49 (0)6232-629542
länger klingeln lassen (Weiterleitung aktiv)
fax : +49 (0)6232-629544
http://www.net-away.de
Am Samstag, 25. März 2006 07:36 schrieb AKHILESH GUPTA:
> hi all,
> below I have created two tables in pgsql with field name as 'name' and 'id'
> as their datatype 'varchar(15)' and 'integer'.
>
> One of the table is:->
> chemical=> select * from test1;
> name | id
> ---+
> akhil | 1
> b | 2
> c | 3
> d | 4
> e | 5
> f | 6
> (6 rows)
>
> Another table is:->
> chemical=> select * from test3;
> name | id
> --+
> ab | 1
> cd | 2
> ef | 3
> gh | 4
> (4 rows)
>
> i want the output as:->
> name | id
> ---+
> akhil | 1 -from test1 table
> ab | 1--from test2 table
> b | 2-from test1 table
> cd | 2--from test2 table
> c | 3-from test1 table
> ef | 3--from test2 table
> d | 4-from test1 table
> gh | 4--from test2 table
> e | 5-from test1 table
> f | 6-from test1 table
>
> i have tried all the joins but it makes different fields for different
> tables.
> is there any way out for this kind of output??
> (plz reply asap)urgent.
>
> THANKS IN ADVANCE
>
> --
> Thanks & Regards,
> Akhilesh
> S/W Trainee (EDP),
> NUCHEM Pvt. Ltd.,
> Faridabad(Haryana)
> GSM:-(+919891606064)
>
> "FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Querying for name/value pairs in reverse
I'm no SQL expert by any means so I'm wondering if something like this is possible. I have two tables like this: create table Errors ( Id serial not null, CreateDate timestamp not null, primary key (Id) ); create table ErrorValues ( Id serial not null, ErrorId int not null, Name varchar(255) not null, Value text not null, primary key (Id), foreign key (ErrorId) references Errors (Id) ); Now, selecting specific ErrorValues with a bunch of names that are related to an Error is of course pretty simple. But I want to go the other way. I want to query for: 'give me all Errors that have the Code=1234 AND Address=1.2.3.4 AND Type=OSX Name/Value pairs' What is a good recipe to deal with this? Is something like this possible with standard sql? Is there a nice PG way to do this? Are there good books that cover real world stuff like this? So many questions from a SQL noob. S. ---(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
[SQL] Is there a solution for "SELECT OR INSERT"
given a simply table table like this:
create table MyTabl
(id serial,
lname varchar,
primary key (id)
)
I am trying to return the Primary-Key for a given
row regardless of the wanted row already being
in the table.
using something like:
select coalesce
(
(select id from MyTabl where lname='wanted'),
(insert into MyTabl (lname) values ('wanted') returning id)
) as id;
the above doesn't allow the insert..returning even thow
a similar select statement would be okay.
Is there a way to get the ID row OR create a new one in
ONE single statement?
my best regards and thank-you
for your time
Stefan
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Column "..." does not exist (view + union)
Assuming the following simple setup with two data tables, one mapping table, and one view - -- CREATE TABLE dossier ( id SERIAL NOT NULL PRIMARY KEY ); CREATE TABLE contact ( id SERIAL NOT NULL PRIMARY KEY, nameTEXT NOT NULL, firstname TEXT NULL ); CREATE TABLE dossier_contact ( dossier_id INTEGER NOT NULL REFERENCES dossier(id), contact_id INTEGER NOT NULL REFERENCES contact(id), ctype INTEGER NOT NULL, PRIMARY KEY (dossier_id, contact_id) ); CREATE VIEW dossier_contact_v AS SELECT dc.dossier_id, dc.contact_id, dc.ctype, (CASE WHEN c.firstname IS NOT NULL THEN c.name || ', ' || c.firstname ELSE c.name END) AS name FROM dossier_contact dc JOIN contact c ON c.id = dc.contact_id; -- - running this query - SELECT name FROM dossier_contact_v WHERE dossier_id = 56993 AND ctype = 234 UNION SELECT name FROM dossier_contact_v WHERE dossier_id = -1 AND ctype = -1 ORDER BY ctype; - fails with the following error message: ERROR: column "ctype" does not exist LINE 10: ORDER BY ctype; ^ The same query works fine without the ORDER BY, without the UNION, or when I select the "ctype" column in addition to "name". Why? Using an alias in the FROM clause gives a different error: SELECT x.name FROM dossier_contact_v x WHERE x.dossier_id = 56993 AND x.ctype = 234 UNION SELECT x.name FROM dossier_contact_v x WHERE x.dossier_id = -1 AND x.ctype = -1 ORDER BY x.ctype; ERROR: missing FROM-clause entry for table "x" LINE 10: ORDER BY x.ctype ^ I am using "PostgreSQL 8.4.10 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit". thanks, stefan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Column "..." does not exist (view + union)
On 2011-12-17 10:02, Andreas Kretschmer wrote: > Stefan Weiss wrote: >> >> SELECT name >> FROM dossier_contact_v >> WHERE dossier_id = 56993 >>AND ctype = 234 >> UNION >> SELECT name >> FROM dossier_contact_v >> WHERE dossier_id = -1 >>AND ctype = -1 >> ORDER BY ctype; >> >> - fails with the following error message: >> >> ERROR: column "ctype" does not exist >> LINE 10: ORDER BY ctype; > > The reult table doesn't contain a column "ctype", it contains only > "name". [...] I see. So this has to do with the union; after combining the two queries, the tables from the FROM clauses are no longer available. Thanks, that explains it. - stefan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Column "..." does not exist (view + union)
On 2011-12-17 22:36, Bèrto ëd Sèra wrote: >>I see. So this has to do with the union; after combining the two >>queries, the tables from the FROM clauses are no longer available. > > this has nothing to do with the UNION, but with the fact that the result > set is ordered after being produced, so you can order by any of its > elements, and only by that. You can actually order by calling them > acording to their position in the result set, like in: > > SELECT > relname, > relpages > FROM pg_class > ORDER BY 1; > > where 1 is actually the first element (no matter how it's called). The > table as such is never available to ORDER BY, no matter how simple your > query is. I know, but the problem only occurs when I want to sort by a column which hasn't been selected, and thus cannot be referred to by its index. For normal (non-union) queries, this is possible: SELECT relname FROM pg_class WHERE relhasindex ORDER BY relpages; In this trivial case, PostgreSQL knows where to look for "relpages". Not so in a union: SELECT relname FROM pg_class WHERE relhasindex UNION SELECT relname FROM pg_class WHERE relhasoids ORDER BY relpages; (ERROR: column "relpages" does not exist) I understand the error now (I think), and I know how to avoid it. thanks, stefan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] update column based on postgis query on anther table
Dear list,
This might be a postgis-specific question, but I could not get access to
the postgis mailing list so I will have a try here as my problem might
be related to SQL:
I need to update a column of a table based on a postgis-query function
that involves another table as follows:
Assuming I have the following two tables:
/* 1) point layer */
CREATE TABLE test1_point (
id serial PRIMARY KEY,
id_test1_poly integer);
SELECT AddGeometryColumn('test1_point','the_geom',32648,'POINT',2);
INSERT INTO test1_point values (
1,1,GeomFromText('POINT(91770.4424465354 2296241.06858129)',32648));
/* 2) polygon layer*/
CREATE TABLE test1_poly (
id serial PRIMARY KEY);
SELECT AddGeometryColumn('test1_poly','the_geom',32648,'POLYGON',2);
INSERT INTO test1_poly values (
22,GeomFromText('POLYGON((91755.2765951597
2296254.99925063,91787.796155 2296240.64800429,91757.7034700958
2296227.19771158,91755.2765951597 2296254.99925063))',32648));
/**/
And I create the following function to get the value 'id' from
'test1_poly' table:
/**/
create function test1_point_get_id_test1_poly(integer) returns integer
as 'select test1_poly.id
from test1_poly,test1_point
where ST_Within(
test1_point.the_geom,
test1_poly.the_geom)
and test1_point.id=$1;'
language SQL
returns null on null input;
/**/
This function works perfectly when I am using it manually like
/**/
select test1_point_get_id_test1_poly(1)
/**/
(returns '22', as the point from test1_point lies within the polygon of
test1_poly)
Now I want to use a trigger function to automatically update the column
'id_test1_poly' in tabel 'test1_point':
/**/
create or replace function test1_point_get_id_test1_poly() returns
trigger as $$
begin
new.id_test1_poly=test1_point_get_id_test1_poly(new.id);
return new;
end;
$$
language plpgsql volatile;
-- create trigger for function:
create trigger test1_point_get_id_test1_poly
after insert or update on test1_point for each row execute procedure
test1_point_get_id_test1_poly();
/**/
However, if I insert a new row into 'test1_point', the column
'id_test1_poly' remains empty, i.e. the function seems to return a null
value:
/**/
INSERT INTO test1_point (id,the_geom) values (
2,GeomFromText('POINT(91759.2587143761 2296245.93565599)',32648));
select * from test1_point where id=2
--(returns: 2;;"010120887F86AFB123F466F6405393C3F7DA844141")
Any ideas what is going wrong here? Thanks in advance for any help!
Stefan
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SOLVED: update column based on postgis query on anther table
Hi Igor, thank you so much, the trigger function that you provided is exactly what I was looking for. I already read/heard about the SELECT INTO statement but I never actually understood what it is needed for. Here I go ;-) Stefan -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how do I to generate a sequence Range or Set of integer constants
dear SQL friends, What I want to do might be done differantly. Right now I can't think of another solution other than a select statement I would like to create a sequence range of integer constants. Join this sequence against a ID Range in a database and look for missing Id's. Another application for this would be to simply populate a database with say 1000.. Records Now: Is there a syntax that allows for the following. create table XX (id int); insert into XX (select xx from "1 to 1000" of integers) or... select IntSeq.MissingValues, x.UniqIntId,x.A,x.B,x.C, from MyDataTable x left outer join ( select MissingValues from "1 to 1000" of integers ) IntSeq on MissingValues=x.UniqIntId I'm hoping that someone has done this and might be able to point to some function or methode to do this Thanks, Stefan Becker -- email: [EMAIL PROTECTED] tel : +49 (0)6232-497631 http://www.yukonho.de ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Selecting exactly one row for each column value
Am Dienstag, 6. März 2007 16:03 schrieb Florian Weimer: > a | b | c > ---+---+--- > 5 | 6 | 7 > 2 | 3 | 4 > 1 | 2 | 3 Hi, couldn't you accomplish this by: select distinct on (a) * from tablename order by a; here: create table tab (a int,b int,c int); insert into tab values (1,2,3); insert into tab values (5,6,7); insert into tab values (1,2,3); insert into tab values (2,3,4); insert into tab values (1,2,2); insert into tab values (2,3,4); select * from tab; a | b | c ---+---+--- 1 | 2 | 3 5 | 6 | 7 1 | 2 | 3 2 | 3 | 4 1 | 2 | 2 2 | 3 | 4 (6 rows) select distinct on (a) * from tab order by a; a | b | c ---+---+--- 1 | 2 | 3 2 | 3 | 4 5 | 6 | 7 (3 rows) my regards, Stefan -- email: [EMAIL PROTECTED] tel : +49 (0)6232-497631 http://www.yukonho.de ---(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
[SQL] unsubscribe
unsubscribe _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] unsubscribe
unsubscribe _ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] unsubscribe
unsubscribe [EMAIL PROTECTED] _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] ROW_NUMBER alias
I might be told off by some better SQL-User here on the list -
still here is my 2 Cents worth
> I needed ROW_NUMBER() in PostGresql and I did find the 'temporary sequence'
> method as a workaround and i think it at least gets the job done relatively
you use:
> CREATE TEMP SEQUENCE rownum;
> SELECT nextval('rownum') AS row_number , t.a, t.b, t.c
> FROM (
> SELECT a, b, c
> FROM table
> WHERE table_id = 973
> ORDER BY record_date
> ) t;
Doesn't this just return the 973th single record for the current
sequence order in your table?
I believe:
SELECT a, b, c FROM table
offset 973 limit 1
will accomplish the same result.
Stefan
--
email: [EMAIL PROTECTED]
tel : +49 (0)6232-497631
http://www.yukonho.de
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] show index from [table]
hi list, currently i am switching from mysql to pgsql, so i am a bit new to postgres' syntax. at the moment i am looking in postgres for something which is similar to SHOW INDEX FROM [table] in mysql. unfortunately i could not find anything satisfying relating to this issue. i have found out, that there is the -di option with psql. but actually i would need the information from within a (postgres) sql-query. is there a possibility to get information about the indices which have been created on a table? if there is not, it might be sufficient for me to get the create index strings, such like you get, when viewing a table in pgAdmin: -- Index: g_g114_b_idx -- DROP INDEX g_g114_b_idx; CREATE INDEX g_g114_b_idx ON g_g114 USING btree (b); and parse them manually. is that possible in any way? maybe there is something similar to SHOW CREATE TABLE (as in MySQL) in postgresql. thanks in advance, stefan ___ SMS schreiben mit WEB.DE FreeMail - einfach, schnell und kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Counting all rows
I need to get statistics from a bunch of tables. Simply the number of records in them. The query plan looks like this: => explain select count(id) from stuff; QUERY PLAN --- Aggregate (cost=1629.69..1629.70 rows=1 width=8) -> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8) The sequential scan kind of worries me. Is there a better/faster way to do this? S. S. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Counting all rows
On 6/23/07, Andreas Kretschmer <[EMAIL PROTECTED]> wrote: Stefan Arentz <[EMAIL PROTECTED]> schrieb: > I need to get statistics from a bunch of tables. Simply the number of > records in them. > > The query plan looks like this: > > => explain select count(id) from stuff; > QUERY PLAN > --- > Aggregate (cost=1629.69..1629.70 rows=1 width=8) >-> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8) > > The sequential scan kind of worries me. Is there a better/faster way to do > this? Yes. A "select count(*) from foo;" enforces a seq. scan. Solutions for this are discussed here: http://www.varlena.com/GeneralBits/120.php Thank you Andreas. That is exactly what I was looking for. S. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] strange result for select * from table where id=random()*900
hi list, this is my query: select * from _g2977 where id=floor(random() * (900)); in table _g2977 i have id(s) from 1 up to 900. i just want to select one random dataset by the above query but i does not work. actually i get sometime zero, sometimes one, sometimes two and sometimes even three results back from the above query although i thought it should give only one random dataset from the table. what is wrong? i appreciate any help. thanks in advance, stefan _ In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten! Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Speeding up schema changes
Is there a way to speed up simple schema changes like ... ALTER TABLE foo ADD COLUMN bar CHAR(64); ... where foo already contains millions of records? On a live database changes like this can take hours. Even when the database is idle. Is there a better way to do this? S. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Many databases
I'm in a strange situation where it makes more sense to give each user it's own database instead of having a single database with users and permissions. How does PG deal with that? Is it a problem to have have say a thousand small databases? S. ---(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] Many databases
Well I'm just toying with an idea. The problem I'm facing is that I would like clients to only see the tuples that they have created and own. I guess I'll just skip direct sql access to the db and write some API on top of it that manages the data. Not a big deal but it complicates things :-) S. On 9/22/07, Hiltibidal, Robert <[EMAIL PROTECTED]> wrote: > How is this possible? > > It smacks of a security issue which there are many proven solutions to > that. > > Would you elaborate? > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Stefan Arentz > Sent: Friday, September 21, 2007 5:39 PM > To: [email protected] > Subject: [SQL] Many databases > > I'm in a strange situation where it makes more sense to give each user > it's own database instead of having a single database with users and > permissions. How does PG deal with that? Is it a problem to have have > say a thousand small databases? > > S. > > ---(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 > > > PRIVILEGED AND CONFIDENTIAL > This email transmission contains privileged and confidential information > intended only for the use of the individual or entity named above. If the > reader of the email is not the intended recipient or the employee or agent > responsible for delivering it to the intended recipient, you are hereby > notified that any use, dissemination or copying of this email transmission is > strictly prohibited by the sender. If you have received this transmission in > error, please delete the email and immediately notify the sender via the > email return address or mailto:[EMAIL PROTECTED] Thank you. > > > > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] pg_clog (?) problem with VACUMM
Dear Gerardo and Pgsql-List, >When executing a VACUUM, i have this msg: >gse_new_version=# VACUUM ; >ERROR: could not access status of transaction 118 >DETAIL: could not open file "pg_clog/": No existe el fichero o el I had a similar problem. I decided one day to "clean" out The pg_clog/ directory of the database cluster. These files' "last modified time Stamps" on many files were quite old, so I deleted them. This didn't have any consequences for the data (dumps or indexes etc.) or the operation of the installation except, as I found out MUCH later found out - I could no longer vacuum the Database. I restored the missing files from a backup and everything was okay again, after a vacuum of the database the system cleaned out the older pg_log/ files on its own. I hope you get things fixed! My best regards, Stefan Becker Email: [EMAIL PROTECTED] ---(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
[SQL] INSERT INTO relational tables
Hi all
I'm new to SQL and I'm facing a problem I can't find any information about
(google, forums). By now I wonder if I understood something wrong about
relational DBs.
An example to explain my Problem:
Lets say I have a table containing information about the customer (name,
address, ...) and about his order (pieces, product-name, price). Because one
customer can order several products I split the table into two relational
tables to prevent redundancy:
tbl_customer (cust_id, cust_name, cust_address)
and
tbl_order (ord_pieces, ord_productname, ord_price, ord_customer REFERENCES
tbl_customer(cust_id))
Now I want to insert several new customers with several orders each at once. If
I had all information in one table, this would be easy with something like:
INSERT INTO tbl_customerorders (name, address, pieces, porductname, price)
VALUES ('MR. X', '1st street', 3, 't-shirts', 30), ('MR. X', '1st street', 5,
'books', 50), ('MRS. Y', '2nd street', 1, 't-shirt', 10),...
But how can I do this in one query if I split the table? I can add one new
customer, get his ID with curval() and then add his orders. But this won’t work
if I want to add several customers at once.
To read this information I can do a query with the argument WHERE
cust_id=ord_customer. I can create a VIEW doing this so I can read the data as
if it was stored in only one table. But is there in posgres/sql an
abstraction-layer that allows me to insert as if the information was stored in
one table? (Something like a VIEW that provides INSERT, UPDATE, … and
automatically inserts the referenced ID.)
Thanks for any help!
Stefan
--
Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] INSERT INTO relational tables
Thanks for your hints so far.
I'm looking for both syntax simplicity and referential integrity. I'm using c++
with libpqxx, but I didn't find a good object-relational mapper. And I'm too
lazy to implement a whole persistency layer as I don't have a huge DB with many
relations.
I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I created the
VIEW composing my table with customers and orders:
CREATE VIEW vw_customer_insert AS
SELECT * FROM tbl_customer, tbl_order
WHERE cust_id = ord_customer;
Then I added a rule for the inserting:
CREATE RULE rl_customer_insert AS
ON INSERT TO vw_customer_insert DO INSTEAD
( INSERT INTO tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address);
INSERT INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname,
NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"'));
);
But this results in the same problem: It works fine if I insert just one new
customer with one new order. But if I want to insert several new entries:
INSERT INTO vw_customer_insert(cust_name, cust_address, ord_pieces,
ord_productname, ord_price)
VALUES ), (‘MR. X’, ‘1st street’, 5, ‘books’, 50), (‘MRS. Y’, ‘2nd street’,
1, ‘t-shirt’, 10);
This doesn't work correctly; all orders are now related to the last customer.
In this example, both orders are related to MRS. Y because I use currval() in
my insert rule.
Do you see any solution for this? I thought that the performance of this would
be better than INSERTing to tbl_customer, fetching the ID and then do several
INSERTS to tbl_order in c++. But actually I tend to do it with several INSERT
statements in one transaction, as Steve proposed.
Cheers Stefan
--
Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] INSERT INTO relational tables
Original-Nachricht > Datum: Mon, 10 Dec 2007 12:29:15 +0100 > Von: "A. Kretschmer" <[EMAIL PROTECTED]> > An: [email protected] > Betreff: Re: [SQL] INSERT INTO relational tables > am Mon, dem 10.12.2007, um 12:08:48 +0100 mailte A. Kretschmer folgendes: > > am Mon, dem 10.12.2007, um 11:42:04 +0100 mailte Stefan Scheidegger > folgendes: > > > Thanks for your hints so far. > > > > > > I'm looking for both syntax simplicity and referential integrity. I'm > > > using c++ with libpqxx, but I didn't find a good object-relational > > > mapper. And I'm too lazy to implement a whole persistency layer as I > > > don't have a huge DB with many relations. > > > > > > I tried Andreas' suggestion with a RULE for INSERT on a VIEW. I > > > created the VIEW composing my table with customers and orders: > > > > > > CREATE VIEW vw_customer_insert AS SELECT * FROM tbl_customer, > > > tbl_order WHERE cust_id = ord_customer; > > > > > > Then I added a rule for the inserting: CREATE RULE rl_customer_insert > > > AS ON INSERT TO vw_customer_insert DO INSTEAD ( INSERT INTO > > > tbl_customer VALUES (DEFAULT, new.cust_name, new.cust_address); INSERT > > > INTO tbl_order VALUES (NEW.ord_pieces, NEW.ord_productname, > > > NEW.ord_price, currval('"tbl_cusomer_cust_id_seq"'));); > > > > > > But this results in the same problem: It works fine if I insert just > > > one new customer with one new order. But if I want to insert several > > > new entries: > > > > Insert the new customer only into the table if this customer doesn't > > exist there. If the customer exist in the table, obtain the id for this > > customer. > > > > > > > > simplified: > > > > - detect, if the customer exists > > yes: obtain the id as id > > no: insert and use currval() as id > > - insert the order with the id > > > > > > I would write a function for this. > > a little example: > > test=# create table customer (id serial primary key, name text); > NOTICE: CREATE TABLE will create implicit sequence "customer_id_seq" for > serial column "customer.id" > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "customer_pkey" for table "customer" > CREATE TABLE > test=*# create table orders (customer int references customer, val int); > CREATE TABLE > test=*# create or replace function orders_insert (text, int) returns int > as $$declare i int;begin select into i id from customer where name=$1; > if i is null then insert into customer (name) values ($1); select into i > currval('customer_id_seq'); end if; insert into orders values (i,$2); > return i; end; $$language plpgsql; > CREATE FUNCTION > test=*# select * from customer; > id | name > +-- > (0 rows) > > test=*# select * from orders ; > customer | val > --+- > (0 rows) > > test=*# select orders_insert('foo',1); > orders_insert > --- > 1 > (1 row) > > test=*# select orders_insert('foo',2); > orders_insert > --- > 1 > (1 row) > > test=*# select orders_insert('foo',3); > orders_insert > --- > 1 > (1 row) > > test=*# select orders_insert('bar',4); > orders_insert > --- > 2 > (1 row) > > test=*# select * from customer; > id | name > +-- > 1 | foo > 2 | bar > (2 rows) > > test=*# select * from orders ; > customer | val > --+- > 1 | 1 > 1 | 2 > 1 | 3 > 2 | 4 > (4 rows) > Thanks a lot, this is exactly what I was looking for. I realize I need to learn more about functions in SQL. Hopefully my questions will be more sophisticated next time ;-) Greets Stefan -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] REFERENCES and INHERITS restrictions?
Hi all
I’m confronted with the following problem:
I have a base table with several child tables which also use the parent’s
primary key as their own primary key:
CREATE TABLE tbl_parent
(
p_id serial NOT NULL,
p_time timestamp(6) with time zone NOT NULL DEFAULT '1970-01-01
01:00:00+01'::timestamp with time zone,
CONSTRAINT tbl_parent_pkey PRIMARY KEY (p_id)
);
CREATE TABLE tbl_child1
(
c1_something character varying(64) NOT NULL,
CONSTRAINT tbl_child1_pkey PRIMARY KEY (p_id),
) INHERITS (tbl_parent);
CREATE TABLE tbl_child2
(
c2_somethingelse integer NOT NULL,
CONSTRAINT tbl_child2_pkey PRIMARY KEY (p_id),
) INHERITS (tbl_parent);
This works fine so far. Now I got another table that references to the primary
key of the parent (I need an n to n relation between another table and an
aggregation of all my child tables, which means an n to n relation between the
other table and the parent table):
CREATE TABLE tbl_reference
(
ref_id serial NOT NULL,
ref_parent integer NOT NULL,
ref_othertable integer NOT NULL,
CONSTRAINT tbl_reference_pkey PRIMARY KEY (ref_id),
CONSTRAINT tbl_reference_ref_parent_fkey FOREIGN KEY (ref_parent)
REFERENCES tbl_parent (p_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT tbl_reference_ref_othertable_fkey FOREIGN KEY (ref_othertable)
REFERENCES tbl_othertable (ot_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET NULL
)
When I try to insert into tbl_reference now, I get an sql error:
INSERT INTO tbl_child1(p_time,c1_something) VALUES ('2008-01-01', 'foo');
INSERT 0 1
SELECT * FROM tbl_parent;
p_id |p_time
--+
1 | 2008-01-01 00:00:00+01
(1 row)
INSERT INTO tbl_reference(ref_parent,ref_othertable) VALUES ('1','1');
ERROR: insert or update on table "tbl_reference" violates foreign key constrain
t "tbl_reference_ref_parent_fkey"
DETAIL: Key (ref_parent)=(1) is not present in table "tbl_parent".
Why is this not possible? It seems that this is about inheritance. When I
reference directly to tbl_child1, everything works just fine.
Any idea?
Greets Stefan
--
Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser
---(end of broadcast)---
TIP 6: explain analyze is your friend
