spot7.org logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
  Home » POSTGRESQL » Page 1
PostgreSQL trigger "WHEN" does not work
A possible cause (not enough information to be sure): Your conditions do not catch any changes from / to NULL. Either column1 and column2 are defined NOT NULL or you need to adapt: WHEN (OLD.column1 IS DISTINCT FROM NEW.column1 AND OLD.column2 IS DISTINCT FROM NEW.column2) Another typical cause: other triggers intervening. You'd have to list all triggers in the question.

Categories : Postgresql

Serialization exception : Who else was involved?
First, look at the main PostgreSQL log file. On my box, it's under /var/log/postgresql. After that, there are a lot of options for logging and error reporting. You can log connection attempts, and you can log every SQL statement. You should be able to put together a pretty good picture of what the server was doing. I think you should read most of that linked documentation. Some of the options ha

Categories : Postgresql

PostgreSQL tsrange: is it correct for lower_inf('(-infinity,today)'::tsrange) to be false?
The confusion stems from two different meanings of "infinity" here. timestamp types accept special values for infinity and -infinity. Range types have a general concept for ranges without lower / upper bound. The functions to test for it are called lower_inf() and upper_inf(), but they are really testing for "no bound" in the range. Ranges with no upper / lower bound include the value infinity /

Categories : Postgresql

Understanding PostgreSQL roles and security, particularly under replication
It is not necessary to create a new user for replication, but it's strongly preferred, because it reduces the privilege of the replication role. You have to store the credentials of the replication role somewhere, or configure Pg so no stored credentials are required (as in the example above, which relies on IP addresses). You really don't want the superuser login just lying around on a replica,

Categories : Postgresql

Retrieve the inserted/updated/deleted record in same function in postgres
Use the RETURNING clause. insert into sampletable(id,categoryname,categorydesc,createdby) values(default,name,descsciption,userid) returning * e.g. (Untested): CREATE OR REPLACE FUNCTION SampleFunction (in name varchar, in descsciption varchar, in userid varchar) RETURNS SETOF sampletable AS $BODY$ begin RETURN QUERY insert into sampletable(id,categoryname,categorydesc,created

Categories : Postgresql

Managing postgresql views without having to write migrations?
What you are describing is just another type of migration that get reversed on deploy. This may make sense for your business needs, and if you get blocked by this technique, you can always fall back to the regular migration system. The best way to implement such a system in PostgreSQL is to create a schema that you drop on deploy. This way you don't have to create all the DROP VIEW ... commands,

Categories : Postgresql

Is there any way to avoid PostgreSQL placing the updated row as the last row?
You could make some space in your tables for updates. Change the fill factor from the default 100%, no space for updates left on a page, to something less to create space for updates. From the manual (create table): fillfactor (integer) The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT

Categories : Postgresql

Possible to let the stored procedure run one by one even if multiple sessions are calling them in postgresql
I would recommend making sure the stored procedure uses SELECT FOR UPDATE, which should prevent the same row in the table from being accessed by multiple transactions. Per the Postgres doc: FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being modified or deleted by other transactions until the current transaction end

Categories : Postgresql

PostgreSQL: best way to join small subsets of large tables
As you alluded to, the only way to truly know is to compare the execution plans. In fact, the best way would be to use EXPLAIN ANALYZE, so that it actually executes the query and inserts the results into output with the estimates, so you can get a sense of the query planner versus reality. However, in general, what I would do in a situation like this would probably be to create a temp table for c

Categories : Postgresql

Postgres types of result columns
Since a value has a type (deduced by the planner) even when its value is NULL, you can create a dummy row consisting only of NULLs, and test the type of the NULLs. You can do that using a UNION, where the column types are determined based on the first (empty) result set, and the second result set simply has the correct number of NULLs. Here's an example which I just tested on SQLFiddle; more comp

Categories : Postgresql

Is it possible to produce an ordered batch of events with an attribute stating the event (ordered) position?
I don't think that currently exists. Esper project's JIRA is at http://jira.codehaus.org/browse/ESPER and JIRA accounts can be created at http://xircles.codehaus.org/

Categories : Postgresql

BookshelfJs failing to bring in nested relationship on create
I'm not completely clear what you are trying to achieve, but here is how I would generally set things up. First I'd create a base model (assuming its saved as base.js), I think you are going to have some problems with circular dependencies, so using the Bookshelf registry plugin would be good: var config = { client: // whatever client you are using, connection: // url to your database }; var

Categories : Postgresql

Postgresql server will not start after OS X Yosemite upgrade, homebrew
I ran into the same issue and after looking at the log file I found one directory was missing: $ tail /usr/local/var/postgres/server.log FATAL: could not open directory "pg_tblspc": No such file or directory Then I did $ mkdir /usr/local/var/postgres/pg_tblspc, restarted PostgreSQL and got: $ tail /usr/local/var/postgres/server.log FATAL: could not open directory "pg_replslot": No such file

Categories : Postgresql

Left outer join - how to return a boolean for existence in the second table?
A CASE or COALESCE statement with an outer join IS the proper way to do this. select CASE WHEN b.id IS NULL THEN true ELSE false END AS banned, u.id, u.first_name, u.last_name, u.city, u.last_ip, to_char(u.login, 'DD.MM.YYYY') as day from pref_users u left outer join pref_ban2 b on u.id=b.id limit 10;

Categories : Postgresql

How to optimize a PostrgreSQL query and avoid using "NOT IN"?
NOT IN can be OK, you just have to think carefully about NULL, and an anti-join is often a better choice. For any query: SELECT ... FROM t WHERE col NOT IN (SELECT col2 FROM t2 WHERE col2 IS NOT NULL AND ...predicate...) you can equivalently write: SELECT ... FROM t LEFT OUTER JOIN t2 ON (t.col = t2.col2 AND ...predicate...) WHERE t2.col2 IS NULL; which is called a "left anti-join". Postgr

Categories : Postgresql

Get the Size of Multiple Tables in One Query POSTGRES?
Following select query will returns all the table and its size's SELECT relname as mytable, pg_size_pretty(pg_relation_size(relid)) As size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; Create a VIEW with this select CREATE VIEW vTableAndSize AS SELECT relname as mytable, pg_size_pretty(pg_relation_size(relid)) As size FROM pg_catalog.pg

Categories : Postgresql

Escaping Backslash using Knex.raw for Copy command in Postgres
copy is a psql (client) command, which wraps Postgres's COPY (server). It sounds like perhaps you're trying to combine the two, and that's where the problem is. Since knex.js is a query builder, it wouldn't know about copy -- COPY would not be valid SQL to use in a client scenario. You mention that you don't have superuser access, which, if you want to reference a file, is required for COPY. If

Categories : Postgresql

chaining sql queries in a WITH clause to eventually perform an update
It looks like you're expecting the UPDATE (the "main query") to find a row that is inserted in the same SQL statement, a row that doesn't exist at the start of the statement. If that's the case, this can't work as documented in Data-Modifying Statements in WITH : The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifyin

Categories : Postgresql

n_dead_tup vs dead_tuple_count in postgresql?
pgstattuple scans the tables and calculates real-time results. It can be quite slow for a big table, but produces accurate results. Access to the pg_stat views, directly or via functions like pg_stat_get_dead_tuples, uses the most recent data collected by ANALYZE. So it can be out of date, especially if you just made big changes. However, it's very fast to access. If you ANALYZE the table, the s

Categories : Postgresql

Postgres uses wrong index
Very common values in the predicates for ssid_id and ap_id can make it cheaper for Postgres to pick the smaller index session_lim_values_idx (only 1 date column) over the seemingly better fit, but bigger index session_date_ssid_apid_idx (4 columns) and filter the rest. In your case around 4 % of the rows have ssid_id=42 AND ap_id=1731. That shouldn't normally warrant the switch to the smaller ind

Categories : Postgresql

Error importing postgres DB dump into empty database
I think the most common cause is a "corrupt" SQL file. There's no easy fix. Split the file in half (man split), fix the SQL statements at the tail end of one resulting file and at the head end of the other, and run again. In the past, I seem to recall seeing "Invalid byte sequence for UTF-8", or something like that. I'm sure there are other causes. PostgreSQL has a lot of logging options; set the

Categories : Postgresql

Postgres drop type XX000 "cache lookup failed for type"
What seems to be the case here is a race condition. Your clients calls tmp._my_test_function() and while that is being set up by interpreting the source code of the function, you drop the function and the enum type and then re-create them. Internally, virtually all objects are referenced by their oid (with a value of 386318 for the type in your case) so the function and the enum in your client fun

Categories : Postgresql

Postgres split LineStrings and convert
Use ST_DumpPoints like this: SELECT ST_X(d.geom), ST_Y(d.geom) FROM ST_DumpPoints( 'Linestring(3.584731 60.739211,3.590472 60.738030,3.592740 60.736220)') AS d; st_x | st_y ----------+----------- 3.584731 | 60.739211 3.590472 | 60.73803 3.59274 | 60.73622 (3 rows)

Categories : Postgresql

How to run functions every time postgresql starts?
This has been asked before: Set default limit for pg_trgm The initial setting is hard coded in the source. One could hack the source and recompile the extension. To address your comment: You could put a command in your psqlrc or ~/.psqlrc file. A plain and simple SELECT command in a separate line: SELECT set_limit(0.1) Be aware that the additional module is installed per database, while ps

Categories : Postgresql

rounding time in postgresql
Cast it to a timestamp(2), e.g. select value::timestamp(2) If you're really just using time I think you can also do, e.g. select value::time(2)

Categories : Postgresql

Authentication by identity file failed with error code -18
I was getting this continously on windows (VM). So I decided pgAdmin's built in ssh tunnel was no good and just used gSTM (On linux). Forwarding the port from the remote server 5432 to local host 5555 worked. Then I could just use pgAdmin III on Linux to connect. You could probably use command line like this if you don't want to use a graphical tool such as gSTM. ssh -fNg -L 5555:localhost:5432

Categories : Postgresql

PostgreSQL: Getting average value for rows matching a column
Something along these lines might work. select row_to_json(row) from (select "index" index_of_affiliation, max("created_at") max_created_at, round(avg("value")) avg_value from responses where "time" = '1416177080' group by "index" ) row ; In the long run, you're better off with more descriptive names than "index", "value", and "time".

Categories : Postgresql

Spring MVC with postgres and tomcat8
Just a wild guess here: In your datasource bean you have connection string as jdbc::postgresql://localhost:5432/myapp. It should probably be jdbc:postgresql://localhost:5432/myapp (only a single colon after jdbc).

Categories : Postgresql

Timestamp resolution
As you saw it in the documentation here, the resolution can be up to microseconds using the timestamp type. Here is an example that show you that microseconds are there: CREATE TABLE table_test ( column1 timestamp(6) without time zone, column2 timestamp(6) without time zone ); insert into table_test (column1,column2) values (current_timestamp, current_timestamp + interval '100 MICROSECONDS'

Categories : Postgresql

Creating a postgres user
I think your issue stems from two misunderstandings around GRANT: Granting on a database doesn't grant on tables within it, it only grants rights on the database object its self; and ALTER DEFAULT PRIVILEGES only affects tables created after that is run, it doesn't change existing tables. You want GRANT ... ON ALL TABLES IN SCHEMA ....

Categories : Postgresql

how function pg_ls_dir () work in postgresql 8.0.6?
The pg_ls_dir() function was introduced in PostgreSQL 8.1 so it's not available in 8.0. You could try adding the function to the installation manually but I would advise against it. Here's the source regardless: http://doxygen.postgresql.org/builtins_8h.html#a35b15cee7a44663710a7125c5b57484d

Categories : Postgresql

Return no-result keywords in select query in SQL
SQL Fiddle select pattern from res_country right join unnest(array['%A%', '%BF%', '%CD%', '%DA%']) p(pattern) on code like pattern where code is null ;

Categories : Postgresql

How plproxy select node when use "RUN ON hashtext(key)"?
It uses bitwise AND operation. This works because the tutorial you mentioned stipulates that the number of shards has to be a power of 2.

Categories : Postgresql

Postgresql: How @GeneratedValue works in postgresql?
K, so you're using AUTO strategy, as it's the default. I don't know much about Ebean, but with Hibernate, a sequence called hibernate_sequence is created. This sequence would be shared by other entities, so that might be part of the reason for the gaps. Your ORM might also be pre-allocating sequence values for performance reasons. Either way, I always use IDENTITY strategy with Postgres, even t

Categories : Postgresql

Can connect to postgres through localhost without password but not through 127.0.0.1. Why?
The behavior you see might be caused by a password file. The password file is conventionally named ~/.pgpass on Unix systems, but a different filename can be given through the PGPASSFILE environmental variable. I think a password file that contains a line for "localhost", but does not contain a line for "127.0.0.1" will show the behavior you're seeing. My own ~/.pgpass file contains this line.

Categories : Postgresql

seperate the Postgres WAL by schma?
No, it is not possible. Not only can't it be separated by schema, it also can't be separated by database either, within the same cluster/instance. To get separate WAL streams, you would have to run different database instances listening on different ports, with either dblink or foreign data wrappers. And then you would have problems with atomicity of commits.

Categories : Postgresql


Recently Add
Calling user defined functions with Create Table in PostgreSql
PostgreSQL trigger "WHEN" does not work
Serialization exception : Who else was involved?
PostgreSQL tsrange: is it correct for lower_inf('(-infinity,today)'::tsrange) to be false?
Understanding PostgreSQL roles and security, particularly under replication
Retrieve the inserted/updated/deleted record in same function in postgres
Managing postgresql views without having to write migrations?
Is there any way to avoid PostgreSQL placing the updated row as the last row?
Possible to let the stored procedure run one by one even if multiple sessions are calling them in postgresql
PostgreSQL: best way to join small subsets of large tables
Postgres types of result columns
Is it possible to produce an ordered batch of events with an attribute stating the event (ordered) position?
BookshelfJs failing to bring in nested relationship on create
Postgresql server will not start after OS X Yosemite upgrade, homebrew
Left outer join - how to return a boolean for existence in the second table?
How to optimize a PostrgreSQL query and avoid using "NOT IN"?
Get the Size of Multiple Tables in One Query POSTGRES?
Escaping Backslash using Knex.raw for Copy command in Postgres
chaining sql queries in a WITH clause to eventually perform an update
n_dead_tup vs dead_tuple_count in postgresql?
Postgres uses wrong index
Error importing postgres DB dump into empty database
Postgres drop type XX000 "cache lookup failed for type"
Postgres split LineStrings and convert
How to run functions every time postgresql starts?
rounding time in postgresql
Authentication by identity file failed with error code -18
PostgreSQL: Getting average value for rows matching a column
Spring MVC with postgres and tomcat8
Timestamp resolution
© Copyright 2017 spot7.org Publishing Limited. All rights reserved.