Tagged Questions

3
votes
3answers
395 views

Months between two dates function

In oracle i can find out no:of months between using MONTHS_BETWEEN function. In postgres i am using extract function for this. eg.like select extract(year from age(current_date, '2012-12-09')) ...
3
votes
1answer
152 views

How to XML-encode a string in PostgreSQL?

Question: I can create a XML-encoded string in Postgres like this: SELECT xmlelement(name name, 'AT&T', null ) now I want to get the xml encoded value, that is to say AT&T. But if I ...
2
votes
1answer
116 views

Make SELECT with LIMIT and OFFSET on big table fast

I have more than 10 million records in a table. SELECT * FROM tbl ORDER BY datecol DESC LIMIT 10 OFFSET 999990 Output of EXPLAIN ANALYZE on explain.depesz.com. Executing the above query takes ...
1
vote
1answer
209 views

Replication of postgres database at table level

I am just wondering whether it would be possible to replicate only a table in a postgres database with another database instance. For example, lets say that I have a postgres instance running at hostA ...
1
vote
1answer
164 views

Could one use Postgres as a document database?

Let's say we're building a DB for storing analytics from web sites or mobile apps. Transactions aren't important (it's ok to drop things) and only slow things down, supporting as many concurrent calls ...
1
vote
2answers
61 views

aggregation in postgres

I have 3 tables in postgres containing information of the form: <id, column1, column2, column3> Now I intend to aggregate the 3 tables based on id, such that all the rows with the same id ...
0
votes
2answers
56 views

PostgreSQL: How to list all available datatypes?

Question: In PostgreSQL (using SQL, not the console), how can I list all available datataypes ? Ideally like this: ...
0
votes
3answers
106 views

PostgreSQL: Which Datatype should be used for Currency?

Seems like Money type is discouraged as described here My application needs to store currency, which datatype shall I be using? Numeric, Money or FLOAT?
0
votes
1answer
45 views

Postgresql 9.x: Index to optimize `xpath_exists` (XMLEXISTS) queries

We have queries of the form select sum(acol) where xpath_exists('/Root/KeyValue[Key="val"]/Value//text()', xmlcol) What index can be built to speed up the where clause ? A btree index created ...
0
votes
1answer
57 views

Accessing postgres without being root users

I have created a postgres database in postgres named "databaseName". Now I can access this data2database through su - postgres and then typing my password Then I enter into the database through: ...
0
votes
1answer
64 views

PostgreSQL 9.x - pg_read_binary_file & inserting files into bytea

I have been looking everywhere (google, stackoverflow, etc.) for some documentation on how to use the PostgreSQL pg_read_binary_file() function. The only meaningful thing I can find is this page in ...
0
votes
1answer
247 views

postGIS for latitude and longitude

I created a table named zipcode with the following columns Id State Zip Latitude Longitude column1 column2 Columns named column1 and column2 are the 2 geometry columns i have created to store the ...
0
votes
0answers
365 views

PostgreSQL install fails on windows 7 with failure to initialize database cluster

The PostgreSQL 9.1 and 9.2 oneclick installer keeps failing on my Windows 7 machine with failing to initialize database cluster. I also tried installing from binary but it fails during database ...
0
votes
1answer
300 views

upgrading postgresql 9.1.2 (9.1.3) -> 9.2.1 via homebrew

In a round of upgrades I ended up (literally, it was the last upgrade left) updating postgresql via homebrew. It installed PostgreSQL 9.2.1. I couldn't make it run: Dart:~ Arta$ pg_ctl -D ...