PostgreSQL Database Forum

Is there a way to change a schema owner other than dump/restore? How about changing the nspowner in pg_namespace? Will that do the trick without any negative consecuences? ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend

SQL Server only allow one NULL in a unique constraint column (it's the unique index that does that, so the unique constraint behaves like that too). The question is, what is the best way to simulate that behaviour in Postgres? Can a CHECK constraint does that? Will a trigger with SELECT count(*) ... WHERE f IS NULL be too slow if the table is...

Hi, Im v.new to postgres. I needed some help writing batch files. I wanted to run psql commands in a batch file. I give 2 inputs to the batch file -- Username and Database name I want my batch file to do the following things. - check if a database created by that user exists if true return 1 else return 0 Anyone who knows how to do...

Hi: I prepare the statement for execution as follows: PREPARE query(text) as SELECT count(*) FROM transform_customer_billing where inv_no = $1; The problem is Iam not able to execute this prepare statement from the stored procedure defined. I added the statement to call the prepare statement is: EXECUTE query('100023'). The ERROR it...

Hi. I've been looking for a function similar to MySQL's sha() function in Postgre and I can't seem to find anything. The reason is that I have a system that is written for MySQL and I've been asked to make it compatible with Postgre. Thanks.

Hi, I can't seem to retrieve a comment on a table column. The following copy from psql should I think return a comment: ==================== mydatabase=> COMMENT ON COLUMN car.manufacturer IS 'manufacturer name'; COMMENT mydatabase=> SELECT relnamespace FROM pg_class WHERE relname='car'; relnamespace

Dear Friend I have some question related to postgresql frontend. I have develop postgresql web based database under linux environment and I use Gedafe as frontend and Apache as server. and now i am trying to build my database under window, the problem is that Gedafe is not working in Window environment, so my question, is anyone know which...

I have a view that used union all to merge three tables together. I was hoping to create a rule, one for each table using the WHERE condition of the rule to determine which table gets updated. Is this possible? This is what I have, assume the view here is a merge of three tables using union all: CREATE RULE "update_xrf" AS ON UPDATE TO...

Hello, Under mysql, we have "desc tablename" to get the detail information about a table. My question is about to get column name, and column type for a specific table under PostgreSQL through JDBC. Assume we have a table named "test". Under PSQL, we can input "\d test" to see the details about table "test" successufully. However, my...

Hello, I'd like to know if it is possible to make a random sample of a number/percentage of rows with the same value, i.e. to select randomly 6000 registers of field "x" where x=0 Thank you

Hi! Suppose I have "pg_dump -s" of two pg installs, one is "dev", another is "production". Their schemas don't differ too much, and I want to get a "diff -u"-like schema diff so I can quickly add missing/remove old tables/sequences/etc to one or another (manually). Is there some quick tool for doing this ? There was a thread about it...

Hi, I am having problem using the crosstab function ( which is under the contrib/tablefunc directory). I have installed the tablefunc module and also installed the functions into my DB. I am using postgresql 7.4 beta version. I am issuing the following query, select * from crosstab( 'select path,name,value from file f, metadata m

Hi, I'd like to return the output, or output as a variable, from a pgSQL query that I'm executing through batch file back to the batch file so that it executes a line in particular of the batch file (through a 'goto' command I think). I manage to execute the query and see the result (in cmd) but am not sure how I can grab that result and...

hi to everyone, I tried to create a function that includes a role creation with the rolename and password as parameters to be called by other function but the postgresql raises an error like that: ERROR: syntax error at or near "$1" SQL state: 42601 Character: 81 The function definition is: CREATE FUNCTION new_user (text,text) RETURNS...

Hi all, Iam struck with one of the problem with postgres. Please help me out.... Iam trying to connect to connect to postgres database on remote machine with the IP address 10.2.1.4. Iam unable to get connected. below is my script. <?php $connection_string = 'DRIVER={PostgreSQL};SERVER=10.2.1.4:5432;DATABASE=Admin';

I have to retrieve two tables which are not having any relation in a single query itself for example select * from memberphoto;select * from restricconfig; if i execute this query only restricconfig table is viewed but i want both table to be retrieved simultaneously in a single query advance thanks kirthika

Hi guys, I am in troubles with a SERIAL field. I have five tables. A parent table and four child tables. When I do the INSERT in the parent table, I have an ID (generated) by the sequence (SERIAL field), and I have to use this ID to reference all child tables.

I run the following script to export some data from my development database and then update or insert the records into to the quality assurance testing database, but I get a warning notice that I don't understand. Aside from that notice, the script appears to work as intended, i.e., updating existing report definitions and adding any new ones...

Hi, In a database I have to create new schemas with exactely the same structure as the default one. Of course, I could reverse a schema with pg_dump, then apply the script to the newly created one. The problem is the base schema sometimes changes so I should generate scripts every time. Also I need to do this from a stored function. My...

I installed pgagent in a windows 7. On pgadmin3, I can see JOB. And I create a simple Job on it to run a simple batch file, just one line, echo ZZZ. I try to "run now". it won't work at all. I check pgAgent service, it's running. And run by a PC account who can access the batch file. Just don't what else I have missed out!

Hello When we create a table, what is the difference between the REFERENCE constraint on column and the FOREIGN KEY constraint on table ? I guess that the FOREIGN KEY constraint assures us that the referenced column is a PRIMARY KEY in an another table. You can reference any colums with the REFERENCE constraint on column. Am I right?

Guys, Now that Ingres has been open-sourced, I'm curious about where it stands with regards to PostgreSQL. Is it a worthy competitor? PostgreSQL has been my database of choice for many years, and I'm very interested in how Ingres and PostgreSQL stack up in terms of features and performance. Any insight you can provide will be very much...

Is there a way to provide a md5 password directly to psql? I'm aware of pgpass but I would like to store a md5 password in some other location and then use it to execute commands with psql. Thanks, John DeSoi, Ph.D. ---------------------------(end of broadcast)---------------------------

Hello I want to upload many files with .spu extension (80000 files which has metadata) in raw format in to a databases using postgresql Can anyone suggest me way to do it.Waiting for your replies.

Just dropping a quick not for Tom Lane. I sent a personal message today, but I wasn't sure if you'd get it after I remembered all of the spam filters you've got set up. Sorry for the off topic post. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? ...