Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
15,904
questions
2
votes
2
answers
4k
views
Update column by comparing date and string values together
I have problem in updating table.
I need to update table by comparing date and name with another table.
I have two tables:
table 1 : dim_sesid
name(varchar) role
...
- 163k
2
votes
1
answer
620
views
Rewriting a query
I have a database with the following structure (star schema):
facttable:
pros integer
mena integer
me integer
vahet double precision
slutttp timestamp without time zone
Structure of each ...
CommunityBot
- 1
17
votes
3
answers
5k
views
Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving?
Context:
Let's say that, while using Streaming Replication/Hot Standby on a Postgres 9.1 cluster, a standby node goes down. It stays down for an day, during which time a lot of DML occurs on the ...
- 846
10
votes
2
answers
7k
views
Can PostgreSQL create a case sensitive database?
I'm porting some code from some version of Sybase to PostgreSQL. This is a C application that uses the Sybase client library. My approach is to write a translation layer that translates calls to ...
- 326
2
votes
1
answer
671
views
Why is a duplicate record inserted when it should not be?
Why does this command
insert into "deliveryLog" select * from temp_dellog where "deliveryID" < 15620652
give this output?
********** Error **********
ERROR: duplicate key value violates unique ...
- 658
6
votes
1
answer
509
views
in postgres, can one optimize a table across partitions?
in a database with many companies, segregating them using schema and search_path
almost all access comes at the company level, that is, limited to a single schema. some data-warehouse and accounting ...
- 38.7k
1
vote
1
answer
459
views
Using PL/PerlU with a non system perl lib
psql -1f check_email.pl.sql ccushing postgres
SET
psql:check_email.pl.sql:7: ERROR: Can't locate Email/Valid.pm in @INC (@INC contains: /usr/lib/perl5/site_perl /usr/share/perl5/site_perl /usr/lib/...
- 706
2
votes
1
answer
3k
views
Postgresql 9.1 replication delay
I just set up a PostgreSQL 9.1.2 database server on debian squeeze.
I configured a standby server (hot standby) on a second identical server (squeeze, PG 9.1.2).
On the master I set these parameters (...
- 2,228
23
votes
2
answers
17k
views
Trigger: move deleted rows to archive table
I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis.
I would like to have a table called restrictions_deleted, where ...
- 163k
24
votes
1
answer
7k
views
Is there a good way to run a trigger for each record in a postgres table?
I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the ...
- 163k
4
votes
2
answers
727
views
Plpgsql seems to be deleting and inserting instead of updating - Why?
I am using postgreSQL 7.4.
I have a large table , call it table_a:
key1 INT NOT NULL,
key2 INT NOT NULL,
data INT NOT NULL,
itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, (timeofday())::...
- 163k
3
votes
1
answer
2k
views
Any Idea how to migrate from PostgresSQL 8.4 to PostgresSQL 9.1.1 in opensuse12.1
I had a crashed opensuse 11.3 that's why I installed opensuse 12.1..
after I installed (via yast) and edited /etc/passwd
I do this:
rcpostgresql start
and this shows on the /var/log/messeges
~ # ...
- 9,580
0
votes
0
answers
49
views
postgresql view [duplicate]
Possible Duplicate:
postgresql view with max min with id
I have the following table:
CREATE TABLE trans (
id SERIAL PRIMARY KEY,
trans_date date,
trans_time time
);
I want ...
CommunityBot
- 1
2
votes
3
answers
1k
views
Replication chaining with PostgreSQL
We have an unusual replication scenario and I am not sure which tool would be appropriate for that. Basically, we have a legacy non-normalized database that we want to refactor into a normalized ...
- 501
1
vote
1
answer
2k
views
64 bit Postgres 9.1 Limitions on Windows 2008 R2?
When Deploying Postgres on Windows Server 2008 R2:
Are there any limitations on windows that don't exist on Linux?
Does the postgres scale in the same way on windows as it does on Linux?
Is ...
CommunityBot
- 1
1
vote
3
answers
361
views
PostgreSQL and query planner
I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to ...
- 38.7k
8
votes
3
answers
15k
views
PostgreSQL vs. Oracle High Availability solutions?
PostgreSQL has a matrix of different high availability options which represent many different ways of building replication into an RDBMS.
Here is the PostgreSQL High Availability, Load Balancing, ...
- 850
5
votes
2
answers
2k
views
VACUUM and VACUUM FULL do not return space to OS
I'm running Postgres 8.3.3. I have a postgres database on a Linux filesystem that is quite full. I've run VACUUM and VACUUM FULL, yet none of the space has been returned to the OS. When I observe ...
- 850
4
votes
1
answer
826
views
Single node parallelism with PL/Proxy?
I have seen some suggest that single node parallelism is possible, but all of the material I have found so far deals with parallelizing across nodes. I am interested in getting large static queries to ...
- 383
7
votes
1
answer
862
views
PostgreSQL partition hierarchy design for centralized logging
I am looking into setting up logging from all of my servers into a centralized postgresql database. It makes sense to be able to drop logs by date or by host, so I'd like to set it up with ...
- 38.7k
4
votes
1
answer
2k
views
Storing bitsets in PostgreSQL?
I need to calculate reach, grouped by different criterions, and would prefer to keep everything in my database. I'm looking for either a pre-built bitset implementation for PostgreSQL, or pointers on ...
- 350
10
votes
2
answers
5k
views
PostgreSQL: reuse of complex intermediate result in same query
Using PostgreSQL (8.4), I'm creating a view that summarizes various results from a few tables (e.g creating columns a, b, c in the view), and then I need to combine some of these results together in ...
CommunityBot
- 1
6
votes
2
answers
7k
views
What is the purpose of PGBouncer's parameter server_reset_query?
Recently I started learning about pgbouncer, but there are some things I do not understand about the parameter server_reset_query.
Question 1: What is the main purpose of server_reset_query?
...
- 10.1k
6
votes
3
answers
5k
views
Is there a need to keep refreshing the indexes from time-to-time?
This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL.
I have created a fresh database and the tables, at present, have no records. I have created ...
- 178k
6
votes
1
answer
5k
views
Does my choice of pg_dump format impact restore speed?
According to the docs for my version of Postgres (8.4.2), pg_dump can "output in script or archive file formats." The script-formatted dump (4GB) takes 1.5 hours to restore, and I'm wondering if the ...
- 6,119
2
votes
3
answers
515
views
New Job and Company uses PostgreSQL [closed]
I have used MySQL and SQL server all of my professional career.
The company I will be working for uses PostgreSQL.
Can someone who was/is in a similar situation please give me some insight on what ...
CommunityBot
- 1
2
votes
1
answer
6k
views
PostgreSQL replication: out-of-sequence timeline ID, when former master made slave
I've configured master slave replication in PostgreSQL, following this tutorial:
http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#Binary_Replication_in_6_Steps
It worked fine. Now I've ...
- 1,149
13
votes
1
answer
12k
views
Type conversion with default fallback value
In PostgreSQL (8.4), I'm trying to convert a string parameter into a date within a SQL query, falling back to now() when the string is not a valid date (or empty).
In "pseudo-SQL", this would be ...
- 38.7k
2
votes
2
answers
524
views
Database Transfer from PostgreSQL to MySQL
Is there any method to tranfer the data and schema from a postgresql database to mysql?
- 178k
7
votes
4
answers
18k
views
Visualization of MySQL/PostgreSQL table structure as a graph/UML
Is there any program, source code or API that will take a MySQL or PostgreSQL schema and
spit out a UML or some other graph visualizing of it?
- 178k
5
votes
1
answer
1k
views
PostgreSQL pl/perl trigger, differentiate null vs empty
I've been trying to speed up a generic auditing trigger that I had written in pl/pgsql some time back. On update, it generates a list of columns in the table being updated and inserts rows in the ...
- 178k
3
votes
3
answers
619
views
Can I limit access to postgresql 8.4 databases via ODBC on the server side?
I have a bit of a tricky problem at the moment that I could really use some help to solve. I'll do my best to explain it but please feel free to ask questions :-)
We have a postgresql 8.4 database ...
- 6,119
7
votes
2
answers
8k
views
Reference a non primary key in another table in postgresql
I have a two columns should references two columns in another table. And those two columns are not primary key. I think I can't use foreign key, because it can only references primary keys.
- 1,558
2
votes
1
answer
620
views
How to perform asynchronous cascade slave replication of PostgreSQL database?
This is a feasibility/architectural question (I am no database administrator). Let's imagine a PostgreSQL database running online (like those from Heroku for example).
One would like to maintain a ...
- 1,791
6
votes
2
answers
4k
views
Is there any index with O(1) complexity for lookup in PostgreSQL?
Is there any index type in PostgreSQL that gives O(1) complexity for lookups? on strings?
- 273
9
votes
3
answers
3k
views
Postgres, MVCC, and Locking
I have a series of SQL statements that look like the following:
BEGIN;
SELECT counter FROM table WHERE id=X FOR UPDATE;
REALLY COMPLEX QUERY;
UPDATE table SET counter=Y WHERE id=X;
END;
I'd like to ...
- 273
6
votes
1
answer
19k
views
How can I generate scripts for all functions in pgAdmin?
I am using pgAdmin version 1.10.5, and I've checked the properties and menu options but couldn't find anything on the subject.
For example, I just want to generate a script that includes the ...
- 23.2k
9
votes
1
answer
129
views
How can I see a query after rules have been applied?
From docs - 37.3.1.1. "A First Rule Step by Step"
CREATE TABLE shoelace_log (
sl_name text, -- shoelace changed
sl_avail integer, -- new available value
log_who ...
- 23.2k
4
votes
1
answer
8k
views
postgres and passwordless authentication
We have users for out postgres database and none have passwords... Do not know the history behind it.
When I try to use clients to connect to these databases, for instance, pgadmin III, the ...
reza
5
votes
1
answer
1k
views
Load Balancing / MPP with Postgres 9.1
I'm trying to setup a testbed for bigdata analytics (basically around 2 to 3 TB base, slight write [only feeding the db], but possibly read intensive queries for analytics purposes).
Since I'm ...
7
votes
1
answer
1k
views
Gaining visibility into long running INSERT INTO ... SELECT
I have a large SQL insert statement that updates a summary table. The process normally runs hourly, and takes about 5 to 10 minutes to calculate uniques over that period. I "foolishly" chose to run it ...
CommunityBot
- 1
13
votes
3
answers
10k
views
Should I use SQL JOIN or IN Clause?
I have a question about best approach. I am not sure which approach is best when data is considered variable in size.
Consider the following 3 TABLES:
EMPLOYEE
EMPLOYEE_ID, EMP_NAME
PROJECT
...
CommunityBot
- 1
6
votes
1
answer
6k
views
Unicode support of PostgreSQL and its performance
How is the Unicode support of PostgreSQL?
If I want to save Unicode data does it have to go through the conversion process while storing and retrieving? If yes then what is the performance of such ...
- 163k
1
vote
1
answer
1k
views
storing a mirror of IMAP/pop3 folder/inbox in a local DB, what choices are there?
I have a email inbox that will soon hit 4 million messages, I have an app that runs different policies over the inbox to associate emails with each other much like the conversation plugin for ...
Max
4
votes
1
answer
1k
views
Solution for deploying multiple instances of Django Server with Database replication
have written a Django application that reads / writes data into a postgres database, at the moment I have been deploying one of these devices per client. I am faced with having to deploy multiple ...
CommunityBot
- 1
3
votes
2
answers
1k
views
PostgreSQL: Separate tables vs single table to perserve disk space?
I have 2 tables with the below schemas with equal number of rows. When I run the SELECT relname, relpages FROM pg_class ORDER BY relpages DESC command, they show up as 23GB each even though the data ...
- 273
14
votes
4
answers
3k
views
Should I invest the time to change the column type from CHAR(36) to UUID?
I have a few million rows in my database already. I didn't know about the PostgreSQL UUID data type when I designed my schema.
One of the tables has 16M rows (about 3.5M to 4 M records per shard), ...
- 229
4
votes
2
answers
2k
views
Creating a GroupBy query to include a result for when no results match
Let's say I need a query to see sales results by Month and I want the result-set to include results for each month whether there were any sales in that month or not.
And let's say my table "sales" ...
- 830
4
votes
2
answers
929
views
Postgresql 9.0 adding new database to streaming replication
postgresql 9.0 streaming replication + hot standby is running. Now i need to add one more database to current cluster. What is the best way of doing this? I have a backup file about 20GB. should i ...
- 77.2k
10
votes
2
answers
9k
views
About "Transaction ID Wraparound"
Now , I read the document about "Transaction ID Wraparound " , but there are something that I really
don't understand, the document is the following url
http://www.postgresql.org/docs/9.0/...
CommunityBot
- 1