postgresql

PSQLException: current transaction is aborted, commands ignored until end of transaction block

by

I am seeing the following (truncated) stacktrace in the server.log file of JBoss 7.1.1 Final: Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.6.0_23] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [rt.jar:1.6.0_23] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [rt.jar:1.6.0_23] at java.lang.reflect.Method.invoke(Method.java:597) [rt.jar:1.6.0_23] … Read more

Postgres unique constraint vs index

by

As I can understand documentation the following definitions are equivalent: create table foo ( id serial primary key, code integer, label text, constraint foo_uq unique (code, label)); create table foo ( id serial primary key, code integer, label text); create unique index foo_idx on foo using btree (code, label); However, a note in the manual … Read more

ERROR: there is no unique constraint matching given keys for referenced table “bar”

by

Trying to create this example table structure in Postgres 9.1: CREATE TABLE foo ( name VARCHAR(256) PRIMARY KEY ); CREATE TABLE bar ( pkey SERIAL PRIMARY KEY, foo_fk VARCHAR(256) NOT NULL REFERENCES foo(name), name VARCHAR(256) NOT NULL, UNIQUE (foo_fk,name) ); CREATE TABLE baz( pkey SERIAL PRIMARY KEY, bar_fk VARCHAR(256) NOT NULL REFERENCES bar(name), name VARCHAR(256) … Read more

Select rows which are not present in other table

by

I’ve got two postgresql tables: table name column names ———– ———————— login_log ip | etc. ip_location ip | location | hostname | etc. I want to get every IP address from login_log which doesn’t have a row in ip_location. I tried this query but it throws a syntax error. SELECT login_log.ip FROM login_log WHERE NOT … Read more

How to upgrade PostgreSQL from version 9.6 to version 10.1 without losing data? [closed]

by

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers. We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations. Closed 9 months ago. The community reviewed whether to reopen this question 9 … Read more

What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

by

Since Postgres came out with the ability to do LATERAL joins, I’ve been reading up on it, since I currently do complex data dumps for my team with lots of inefficient subqueries that make the overall query take four minutes or more. I understand that LATERAL joins may be able to help me, but even … Read more

django test app error – Got an error creating the test database: permission denied to create database

by

When I try to test any app with command (I noticed it when I tried to deploy myproject using fabric, which uses this command): python manage.py test appname I get this error: Creating test database for alias ‘default’… Got an error creating the test database: permission denied to create database Type ‘yes’ if you would … Read more

How to create user for a db in postgresql? [closed]

by

Closed. This question is off-topic. It is not currently accepting answers. Want to improve this question? Update the question so it’s on-topic for Stack Overflow. Closed 9 years ago. Improve this question I have installed PostgreSQL 8.4 on my CentOS server and connected to root user from shell and accessing the PostgreSQL shell. I created … Read more

Using current time in UTC as default value in PostgreSQL

by

I have a column of the TIMESTAMP WITHOUT TIME ZONE type and would like to have that default to the current time in UTC. Getting the current time in UTC is easy: postgres=# select now() at time zone ‘utc’; timezone —————————- 2013-05-17 12:52:51.337466 (1 row) As is using the current timestamp for a column: postgres=# … Read more