All - I need some help designing a table for a Postgre SQL database. I have a table of products ie; CREATE TABLE products ( product_id integer PRIMARY KEY, product_name text, price numeric); INSERT INTO products (product_id, product_name, price) VALUES (DEFAULT, 'Purple Widget', '5.50'), (DEFAULT, 'Green Widget', '1.5...
I try to install Postgres on Windows (Server 2003). Both versions I tried (8.3, 8.4) fail at the end of the installation because the service cannot be started. I tried with different users for the service without success. Any ideas anyone? ...
Hi all, I tried to enlarge the value of the "shared_buffers" settings to be larger then a default 24Mb, and the server doesn't starts with other values (I tried some) except of the default one. Just an empty logfile is created. It's a clean installation of the postgresql on the linux server, so all other settings are default. Does anybod...
Hello. I need to sync two PostgreSQL databases (some tables from development db to production db) sometimes. So I came up with this script: [...] pg_dump -a -F tar -t table1 -t table2 -U user1 dbname1 | \ pg_restore -a -U user2 -d dbname2 [...] The problem is that this works just for newly added rows. When I edit non-PK column I get...
There is a function in our system that writes a file to a postgres BLOB, the function is pg_lo_write, our system uses PHP, ADODB and PostgreSQL. What I would like to know is where is this data stored, is it a file in a postgres directory, in a table? Many thanks! Answer Information Thanks to Ben Fransen for the answer, the BLOB is in...
PostgreSQL 8.4 on Linux - I have a function - CREATE OR REPLACE FUNCTION production.add_customer ( name varchar(100), email_address varchar(300), street_address text, city varchar(50), state varchar(2), zip varchar(10), secret1 bytea, secret2 bytea, secret3 bytea, secret4 bytea, referrer text) RETURNS integer...
Oracle creates nicely HTML-formatted workload reports (AWR) that list things like instance statistics, wait events, the top ten heavy SQL statements (by number of executions, number of disk reads, and so on). Is there a similar tool for Postgresql? ...
A bit of a vague title, I will explain. I am writing an SQL script to create an insert statement for each row of a table in my database, purely to be able to apply that data back to another database. Here is what I have at the moment: SELECT 'INSERT INTO products (id,name,description) VALUES ('||ID||','''||name||''','''||description||...
I have a need to change the length of CHAR columns in tables in a PostgreSQL v7.4 database. This version did not support the ability to directly change the column type or size using the ALTER TABLE statement. So, directly altering a column from a CHAR(10) to CHAR(20) for instance isn't possible (yeah, I know, "use varchars", but that's n...
Is it possible to copy the user permissions from one table in a PostgreSQL database to another table? Is it just a matter of updating the pg_class.relacl column value for the target table to the value for the source table, as in: UPDATE pg_class SET relacl=(SELECT relacl FROM pg_class WHERE relname='source_table') WHERE relname='target_...
I'm running PostgreSQL 8.3 on a 1.83 GHz Intel Core Duo Mac Mini with 1GB of RAM and Mac OS X 10.5.8. I have a stored a huge graph in my PostgreSQL database. It consists of 1.6 million nodes and 30 million edges. My database schema is like: CREATE TABLE nodes (id INTEGER PRIMARY KEY,title VARCHAR(256)); CREATE TABLE edges (id INTEGER,li...
I have a table used by one piece of 3rd party software (say user_fields) that has for each row a user_id, a field_id and a field_value. Crappy I know. I also have another user table of my own, users, that contains the values needed to populate this, except the field_id which is "magic" and corresponds to the custom field mapping. How c...
I have created a custom data type enum like so: create type "bnfunctionstype" as enum ( 'normal', 'library', 'import', 'thunk', 'adjustor_thunk' ); From an external data source I get integers in the range [0,4]. I'd like to convert these integers to their corresponding enum values. How can I do this? I'm using PostgreSQL 8.4. ...
Hi, I want to run my PostgreSQL database server from memory. The reason is that on my new server, I have 24 GB of memory, and hardly any of it is used. I know I can run this command to make a ramdisk: mdmfs -s 1024m md2 /mnt And I could theoretically have PostgreSQL store its data there. But the problem with this is that if the serv...
Hello I have several databases, such as mytable_2009_11_19_03 where last 2 numbers identify the hour (table for 03:00), now I want to query something from _00 to _23 . It could be done in such way but it is really clumsy select * from mytable_2009_11_19_00 where type = 15 UNION select * from mytable_2009_11_19_01 where type = 15 UNION ....
I have a query that takes avg data (prices) from 7 days of the week for a long interval. IE avg prices for monday, tues, etc. It works fine, but I'm unsure how I can in the same query sum the avgs that this query finds? Summing Day1..Day5 As it stands this query sums the entire from of all the prices... IE huge number.. not from the ...
I'm running 64-bit Solaris 10, and I have self-compiled Perl 5.10 and Postgresql 8.4.1 installed in /usr/local, both 64 bits. Solaris came with 32-bit Postgresql 8.1.4 installed in /usr, but it is not running. When I attempt to install DBD::Pg, it hits a problem because the libpq.so it finds is the 32-bit one in /usr/lib rather than the ...
I have a postgres table that looks in part like: Year | Month | ...... (more columns) "2004" | "09" | ...... "2004" | "09" | ...... "2004" | "09" | ...... "2004" | "10" | ...... "2004" | "11" | ...... "2004" | "11" | ...... "2004" | "12" | ...... "2005" | "01" | ...... "2005" | "01" | ...... Yes, these are all strings. Don'...
I am developing an algorithm with Postgres (PL/PgSQL), and I need to calculate the number of working hours between 2 dates, taking into account that weekends are not working and the rest of the days are counted only from 8am to 15pm. Examples: From Dec 3rd at 14pm to Dec 4th at 9am should count 2 hours. (3rd = 1, 4th = 1) From Dec 3rd...
Hello, Am wondering if there are guidelines somewhere to write SQL code which is compatible in SQL Server, mySQL as well as postgreSQL. I am writing a program and the user has a choice of backend database. It will be really difficult for me to create separate queries for each and every db. I do not want to use an ORM or anything like th...