People who code: we want your input. Take the Survey

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

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

Copying the database from SQL Server to PostgreSQL

I currently have SQL Server 2005 and I want to make a copy of the entire database into PostgreSQL. I don't want to migrate to new PostgreSQL but just a entire copy. I almost have more than 1 TB of ...
2
votes
1answer
600 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 ...
5
votes
1answer
7k views

Postgres PgAdmin III Query Builder crash

I've been using Postgres and pgAdmin III for some years, but this morning I had a very BIG BIG problem. I'm using pgAdmin III 9.0 on Windows Xp operating system, on my laptop Dell Latitude E4300. ...
10
votes
2answers
6k 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 ...
13
votes
1answer
13k views

PostgreSQL Database Data File Integrity Check

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption? In the ...
17
votes
3answers
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 ...
2
votes
1answer
669 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 ...
19
votes
2answers
55k views

Is there a simple way in PL/pgSQL to check if a query returned no result?

I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this: select c.data into data from doc c where c.doc_id = id and c.group_cur > ...
28
votes
5answers
83k views

PostgreSQL: How to create full copy of database schema in same database?

How I can copy my public schema into the same database with full table structure, data, functions, fk, pk and etc. My version of Postgres is 8.4 P.S. I need to copy schema NOT database
2
votes
1answer
543 views

Query takes hours

Up until now all queries in my PostgreSQL application were fast. During the last days, sometimes a query takes several hours. Index are healthy, because after dump, restore, vacuum -z, it is still the ...
14
votes
2answers
5k views

Streaming replication and failover on PostgreSQL

I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is ...
6
votes
1answer
502 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
2k views

hstore for versioning fields in postgresql

I have a postgresql database that will hold about 50 tables, each of them having about 15 fields, it would have at least 300.000 rows on each table. In order to track the changes done on each field I ...
16
votes
5answers
68k views

PostgreSQL: The database cluster initialization failed

Both C:\PostgreSQL and C:\PostgreSQL\data have postgres user with full access and admin rights. I ran postgresql-9.1.2-1-windows.exe from postgres user as admin. Target C:\PostgreSQL Every way I try,...
30
votes
3answers
36k views

Configuring PostgreSQL for write performance

One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed ...
3
votes
1answer
6k 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?
14
votes
3answers
11k views

How to convert mysql to postgresql? [closed]

I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be: Free Works under Linux Simple to use and not buggy You actually tried and ...
5
votes
1answer
848 views

Database as a service (DbaaS) over dimensions--performance, scalability, & reliability?

MySql Database as a service: Amazon Xeround Another eg. PostgreSQL Database-as-a-Service. You can get the list of similar products here. Does anyone have details on their performance, reliability, ...
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 (...
1
vote
1answer
440 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/...
13
votes
4answers
18k views

No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00

I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
8
votes
2answers
14k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
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 ...
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 ~ # ...
132
votes
3answers
123k views

PostgreSQL multi-column unique constraint and NULL values

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), ...
14
votes
5answers
17k views

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql describes some nifty ways to change table and other objects to a specific user, and it works ...
8
votes
2answers
19k views

PostgreSQL: Query for location of global tablespace?

I have been trying to make our database clients pro-active about not filling up the partition on which the database they are using resides. As all our clients are on the same host as the database ...
18
votes
3answers
29k views

Automating failover in PostgreSQL 9.1

How does one setup two identical servers for automatic failover in PostgreSQL 9.1. OS Centos 5 PostgreSQL 9.1 compiled from source The postgres user account exists on both machines and has a ...
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
324 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
13k 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, ...
2
votes
3answers
850 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 ...
4
votes
2answers
4k views

Postgres uuid: Use as primary key, or in addition to SERIAL -for disconnected app-

For a new SASS app with postgres 9, that will support disconnected clients. The client not will use the regular IDs, only the UUID. The clients will be on iOS, html5/python and perhaps later on other ...
3
votes
2answers
2k views

How do I configure PostgreSQL 8.3 to log bind parameters?

I'm running into some unique constraint problems with my database. I can't reproduce them, but see error messages from our production system: exception 'UniqueViolation' with message 'SQLSTATE[...
7
votes
1answer
823 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 ...
5
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
824 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 ...
8
votes
2answers
4k 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 ...
1
vote
1answer
346 views

Show all variations of 2 characters in postgres

How would I do this in postgres : Lets say I have an x and a y and I want to show all possible combinations : xx yy xy yx How can I make postgres do this for me ?
6
votes
2answers
6k 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? ...
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 ...
6
votes
1answer
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 ...
13
votes
1answer
11k 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 ...
19
votes
3answers
22k views

How to promote an existing index to primary key in PostgreSQL

I know how to make a primary key within a table, but how do I make an existing index a primary key? I'm trying to copy an existing table from one database to another. When I show the table, the index ...
4
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 ...
2
votes
3answers
507 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 ...
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?
42
votes
2answers
20k 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
2answers
1k views

Role problem of PostgreSQL

I want to create a role named cp with some defined privileges, then we will create some other roles which will be granted with cp role. I know Oracle can do this job. For examle grant resources to ...
4
votes
2answers
7k 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
263 264
265
266 267
269