Re: [GENERAL] Howto sort the result of UNION (without modifying its type)?
Hi Robert, > does this work for you? > select u.id from (your unions) as u order by u.id Unfourtunatly not, it converts my union-results from INTEGER to RECORD. However, it seems to be possible to order the unions directly: > result1 UNION result2 ORDER BY u.id Hmm, the query plan looks really insane for this ... wonder how it will perform when the tables will not be mostly empty ;) Thanks, Clemens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rename or Re-Create Constraints?
On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane wrote: > I believe you can rename the underlying indexes and the constraints will > follow them. (This works in HEAD anyway, not sure how far back.) Below is my table: inkpress=# \d marketing Table "public.marketing" Column | Type | Modifiers -+---+--- id | integer | not null vendor | character varying(40) | not null account | integer | not null email | character varying(40) | not null state | character(2) | not null Indexes: "accounts_pkey" PRIMARY KEY, btree (id) "accounts_account_key" UNIQUE, btree (account) "accounts_email_key" UNIQUE, btree (email) "accounts_vendor_key" UNIQUE, btree (vendor) I renamed the table name from 'accounts' to 'marketing' however all the constraints listed under 'indexes' are still named 'accounts_*' and I've tried to rename them but I can't find any information with an example command to rename the constraints: ALTER TABLE marketing ...??? I can't find any update / alter SQL commands to correct the constraint inconsistency. :( -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rename or Re-Create Constraints?
Carlos Mennens writes: > On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane wrote: >> I believe you can rename the underlying indexes and the constraints will >> follow them. (This works in HEAD anyway, not sure how far back.) > I renamed the table name from 'accounts' to 'marketing' however all > the constraints listed under 'indexes' are still named 'accounts_*' > and I've tried to rename them but I can't find any information with an > example command to rename the constraints: ALTER INDEX accounts_pkey RENAME TO whatever On very old versions of PG you may have to spell that "ALTER TABLE" instead of "ALTER INDEX", but it's the same thing either way. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rename or Re-Create Constraints?
On Sat, Apr 9, 2011 at 12:58 PM, Tom Lane wrote: > ALTER INDEX accounts_pkey RENAME TO whatever > > On very old versions of PG you may have to spell that "ALTER TABLE" > instead of "ALTER INDEX", but it's the same thing either way. Thank you so much for clearing that up for me Tom! I just couldn't find anything documented or do I understand SQL enough to work through that w/o an example. I read the PostgreSQL documentation all morning and just couldn't find it. Also to make sure I did this correct, if I had an existing table w/o a PRIMARY KEY index / constraint, is the following correct? CREATE UNIQUE INDEX users_pkey ON public.users (id); CREATE INDEX I'm guessing that's how I generate a index / constraint on an existing table when it was generated during the table creation SQL command, right? Is there a difference between an INDEX and a CONSTRAINT? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rename or Re-Create Constraints?
On Saturday, April 09, 2011 2:59:06 pm Carlos Mennens wrote: > On Sat, Apr 9, 2011 at 12:58 PM, Tom Lane wrote: > > ALTER INDEX accounts_pkey RENAME TO whatever > > > > On very old versions of PG you may have to spell that "ALTER TABLE" > > instead of "ALTER INDEX", but it's the same thing either way. > > Thank you so much for clearing that up for me Tom! I just couldn't > find anything documented or do I understand SQL enough to work through > that w/o an example. > > I read the PostgreSQL documentation all morning and just couldn't find > it. Also to make sure I did this correct, if I had an existing table > w/o a PRIMARY KEY index / constraint, is the following correct? You want to create a PRIMARY KEY correct? If so starting from scratch: test(5432)aklaver=>create table pk_test(id integer,fld_1 text); CREATE TABLE test(5432)aklaver=>\d pk_test Table "public.pk_test" Column | Type | Modifiers +-+--- id | integer | fld_1 | text| test(5432)aklaver=>ALTER TABLE pk_test ADD CONSTRAINT pk PRIMARY KEY(id); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pk" for table "pk_test" ALTER TABLE test(5432)aklaver=>\d pk_test Table "public.pk_test" Column | Type | Modifiers +-+--- id | integer | not null fld_1 | text| Indexes: "pk" PRIMARY KEY, btree (id) > > CREATE UNIQUE INDEX users_pkey ON public.users (id); > CREATE INDEX > > I'm guessing that's how I generate a index / constraint on an existing > table when it was generated during the table creation SQL command, > right? > > Is there a difference between an INDEX and a CONSTRAINT? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rename or Re-Create Constraints?
On Saturday, April 09, 2011 2:59:06 pm Carlos Mennens wrote: > > > Is there a difference between an INDEX and a CONSTRAINT? Oops forgot to add this to my previous reply. The short version, an INDEX is one form of a CONSTRAINT. For the long version look at the CREATE TABLE section of the docs. It shows the various CONSTRAINTS available for both columns and tables i.e NOT NULL, FOREIGN KEY, etc -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] could not access status of transaction 1118722281
Turns out this was most likely the pg_upgrade bug. In our case, I was able to dump and recreate the table in question. Since then, this has been made public: http://wiki.postgresql.org/wiki/20110408pg_upgrade_fix -- View this message in context: http://postgresql.1045698.n5.nabble.com/could-not-access-status-of-transaction-1118722281-tp4283137p4293709.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] searchable database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sat, Apr 09, 2011 at 12:10:46PM +0530, quickinfo wrote: > Dear Friends, > > I need help from you. > > We have more than thousand electronic journals. I want to make a searchable > database for easy access. Is there any light wight database available for > that. Please provide me the details for the same. Your question is just too general to make a meaningful answer possible. The only answer I might offer is -- "yes, you might use a data base for that", and "yes, PostgreSQL might be useful for that", but I know that's just too general to be helpful. Maybe try to target your question a bit more? Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFNoT8FBcgs9XrR2kYRAuzWAJ4pB4cjQXNdtVHvxU+dyJeQCUQogACfXs4u GRMyRyllE5eIMH/l4qMaHUM= =hdaL -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
