PostgreSQL Database Forum

Hi, I have one trouble about exec sql define. Here's an example: EXEC SQL DEFINE INTSIZE 2; EXEC SQL DEFINE I_(x,y) char x; EXEC SQL TYPE CUSTOMER_RECORD IS STRUCT { I_(cno,2)

Hi, I'm a novice to postgress and need to constract a simple tree. In my table I have: regionID int parentID int name char I need to do two functions: find all the children of XXX parent and find if an item is a child of an XXX parent.

I'm having trouble restoring databases that have to lo type installed in /contrib/lo. The dump seems to work just fine, I get no errors when I execute the following command #pg_dump -Fc -o -b mydb > mydb_dump > CREATE DATABASE mydb_restore TEMPLATE template0

First, this may not be the correct newsgroup. I have some relatively basic questions on SQL. I tried to find a newsgroup that was specifically just about SQL, and was surprised to find that all the SQL-related newsgroups seem to be product related. But if I missed something, and someone can steer me to a correct newsgroup, please do so. ...

Hi Everyone, I have a rather trivial (I hope) question about OID types and PostgreSQL. Since PostgreSQL creates tables "WITH OIDS" by default, I'm wondering if it is bad practice to allow the default behaviour. For example, if I have a database with 60+ tables (all tables have their own PK that is not of type OID) and all of them have an...

I have a tough SQL join query and I figured it out, so I thought you might find it useful. I have 3 tables with tracking numbers in them. I want to think of the sum of all these tracking numbers, then find non-matches to a master tracking number table. When I find non-matches (orphans) in the master tracking number table, I want to...

I have a script that updates one record in a PostgreSQL 7.2.1 database through the Perl DBI. Here is the output from my log, which outputs the query sent to the DBI and the return value: --- Update query: UPDATE item SET status='SOLD' WHERE item_id=1129832 1 rows affected.

I'm trying to create a template database as a superuser. I set the value in pg_database datistemplate to 't'. But when I create a database as another user i don't have any priviliges on the tables in the new database. So I granted ALL to PUBLIC on the tables in the template database. Now when I create a database with the new template I can...

I want to allocate a contiguous block of ID's from a sequence generator. My concern is that between the time I call nextval() to get the start of the block and setval() to set a new starting seqence number past the end of the block that some parallel computation may already have called nexval() and allocated something within my block, or...

The postgresql documentation claims that "on insert" rules are executed after the insert. This is not happening for me. I have a rule that is being executed even though the insert should have failed due to a foreign key check. The rule does an insert that is failing, and the whole insert fails. So in this case it's not a huge problem. But I...

I need to change a varchar(32) to varchar(1024). It is a primary key that many tables reference. How do I do this without reloading the entire database?

Hello, Im trying to get some kde database application working, but I cant get them connected to the postgres server. psql connect seamlessly with - lets say - music database. But kde application dont? I created the user "janusz" in postgres I lived untached /etc/postgresql/pg_hba.conf (I understand localhost can connect allthe time) ...

hi i want to write a php script that will back up all tables for postgres on a weekly basis. does anybody know how to read the table names/columns from php? thanks

What is the most appropriate encoding for the UK when creating a database?

Hi, I developing a program using postgres and linux like operating system. My problem is this: I have a quite complicated view with roughly 10000 record. When I execute a simple query like this "select * from myview" postgres respond after 50 - 55 minutes roughly. I hope that someone can help me with some suggestion about reason of this...

Hello, I have a question about pgplsql: Is it faster to execute a compact function (with minimal spaces and comments) than a function with many comments and many spaces? In others words, which one will be faster? AAAAA_function or BBBBB_function ?

Hi everyone...my name is Mike MacEachern. I work with a technical search firm in Toronto - ARES Consulting A client of mine in Toronto is keen to hire a solid Postgres DBA --they're very hard to find! . If anyone would be interested please give me a call. Thanks To discuss this position further please contact Mike MacEachern at...

Is anyone out there using PostGIS in a production environment? If so does anyone know how well if at all postgresql/postgis work with the likes of ArcSDE and so on? Also if anyone out there reading this is a contact at ESRI that knows the behind the scenes support for postgresql Id like them to contact me. I have attempted to find someone...

Hi all, I need to filter out rows like 'A <number>' where the second character=space character. To start with I have the expression: ~* '^A' which will filter out any row starting with 'A'. Now there must be something to indicate the space character. (in Perl '\s' defines <space> but not in Postgres it seems)

Is it reasonable to have a table with about 300 columns? There will be about 2500 rows. The data does not normalize. The alternative would be about 30 tables of about 10 columns each (all 30 tables with about 2500 rows). If I do this, about half of the selects will be against one table, but the other half will require joins of all 30. ...

Hi, I have a dump with a table with this field: "hdstart" integer which contains integer like 1042758000 1028152800 etc etc How can I convert it to a SQL Server DateTime field? Thank in advance and sorry for my bad english...

I'm having a problem with a table that accidently had a column added whose type is the same as the table. This is seriously creating a problem as I cannot delete the table and I really don't have the ability to destroy the database (with all the other tables) just to get rid of this. Does anyone know of a way to get the database to allow a...

waiting for postmaster to shut down...LOG: received smart shutdown request ..LOG: shutting down ...LOG: database system is shut down done postmaster successfully shut down LOG: could not bind socket for statistics collector: Cannot assign requested address LOG: database system was shut down at 2003-11-26 14:13:05 MET LOG: checkpoint...

Hello I have database: base and user: vertigo. When i log into that database from pgsql: #psql base -U pgsql and try to give user vertigo all righst: base=# GRANT ALL ON DATABASE base TO vertigo\g ERROR: parser: parse error at or near "base"