Re: [GENERAL] Question About UNION
On Thu, 2008-10-09 at 10:59 -0600, Bill Thoen wrote: > >> I'm trying to combine two tables, but I only want unique records based > >> on the first two columns. Can UNION be used to join three-column tables > >> but only include records based on the uniqueness of the first two > >> columns? If not, how would I do this with PostgreSQL 8.1? > > > > How do you decide which records you want? - e.g. given the following rows... > > > > (a, b, c) > > (a, b, d) > > > > ...how do you decide whether you the one with c or the one with d? > > > > > The physical order that they appear will take care of that. If the rest of the columns don't matter, how about: SELECT DISTINCT ON(col1, col2) * FROM ( SELECT col1, col2, col3 FROM table1 UNION SELECT col1, col2, col3 FROM table2 ORDER BY col1, col2 ) AS uniontable; - Josh Williams -- 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] Reg: Permission error in Windows psql while trying to read sql commands from file
On Sun, 2008-10-12 at 09:25 +0530, Raj K wrote: > Since it is in windows - I could not find any specific file permission > mechanisms similar to linux. (This is my first foray in windows - so I > am a newbie there too ) > The computer is not in a network. So, through googling, I found that > to share it, we have to move it to > C:\Documents and Settings\All Users\Documents\ - which I did. > > But even that did not help - as mentioned in the first mail. Try using forward slashes in your path: testdb=# \i c:/testdb.txt And if you have spaces, enclose the path in quotation marks: # \i 'C:/Documents and Settings/All Users/Documents/DB/testdb.txt' > If you could help me on this I would be much obliged.. > > Regards > Raj - Josh Williams -- 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] schema and roles
On Fri, 2008-10-17 at 19:45 +0200, Alain Roger wrote:
> i mean if i'm logged with the role "test_user", typing show
> search_path; will show me all default schemas.
On SHOW, the manual saith[1]:
"The function current_setting produces equivalent output" ...
SELECT current_setting('search_path');
It's also in pg_settings, though not listed in the (8.2) manual:
SELECT * FROM pg_settings WHERE name = 'search_path';
> After that, i know that using alter user test_user i can add
> another schema as default, but how to remove some ?
> thanks a lot,
ALTER USER ... SET search_path ... will always replace the schema search
path with the value you specify, so simply leave it out of that list to
effectively remove it.
P.S. In case there's any question, search_path has nothing to do with
security and access rights to schemas.
[1] http://www.postgresql.org/docs/8.2/interactive/sql-show.html
- Josh Williams
--
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] psql: what's the SQL to compute the ratio of table sizes?
On Fri, 2008-10-17 at 15:30 -0400, Kynn Jones wrote: > Suppose I have two table X and Y and I want to compute the ratio of > the number of rows in X and the number of rows in Y. What would be > the SQL I could type into a psql session to get this number? Sub-selects should work. And make sure to cast to avoid integer division (well, assuming you want to avoid it...) SELECT (SELECT COUNT(*) FROM tablex)::numeric / (SELECT COUNT(*) FROM tabley)::numeric AS ratio; - Josh Williams -- 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] ordered pg_dump
On Mon, 2008-11-10 at 17:05 -0800, Jeff Davis wrote: > Is there any interest in an optional mode for pg_dump to order the > output so that it's easier to use diff? > > I don't think it would make the output 100% deterministic, but it would > make it easier to at least compare the data for small databases. That'd be cool. I'd done some poking around on the topic a little while back. The goal was to make the output more predictable so that backups would be more efficient, specifically with a product that does binary diffs of some sort. I may still have some notes somewhere if you're interested. But I believe the idea was to use COPY with a SELECT statement. The non-trivial part was to figure out a proper ordering to use. Or did you plan on combining it with -t, where you could then specify the ordering for each table? > I think this has been brought up before, but I couldn't find the thread, > so I don't know what conclusion was reached. > > Regards, > Jeff Davis (... Plus, you potentially get a free CLUSTER on a reload.) - Josh Williams -- 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] Alter definition of a column
From: "Kelly Burkhart" <[EMAIL PROTECTED]> > On 1/20/07, Shoaib Mir <[EMAIL PROTECTED]> wrote: > > Should help --> ALTER TABLE tablename ALTER columname TYPE text; > > I was looking for a way to alter a column from varchar(n) to text > without using the alter command and consequently touching every single > row. Below is sql which seems to work, but is it advisable to do such > shenanigans? (are varchar and text the same thing)? Always have been under the impression myself that text and varchar (and character varying for that matter) were all aliases for the exact same thing in the back end. Just the latter allows for that limit (n) to be specified if your specs require. Leaving that off saves a few validation cycles, but at the possible expense of schema and data incompatability with other systems should that ever matter. So yeah, I don't see any reason it shouldn't work just fine. - Josh Williams ---(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
