Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
4
votes
2answers
5k views

Should arrays of IDs stored in Postgres use varchar, int, or another type?

What is proper convention for creating a field that will hold an array of foreign keys? In this particular case, I'm creating a field called "owners" that will hold an array of user IDs. I want to ...
2
votes
2answers
3k 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 ...
2
votes
1answer
594 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 ...
16
votes
3answers
4k 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 ...
10
votes
2answers
5k 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 ...
2
votes
1answer
667 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 ...
6
votes
1answer
500 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 ...
2
votes
1answer
5k views

How to make it impossible for a postgres user to delete databases?

What yould be the sql statement (that the user postgres will execute) so it will be impossible for the postgres user user1 to delete (drop) databases? Or can I add a rule into some config file?
1
vote
1answer
419 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/...
2
votes
1answer
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 (...
18
votes
2answers
11k 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 ...
22
votes
1answer
6k 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 ...
3
votes
2answers
492 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())::...
3
votes
1answer
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 ~ # ...
0
votes
0answers
48 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 ...
2
votes
3answers
705 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 ...
1
vote
1answer
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 ...
1
vote
3answers
302 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 ...
8
votes
3answers
12k 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, ...
4
votes
2answers
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 ...
4
votes
1answer
821 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 ...
7
votes
1answer
799 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 ...
3
votes
1answer
1k 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 ...
8
votes
2answers
3k 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 ...
6
votes
2answers
5k 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? ...
6
votes
3answers
3k 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 ...
6
votes
1answer
4k 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 ...
2
votes
3answers
506 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 ...
2
votes
1answer
5k 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 ...
9
votes
1answer
10k 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 ...
2
votes
2answers
430 views

Database Transfer from PostgreSQL to MySQL

Is there any method to tranfer the data and schema from a postgresql database to mysql?
7
votes
4answers
17k 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?
5
votes
1answer
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 ...
29
votes
2answers
14k views

Force PostgreSQL clients to use SSL?

I have configured ssl = on in postgresql.conf (and installed a certificate etcetera). Does this ensure that all clients will always connect over SSL? (I.e. does ssl = on it make it impossible to ...
3
votes
3answers
606 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 ...
3
votes
2answers
6k 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.
2
votes
1answer
571 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 ...
5
votes
2answers
2k 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?
8
votes
3answers
2k 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 ...
6
votes
1answer
15k 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 ...
9
votes
1answer
104 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 ...
3
votes
1answer
7k 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 ...
5
votes
1answer
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
1answer
924 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 ...
13
votes
3answers
8k 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 ...
5
votes
1answer
4k 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 ...
1
vote
1answer
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 ...
3
votes
1answer
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 ...
3
votes
2answers
985 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 ...
14
votes
4answers
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), ...