PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

0
votes
0answers
8 views

Column Datatype Conversion Question

I have a varchar column with text formatted like so "Sun Aug 07 17:43:55 +0000 2011" How would I convert that column in PSQL to a date datatype?
0
votes
0answers
10 views

To split tables is a good idea?

I have a table with more than 40 million rows and I want to know what is the best way I can optimize it for searches. The search will happen by the person's document number, and these document numbers ...
0
votes
0answers
11 views

how to use index to speed up sorting in postgres

I am using postgres 9.4. The messages has the following schema: Table "public.messages" Column | Type | Modifiers ...
0
votes
0answers
6 views

postgresql + pgbouncer, idle server and high cl_waiting

I have such a setup: OpenSUSE 13.1 64-bit Linux kernel 3.11.10 PostgreSQL 9.0 + PgBouncer 1.5.4 (libevent-2.0) 16 core AMD, 128 GB RAM shared_buffers=4GB work_mem=20MB max_client_conn = 15000 ...
0
votes
1answer
21 views

Does MySQL support querying for the current running transaction id

PostgreSQL supports retrieving the current running transaction id using a query like this: select txid_current(); Does MySQL have any such equivalent?
4
votes
1answer
30 views

Minimal grants for readonly single-table access on PostgreSQL

Following is a list of commands that seems to work to create new user (login) and grant readonly access for one specified table on PostgreSQL. Let's assume that these commands are executed on login ...
0
votes
1answer
24 views

postgresql rename a foreignkey after renaming a table

I renamed a table, and apparently a foreignkey in it doesn't get changed, and causing problems, Although when I \d error I get: "error_test_id_fkey" FOREIGN KEY (test_id) REFERENCES test(id) ON ...
0
votes
0answers
14 views

Login Roles not showing at previleges tab

Im migrating to a new server , created my login roles using plain sql querys on PgAdmin . Now when trying to grant privileges on database to the created login roles they don't show up as selectable ...
1
vote
0answers
24 views

Most efficient way to bulk insert or upsert data into multiple related tables

Given a PostgreSQL 9.4 schema with 3 related tables: CREATE TABLE a ( id BIGSERIAL PRIMARY KEY, a_name VARCHAR(80), a_field INTEGER ); CREATE TABLE b ( id BIGSERIAL PRIMARY KEY, b_name ...
0
votes
0answers
8 views

Log large queries with PGbouncer

Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?
-1
votes
2answers
31 views

How to use aggregate function and group by with inner join? (Postgres 9.3) [on hold]

I would like to select "extra" payments made in association with normal appointments for a one week period and SUM them up by contractor when calculating payroll. Here's what I attempted: SELECT ...
-1
votes
0answers
63 views

Should I use array in column or not? [on hold]

I have the following table: CREATE TABLE profits ( id serial NOT NULL, matriz name, profit2013 double precision[], profit2014 double precision[], profit2015 double precision[], CONSTRAINT ...
0
votes
0answers
24 views

How to catch code of error which occured inside plv8 function?

In plv8 function I am executing (plv8.execute(...)) INSERT INTO ... query which violates foreign key constraint. However pg client receives following (internal) error instead: { [error: Error: ...
3
votes
1answer
74 views

PostgreSQL function definition “syntax error at or near +” [on hold]

Suppose a simple query: (SELECT MAX(timestamp) FROM events e WHERE e.id < some_id) + ((SELECT MIN(timestamp) FROM events e WHERE e.id > some_id) - (SELECT MAX(timestamp) FROM events e ...
0
votes
0answers
17 views

How to move a specific Postgres database to different harddrive?

I have my postgres db running on harddrive d:\, and also all the tables are placed there by default. Now I want a specific table to be save to a different drive (say f:\). The table is not yet ...
1
vote
1answer
16 views

missing WAL file on standby server following a backup

I seem to have an intermittent problem with my postgres backup/restore to standby Postgres, is version 9.2 running on red hat 6 enterprise i have a production server (PROD1) and a hot standby ...
0
votes
0answers
39 views

PostgreSQL: Best way to INSERT for vertical search engine [on hold]

I'm trying to develop vertical search engine running on PostgreSQL & Go in Linux server. Data harvesting using Linux command line. For the INSERT query: The "best" I means is safe & high ...
1
vote
1answer
27 views

Is it possible for a client to kill a long-running Postgres query?

A client makes a request to a Postgres database for data. The database is too slow to respond. Is it possible for the client to send a CANCEL request or something to tell Postgres to stop working on ...
1
vote
2answers
23 views

What to do with WAL files for Postgres Slave reset

So last night our PG Slave ran out of space after a lot of reconfiguring of disk space, new drives etc its now reporting the following error: FATAL: could not receive data from WAL stream: FATAL: ...
0
votes
1answer
11 views

Postgres 9.3: How to update row that has max value of a timestamp in addition to other selection criteria?

I want to allow users to cancel recurring subscriptions. The easiest way to do this given the other infrastructure I've got is to set the recurring field = 0 for the user's latest appointment ...
1
vote
2answers
25 views

Can I select data inserted in the same uncommited transaction?

Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. ...
1
vote
2answers
46 views

Anything like a hash for Postgresql databases?

I have an export process I want to check. I want to verify that source and destination databases are equal. There are about 25000 rows, and schema is the same. Is there a way to somehow "hash" the ...
0
votes
1answer
24 views

Inner join on array column in Postgres

In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres. It looks like the names of such columns are contained in ...
1
vote
0answers
23 views

PostgreSQL DB freezes with large inserts

I am developing a web application with an SQLAlchemy ORM linked with PostgreSQL database backend. I have this usecase where a client uploads a file of about 20 MB containing large data. What I am ...
0
votes
0answers
17 views

What is the best way to analyze free space in a postgres database and the grow of usage?

In Oracle you can in datadictinary analyze free space by using SQL like: select b.tablespace_name, b.file_id, b.file_name, b.bytes/1024 total_KBytes, sum(a.bytes)/(1024) sum_free_Kbytes, ...
1
vote
1answer
21 views

Does index occupy disk memory for rows for which index expression is NULL?

Let's assume that I have following pg_trgm index on mydata JSONB column: CREATE TABLE mytable (mydata JSONB); CREATE INDEX trgm_index_1 ON mytable USING gin ((mydata#>>'{myfield}') ...
1
vote
0answers
13 views

PostgreSQL security threats in external modules or PL languages

In reading the answer to question I came across a paragraph that indicated that some PostgreSQL PL languages and/or contrib modules can be used to compromise the security of the system. Is this only ...
1
vote
1answer
32 views

pg_ctl status responds with “available” during failed startup

I am using pg_ctl -D $PGDATA status to monitor whether my postgres standby database is up and running. Sometimes the database fails to start due to a missing WAL file, but its status is reported as ...
0
votes
2answers
42 views

How to use except but excluding a column from the except clause comparison -psql? [closed]

I want to compare two sets of columns but exclude one column from the comparison as it will never be in the second table and I need it on my result. So, explaining: columns for both tables: ID ...
1
vote
1answer
30 views

Postgres - copy table INCLUDING CONSTRAINT doesn't include constraint

How come that statement don't copy constraints if I explicitly ask for it? CREATE TABLE backup_schema.permissions (LIKE public.permissions INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES ...
1
vote
0answers
15 views

Optimizing a geo search in Postgres

I'm searching for UK postcodes within a radius of a given postcode. The query that I'm using looks like this: SELECT *, earth_distance(ll_to_earth( {$lat}, {$lon} ), ll_to_earth(lat, lng)) as ...
1
vote
1answer
35 views

Can't connect to EC2 Postgresql instance unless I change the default port 5432 [closed]

I just created a brand new EC2 instance with a default security group allowing All Traffic from Anywhere. My listen_address is set to * I can SSH to it but I can't connect to the pgsql instance ...
2
votes
1answer
55 views

Can this UPDATE be faster with indexes?

update line_items set product_id = dups.latest_product_id from line_items as li join products p on li.product_id = p.id join vendors v on p.vendor_id = v.id join vendorgroups vg on ...
0
votes
1answer
41 views

Limit the number of votes

I have a table of content. Users can vote on a content row. They can vote up to three times. I'm looking for the most efficient way to make sure they don't go over three votes. I can think of two ...
1
vote
0answers
21 views

debugging PostgreSQL serialization failures

I am trying to migrate our PostgreSQL 9.4 database from transaction level READ COMMITTED to either REPEATABLE READ or SERIALIZABLE. In either case, I'm exposed to a new set of errors with the format: ...
1
vote
1answer
43 views

PostgreSQL JSON data type used as NOSQL, But VIEW as Relational Data Structure

Could I create a VIEW from the JSON data type but have it as a relational structure? Example: SELECT * FROM json_test; Results [ { "name": "Roy", "Country": "USA", ...
4
votes
1answer
105 views

Combine multiple queries into 1 query

I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages: \d messages Table ...
0
votes
1answer
58 views

How to upgrade postgresql 8.4 to 9.4

I want to upgrade my postgresql 8.4 to 9.4. the documentations are not clear to me. Will I lose my old databases if I do the upgrade? How can I backup my old databases if I am to lose them after ...
3
votes
1answer
53 views

Sharing PostgreSQL database on one dual-boot machine without duplication (cross-platform)

I have PostgreSQL 9.4 installed on a dual-boot machine (both on Win 7 and Ubuntu 14.10). The two systems share a drive (NTFS-formated) which includes a PSQL database directory which was created under ...
1
vote
2answers
34 views

postgreSQL hot_standby pg_dump on slave

I have master-slave (hot_standby) pgsql configuration and I want to make backups from one of my slaves. I don't want PITR, just good old database dump. I switched to postgres from MySQL. With MySQL ...
1
vote
1answer
28 views

RHEL 6: postmaster dead but pid file exists

I am using PostgreSQL 9.2 on RedHat 6 I'm a little confused as to why the following is happening: When postgres is running, performing "service postgresql92-postgresql status" as root results in ...
0
votes
0answers
15 views

Nicely managed lookup tables [migrated]

We have a people table, each person has a gender defined by a gender_id to a genders table, | people | |-----------| | id | | name | | gender_id | | genders | |---------| | id | ...
3
votes
2answers
55 views

Performance hickups after Postgres 9.4 upgrade

After upgrading our database from 9.3.5 to 9.4.1 last night, the server suffers from high CPU spikes. The upgrade was done with pg_dump. So the database was converted to SQL and then imported into ...
0
votes
0answers
16 views

How to display additional column in select with true/false value [SOLVED]

Solved. Look in comment. I am using PostgreSQL 9.1. I have 3 tables: simcards: id serial NOT NULL, iccid character varying(30) NOT NULL, customer_id integer customer: id serial NOT NULL, name ...
0
votes
1answer
40 views

Is anyone else experiencing consistency issues with postgres-xl?

Just like the title says. I have been playing with postgres-xl for over a month and have been seeing strange behaviors. I am using the docker container referenced from the postgres-xl.org site and ...
0
votes
0answers
17 views

rt_raster_to_gdal: Could not load the output GDAL driver

I have already set the environment in windows SET postgis.gdal_enabled_drivers = 'ENABLE_ALL'; Even I tried by adding following in environment path- POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL ...
2
votes
2answers
24 views

Aggregate all joined rows with where clause

Three nested tables exist: user_groups, users, projects. Each user_group has many users and, in turn, each user has many projects. Each project has an ordinal assigned, 1-5 (step_number). I would ...
0
votes
2answers
42 views

Automatically set value on child rows when deleting parent row?

Description We have a table and data below : Version: PostgreSQL 9.3 Table name: tree_data (id int, code text, name text, parent_id int) Primary key: id Foreign key: parent_id (refer to id) and ...
1
vote
1answer
20 views

Unable to create read only user— postgresql 9.4

I am using PostgreSQL 9.4, I want to make a user with permanent read only access on public schema. I have tried many different ways to get this but using REVOKE ALL on schema public to user1; GRANT ...
0
votes
1answer
25 views

Insert sum of the row data of each record into separate column in postgreSQL?

I have a table id name class social science math Total 2 Max Three 85 56 85 3 Arnold Three 55 40 75 4 Krish Four 60 50 70 ...