PostgreSQL Database Forum

Hi, I'm having a performance problem with a large database table we use with postgres 7.3.4. The table is: db=> \d log Table "public.log" Column | Type | Modifiers ---------+----------+----------- hid | integer |

Hi, I've been scouring the net and reading the PostgreSQL docs for a while now trying to learn how to create a recursive function in PL/pgSQL that will return a whole subtree given a starting node. I wanted to share my summary results here. Maybe this will help someone and save them from doing a bunch of research (like I had to do :). For...

Hello ! It is late night and I cannot think right anymore. Please help me to convert a interval like '2 days 00:22:10.2905' in seconds. Thank you very much in advance, Liviu ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your

Aother head banger for me. Below is a complete example of the code Using Postgres 7.4, the function "test" gets this: psql:temp3.sql:10: ERROR: syntax error at or near "%" at character 135 the function "test2" gets this: psql:temp3.sql:10: ERROR: syntax error at or near "ROWTYPE" at character 141

For ease of configuration and other reasons, I would like for my single-user GUI app to be able to use postgresql in-process as a library accessing a database created in the users home directory. I think I could possibly get what I want by launching a captive copy of postmaster with appropriate args but it seems conceptually cleaner to not...

Hi guys...need help here ... My problem is about pagination like <<PREV 1,2,3,4,5,6 NEXT>> Does LIMIT Function applicable in PostgreSQL? $SQL="SELECT * FROM TABLE1 LIMIT 0,5"; the sql above works in MySQL but in PostgreSQL failed... any idea how or whats the alternative way? or i just miss something?

Hello, I'm currently trying to decide on a database design for tags in my web 2.0 application. The problem I'm facing is that I have 3 separate tables i.e. cars, planes, and schools. All three tables need to interact with the tags, so there will only be one universal set of tags for the three tables. I read a lot about tags and the best...

Hi, is it possible to concatenate two rows (same or different data type) into one. like First Name, Last Name or ZIP City etc. Thanks Alex

I'm start to try to developer a Data Warehouse over PostgreSQL, I want to know what tools exist to: Modeling (Diemsnional) ETL process (Extract Transform Load) Query Report thanks

Hey guys, Is anybody at all using the PGOLEDB for PostGreSQL on Windows ? I google this and i come up completely empty as far as code examples or any documentation etc. Does anybody know how to connect to postgresql using the postgres oledb connection from visual basic 6? Here is what I have tried

Hi All, I Made a window application in c#.net and trying to fetch the record from the postgresql database. But whever i run the application "String was not recognized as a Valid Date Time" message show. This problem is arries from the database when it tries to fetch the record from a table in which timestampz datatype is used. so plz help me to...

Dear All, I'm trying to insert an xml file into my database. I have a table with a single text column. My intention is just to have the xml file take up one row in the table. I've tried the command COPY t1 FROM '/tmp/file.xml';

Hi, I have been trying to get LISTEN/NOTIFY working in with JDBC. I cannot seem to get notified. I looked in the e-mail archive and saw a lot of similiar questions a couple of years ago. I never could find any answers in the e-mail nor in the documentation. Perhaps I just missed it. I have tried the following code snipit: ...

Greetings. We're running Postgres 7.3 on an Intel linux box (Redhat Enterprise server, version 3.0). We find ourselves in an awkward position: we have a database of attributes relating to students that uses as its primary key the ID number of the student. This is awkward for the following reasons. Our university used to use...

Hi, I'm trying to do a simple import of a comma delimited text file with COPY but it's returning an error. The file has been granted all permissions in Linux, so it's not clear to me what the problem is. Version 7.41 is installed... see below for details: A comma delimited text file has been placed in a local directory with permissions...

I suspect there is something wrong because it takes 73s to delete a single line from a table whith 140 lines. So I tried to reindex the database: bash-2.05b$ postgres -P -O -D /var/lib/pgsql/data KakaoStats LOG: database system was shut down at 2004-04-11 19:55:10 BRT LOG: checkpoint record is at 7/9505D650 LOG: redo record is at...

in oracle 10g, you can issue: ALTER SESSION SET NLS_COMP = ansi; ALTER SESSION SET NLS_SORT = binary_ci; do you think this is an elegant solution for case insensitive sorting & searching? is there interest in seeing this in postgres? -- dave

hi. I am using a pseudo "pivot table" SQL query to generate a report but I am totally stumped on why the COUNT() function isn't getting me the expected results and was wondering if anyone has some ideas... it is for a statistical report showing which lead source a member followed when creating a profile (i.e. CNN ad, Chicago Tribune ad,...

Is there a way 'within' psql to suppress output? One of our cron scripts calls a sql file which contains various database commands (ALTER TABLEs, UPDATEs, etc) and various user-defined functions. So within this sql file there are various SELECT * FROM myFunction(); which sends output to the user from cron. I can't see anyway to suppress this...

have the table "numbercheck" Attribute | Type | Modifier -----------+------------+---------- svcnumber | integer | not null svcqual | varchar(9) | svcequip | char(1) | svctroub | varchar(6) | svcrate | varchar(4) | svcclass | char(1) | trailer | varchar(3) |

I uploaded data in to postgresql using pdadminIII ,In one the fields called 'time' i want to store time as 24 hour format .Is there a way where i can convert time in to 24 hour format and store in the postgresql database?

I have a pretty simple select query that joins a table (p) with 125K rows with another table (pc) with almost one million rows: select p.* from product_categories pc inner join products p on pc.product_id = p.id where pc.category_id = $category_id order by p.title

I have a simple function which I use to set up a users' expiry date. If a field in a table contains an interval then this function returns a timestamp some time in the future (usually two weeks), null otherwise. I can't pass the interval from the table into a variable properly within the function. Any ideas? CREATE OR REPLACE FUNCTION...

ERROR: could not convert UTF-8 character 0x00ef to ISO8859-1 Running 7.4.5, I frequently get this error, and ONLY on this particular character despite seeing quite a bit of 8 bit. I don't really follow why it can't be converted, it's the same character (239) in both character sets. Databases are in ISO8859-1, JDBC driver is defaulting to...

I need help to connect DBDesigner4 linux in ODBC to postgres. I'm having a problem connecting to a remote Postgres db using ODBC from my Debian Linux laptop. I've got ODBC set up (UnixODBC). In DBDesigner I'm simply trying to set up the connection using the ODBC datasource, but I keep getting the following error message: "Connection to...