PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for many platforms including Linux, UNIX, MS Windows and Mac OS X. Please mention your PostgreSQL version when asking questions.
0
votes
0answers
26 views
Slow update need an advice to optimize
update x = points.x
from new_report channel inner join old_report points
on points.provider_id = channel.provider_id and points.service_id = channel.service_id and ... and CAST(points.date as date) = ...
0
votes
0answers
6 views
Doctrine: avoid collision in update
I have a product table accesed by many applications, with several users in each one. I want to avoid collisions, but in a very small portion of code I have detected collisions can occur.
$item = ...
0
votes
0answers
6 views
How to specify cleanup by file age or date with pg_archivecleanup
Is there a way to specify files by age or date instead of by a hardcoded name to cleanup the WAL archives with the pg_archivecleanup command ?
For example, using the following command is pretty ...
0
votes
2answers
20 views
PostrgreSQL 8.4: Summation by account numbers
I am doing some report on financial accounts, and I need to sum values at levels depending on the initial numbers...
For example sum all values for account starting with 0 (01, 011, 012..), or ...
0
votes
1answer
8 views
Comparing Text on PostgreSQL 8.4 and 9.1
I have two databases, one is running on postgresql 8.4 and the other on postgresql 9.1.
Both are on CentOS machines with the same locale (en_US).
Suppose i have a table with this data:
id | ...
0
votes
3answers
31 views
How to View My Postgres DB Schema from Command Line
So I have my Django app running and I just added South. I performed some migrations which worked fine locally, but I am seeing some database errors on my Heroku version. I'd like to view the current ...
0
votes
0answers
10 views
PostgreSQL: Retain Sort Order / Temporary Index / Paging
I'm using PostgreSQL and I intend to paging. The target table contains 1M+ rows. In principle, this is straight forward
SELECT * FROM myTable ORDER BY orderCol LIMIT <pageSize> OFFSET ...
0
votes
2answers
18 views
Postgresql's substring expression
I have a field message with strings like <pika> [SOME_TEXT_WITH|ACTION] And other stuff....
I wish to capture what's inside the brackets. I use the following form:
SELECT
substring(message ...
0
votes
1answer
28 views
Convert into PostgreSQL Dynamic Query
Below is one function which has one query ,
Now I want to convert into dynamic query. I want one table name parameter so query return data from multiple tables.
please help me in this I am new in ...
0
votes
1answer
14 views
PostgreSQL, libpq/C, update parameters
I can update record in table by string manipulation which have some weakness.
So, now I'm try to update with parameters but this don't go as I thought.
sprintf(sql, "%s%s%s%s%s%d%s",
"UPDATE ", ...
1
vote
1answer
19 views
Vacuum analyze is running very slow and causes timeouts for other systems
I'm running PostgresSQL 8.3.3. Every night a vacuum analyze is performed against the database. Every table takes around 5 minutes to complete (large or small). Initially the process took 1/3 of the ...
0
votes
2answers
47 views
Convert SQL Server stored procedure into PostgreSQL stored procedure
I am very new to PostgreSQL database, I am developing database objects in SQL Server 2008 but I require some operation on PostgreSQL also.
Here, I write one sample stored procedure of SQL Server ...
2
votes
1answer
41 views
Rails first_or_create adds (1=2) to query
While running development code through Heroku console in sandbox mode, I use first_or_create to test for existence of a record:
Right.where(:language => language ).
...
1
vote
5answers
26 views
PostgreSQL, SELECT from max id
By using libpq on PG 9.1, I am trying to write query to get values from row with highest index 'my_id':
SELECT my_id, col2, col3 FROM mytable WHERE my_id = MAX(my_id)
That gives me error:
...
0
votes
1answer
34 views
Analog of OUTER APPLY in other RDBMS (not SQL Server)
I'm using SQL Server at work, and I have some nice tricks with OUTER APPLY clause which helps me do not repeat code. For example, if I have a table like this:
create table Transactions
(
ID ...
1
vote
1answer
21 views
Multiple unique partial indexes in PostgreSQL sanity check
I'm trying to figure out the best way to enforce a unique constraint across multiple nullable columns in PostgreSQL.
Considering the following table:
CREATE TABLE test_table
(
id serial NOT NULL,
...
0
votes
0answers
17 views
Determination of the reasons of omission of request
There is a procedure reading from a database of value and writing data in ArrayLists which also register in files. As a result of program execution debug files are empty. Prompt in what business. As I ...
0
votes
0answers
15 views
CodeIgniter with a PostgreSQL multiple schemas issue
I am developing an agent-port application in CodeIgniter with PostgreSQL by using mutiple schemas from a single database. The setup I have designed is working fine.
So the problem I am facing right ...
-2
votes
0answers
27 views
postgres copy is not working in large column table
I have a table in posgtres which have 72 column but when i use copy command to copy a file from postgres table is not working it shows an error like missing data for column "DtToBeDiscontinued". but ...
-2
votes
3answers
52 views
How to insert into table in Postgres?
INSERT INTO HMS_RESERVE_CANCEL_DTL
(DIVISION_CODE,
UNIT_CODE,
RESERVATION_NO,
RESERVATION_DATE,
CANCELLATION_NO,
CANCELLATION_DATE,
CANCELLED_AT_UNIT,
...
1
vote
1answer
36 views
How to switch my Rails app to postgresql from Sqlite3?
So I started working on a Rails app recently and we decided (well not me, the person working on it with me) that we should switch from Sqlite3 to Postgresql. I've installed Postgresql on our server ...
1
vote
1answer
17 views
Is it possible to supply parameters for table or column name in Prepared Statements or QueryRunner.update()?
DELETE from ? WHERE ? = ?
Is it possible to feed in parameters for all of these ?s? It seems that if I don't supply Strings, I get errors. For example, it only seems to work if I have something like:
...
0
votes
3answers
31 views
postgres update rule wont insert
i have a simple table:
CREATE TABLE aaa_has_bbb (
aaa_id integer not null,
bbb_id integer not null,
rank integer not null,
primary key(aaa_id, bbb_id),
uniq(aaa_id, rank)
)
I ...
0
votes
1answer
13 views
Rails : Create a drop table cascade migration
How do I force a DROP TABLE CASCADE in a Rails 3.2 migration?
Is there an option to pass to drop_table("table_name")?
0
votes
1answer
15 views
PostgreSQL point_ops with GiST PostGIS Spatial Index
The 9.0 release notes for PostgreSQL states the following change:
Add point_ops operator class for GiST (Teodor Sigaev)
This feature permits GiST indexing of point columns. The index can be
...
0
votes
0answers
14 views
Postgres/GIS: RULE does not affect all inserts
I have a rule for a table which simply checks if the new entry matches a name and intersects with that matching existing row using st_intersects from postgis library.
It seems that only a part are ...
1
vote
1answer
26 views
Reconnecting to a postgres database after postgres restart from Java
I'm using postgres 9.1, org.apache.commons.dbcp.BasicDataSource (for my connection pool) and Java 1.7. When I restart my postgres server, I get exceptions like org.postgresql.util.PSQLException: ...
0
votes
1answer
22 views
Combining PostgreSQL Enum with a TypeDecorator
I want to have an Enum type that automatically lowercases input before saving it in a PostgreSQL 9.x database. This first code block accomplishes that, but, unlike a normal Enum, the specific ...
0
votes
2answers
35 views
postgresql index on string column
Say, I have a table ResidentInfo, and in this table I have unique constraints HomeAddress, which is VARCHAR type. For future query, I gonna add an index on this column.
The query will only have ...
-1
votes
2answers
42 views
Insert command without id postgreSQL
In mySQL i am able to do
INSERT INTO table_name (column_name1, column_name2) VALUES('John', 'Doe);
As you can see I do not have to mention the ID, how would I do this in postgreSQL.
Thank you
0
votes
0answers
16 views
Rails jquery datetime into PG [closed]
I'm using jquery (coffeescript) to update a record. When that happens I would like today's datetime to go into this field:
t.datetime "completed_at"
This is the coffeescript:
compdate = ...
0
votes
2answers
25 views
SQL query, filtering based on last value in a connected table
I have a problem with a nested query (the problem beeing i have no idea how to do it)
I'm using PostgreSQL
I have 2 tables:
users
id name
1 x
2 y
3 z
call
id user_id time data
1 1 00:10 stat1
2 1 ...
0
votes
0answers
33 views
porting Apache Cayenne from oracle to postgreSQL
I'm porting an existing Java project from Oracle 10 to PostgreSQL 9.1.8 (is this version actually supported from Cayenne?)
Something is wrong in OUT parameters when a cursor is returned.
Return type ...
0
votes
0answers
14 views
Multiple Relations on the same model
i'm having a problem retrieving related data of my model ProductsPproduct when i search with contain. My table is basically a mapping between ordered products and produced products for example ...
2
votes
1answer
30 views
Updating status timespan and removing unneeded rows with SQL
let's say i have some servers, and they are constantly updating a database with their status.
i need to run some reports on the status of these servers. doing a little cleanup on the tables would ...
0
votes
1answer
17 views
Postgresql multi-row insert using command parameters
I need to insert large amounts of data into postgresql using multi-row inserts.
I am currently doing this by constructing the sql query using a stringbuilder and appending the values but I was ...
0
votes
0answers
29 views
Can Octopus keep a pool of database connections?
We switched to using Octopus for sharding in our rails2.3/postgresql/resque app because we were maxing out the disk I/O of our database server. We have ten databases, each with multiple shards. (A ...
1
vote
1answer
21 views
Selecting schema configuration in Postgres with doctrine 2
I've got a complex question, I hope you will help me.
I'm using Doctrine DBAL in my application because I would like to make it works in Postgres and Mysql.
The problem is that I want to switch over ...
0
votes
0answers
25 views
postgreSQL get last ID in partitioned tables /
my question is basically the same as this one, but i couldn't find an answer, its also written "to be solved in the next release" and "easy for min/max scans"
PostgreSQL+table partitioning: ...
0
votes
1answer
43 views
If PostgreSQL count(*) is always slow how to paginate complex queries?
If PostgreSQL's count(*) is always slow how to paginate complex queries?
Making triggers doesn't seem to be a good solution as long as in this case we have a lot of pages (for example different ...
1
vote
2answers
19 views
PQprepare and PQexecPrepared Usage
Hoping someone can help me with the usage of PQprepare and PQexecPrepared. I'm sure I must have something wrong but nothing I try seems to work.
I'm trying to insert into a table using a prepared ...
0
votes
0answers
24 views
4 letter strings sent to SQL function in .NET DataSet using PostgreSQL 8.4+ returns no result
Problem
After upgrading PostgreSQL from 8.1 to 8.4 we get problems when sending 4 letter strings into database functions set up in data sets.
It happens for all functions
Using 1, 2, 3, 5 or any ...
0
votes
1answer
38 views
Error 28 “Out of Stack Space” executing a huge query with VB6 & ADO 2.8
Scenario:
Executing an SQL command from a Visual Basic 6 application using ADO Connection.Execute method through PostgreSQL OLEDB Provider to a PostgreSQL 9.2 database.
Query:
It's a simple EXECUTE ...
1
vote
3answers
38 views
How to select id with max date group by category in PostgreSQL?
For an example, I would like to select id with max date group by category,
the result is: 7, 2, 6
id category date
1 a 2013-01-01
2 b 2013-01-03
3 c 2013-01-02
4 a ...
0
votes
3answers
23 views
Postgres: Distinct but only for one column
I have a table on pgsql with names (having more than 1 mio. rows), but I have also many duplicates. I select 3 fields: id, name, metadata.
I want to select them randomly with ORDER BY RANDOM() and ...
1
vote
2answers
36 views
Why isn't my index used
I'm running postgresql-9.1.6 on RHEL 5.8 OS. I got a statement implementing seq scan on which column is indexed.
Table "public.table"
Column | Type | ...
0
votes
1answer
35 views
PostgreSQL - Comparing time with value >24:00
I'm developing an attendance application using PHP with PostgreSQL as the database.
Here is my table:
ID - int, PK
Name - character varying(50)
In - time without time zone
Out - time without time ...
-2
votes
1answer
9 views
postgres syntax error in sql
select * from (
select max(h.updated_datetime) as max, min(h.updated_datetime) as min from report r, report_history h, procedure_runtime_information PRI, study S
where
...
0
votes
1answer
81 views
Select count doesnt returns all rows in table
I do have a table in the database, which supposed to have more than 1k rows. The DB is Postgress. I use the following command:
select count(*) from icdten; it returns 1000 which is wrong
and also
...
0
votes
0answers
18 views
Can not write to PPAS logfile
Now, I am testing query from application to PPAS via PgPool.
When I query
select a.name, b.name from tb_01 a, tb_02 c where a.id = b.id_ref
... or update/insert, I have to wait for a long time ...
Community Bulletin
Related Tags
sql × 4551ruby-on-rails × 1648
database × 1613
php × 938
mysql × 890
java × 855
python × 817
query × 711
django × 694
heroku × 654plpgsql × 637
ruby-on-rails-3 × 607
postgresql-9.1 × 449
ruby × 443
hibernate × 432
postgis × 397
activerecord × 367
performance × 336
jdbc × 333
database-design × 285
triggers × 272
stored-procedures × 229
c# × 227
psycopg2 × 224
sql-server × 207
