w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
  Home » POSTGRESQL » Page 1
could not Convert legacy permission algorithm to 5 while migrating from liferay 5.2.3 to 6.0.6 tomcat with PostgreSQL
the problem was solved : I followed this link http://vetal-liferay.blogspot.com/2014/01/liferay-migration-form-523-to-62.html but instead of changing the permission.user.check.algorithm in liferay 6.0.6 i have changed it in 6.1.1.

Categories : Postgresql

All combinations for 'x' and 'y'
I'm not sure understanding your intent perfectly, but perhaps the following would be close to what you want: <!-- language: lang-sql --> select x||y from (select 'x'::text as x union select 'y'::text as x) x cross join (select 'y'::text as y union select 'x'::text as y) y Regards

Categories : Postgresql

PostgreSQL: how to insert null value to uuid
If the column is defined NOT NULL, you cannot enter a NULL value. Simple as that. uuid in the error message: ERROR: null value in column "uuid" violates not-null constraint is the name of the column: uuid | character varying(36) | not null Not the data type of address_id. And this column is obviously defined NOT NULL. Your INSERT statement does not include uuid in the target lis

Categories : Postgresql

postgresql date diff between two date with two table and two fields
Just subtract them. As both are dates the result of the difference is the number of days. select p.houseid, t.transdate - p.date from tblprop p join tbltrans t on t.houseid = p.houseid; If both were timestamp columns you'd get back an interval (rather than an integer representing days)

Categories : Postgresql

How to customize heroku CLI?
It doesn't seem like you can pass flags to pgbackups:capture. You can, however, uses pg_dump directly. pg_dump DATABASE_NAME -h DATABASE_HOST -p PORT -U USER -F c -a -x -O -f db.dump You can get the database values by running heroku pg:credentials DATABASE_URL You can also use the plugin a colleague and I wrote: parse_db_url. This will let you run a command like heroku pg:parse_db_url --format

Categories : Postgresql

Heroku postgres no longer overwrites config/database.yml Rails 4.1.4 (and other questions)
You need to go to https://postgres.heroku.com/databases There find the database of your app and you'll find the appropriate keys for your database. You can upload these as config variables and load them into your database.yml Look into figaro for an easier way to handle your config variables.

Categories : Postgresql

How can I fetch a NULL boolean from Postgres in Go?
See http://golang.org/pkg/database/sql/#NullBool in the standard library. NullBool represents a bool that may be null. NullBool implements the Scanner interface so it can be used as a scan destination, similar to NullString.

Categories : Postgresql

Postgres - Configuration Change to Vacuum Requires Restart?
When a value in postgresql.conf is commented out, that means the default is used. autovacuum defaults to on. Run: SHOW autovacuum; to see the current value. For more detail about a parameter use pg_settings: postgres=> x Expanded display is on. postgres=> select * from pg_settings where name = 'autovacuum'; -[ RECORD 1 ]--------------------------------- name | autovacuum settin

Categories : Postgresql

sonarqube 4 server migration
How is your ..confsonar.properties? Normally, you should have changed: sonar.jdbc.url=jdbc:oracle:thin:@localhost/XE Did you use some tool to migrate Oracle to Postgress? I thought it was not possible. Do not hesitate to ask for further precision. Regards.

Categories : Postgresql

PostgreSQL: how to periodically copy many tables from one database to another
I believe I may have found a way. Of course suggestions and comments are more than welcome. My idea is similar to what was suggested here: I want to restore the database with a different schema (not in the most voted answer, though). Since I am only interested in tables in the public schema, I did the following: Backup of the public schema of database1 "C:Program FilesPostgreSQL9.3inpg_dump.ex

Categories : Postgresql

Find row where keyword is like any reference in pg array of references
Seems you need alias search. It is usually convinient to implement using FTS (full-text-search) built in PostgreSQL. I'll describe it brifely. CREATE TABLE test.entity (pkey SERIAL NOT NULL, description text); Then we create alias table. Our goal is to separate original entity from its aliases. CREATE TABLE test.aliases ( talias tsquery NOT NULL, tentity tsquery, CONSTRAINT

Categories : Postgresql

How to use "RAISE INFO, RAISE LOG, RAISE DEBUG” to track log in PostgreSQL function?
They can either be output to the Postgres log, reported back to the client, or both. These are controlled by server-side settings, log_min_messages and client_min_messages. See the following doc for more details: http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html http://www.postgresql.org/docs/current/static/runtime-config-logging.html As @a_horse_with_no_name sugge

Categories : Postgresql

Creating a specific exception for a function in PL/PgSQL
You need the RAISE statement to raise an exception. There are examples in the manual. RAISE EXCEPTION .... (This looks like homework and you've asked about homework before, so I'm intentionally not giving a complete answer).

Categories : Postgresql

log shipping process (archive_timeout)
If you want timely replication, I suggest enabling streaming replication as well as log shipping. The main purpose of archive_timeout is to ensure that, when you're using log shipping for PITR backups, there's a maximum time window of data loss in situations where the server isn't generating lots of WAL so segment rotation would otherwise be infrequent.

Categories : Postgresql

POSTGRESQL store a gzip or json as text
The most efficient way to store this would be to extract the image data, base64-decode it, and store it in a bytea field. Then store the rest of the json in a json or text field. Doing that is likely to save you quite a bit of storage because you're storing the highly compressed JPEG data directly, rather than a base64-encoded version. If you can't do that, or don't want to, you should just shove

Categories : Postgresql

Running PostgreSql inside Docker
The line RUN /etc/init.d/postgresql start only serves to start Postgres while your image is being built. To ensure it is running at execution, you will want to create a script as entrypoint (using either ENTRYPOINT or CMD depending on what you want, which starts Postgres and runs your application. The simplest form of this would be something like #!/bin/sh /etc/init.d/postgresql start exe

Categories : Postgresql

Run all migrations from the beginning for a Heroku app
This is current as of November 2014. A lot of answers are outdated since the commands PG supports have changed. $ heroku pg:reset DATABASE Then you will be prompted to confirm by typing the name of your app. $ heroku run rake db:migrate

Categories : Postgresql

postgresql-simple query error
OK, Thanks to @AlpMestanogullari, @muistooshort, I got an answer here. My final code is: {-# LANGUAGE OverloadedStrings #-} module Main where import Control.Applicative import Database.PostgreSQL.Simple import Database.PostgreSQL.Simple.FromRow data Gerund = Gerund { f :: String, s :: String, t :: String } deriving (Show) instance FromRow Gerund where fromRow = Gerund <

Categories : Postgresql

How to preserve additional keys when using "SELECT DISTINCT"?
Maybe something like this? SELECT DISTINCT e.sid, e.cid, ip_src, ip_dst FROM event e INNER JOIN sensor s ON (s.sid = e.sid) INNER JOIN iphdr i ON (i.cid = e.cid) AND (i.sid = e.sid) WHERE timestamp >= NOW() - '1 day'::INTERVAL; If you want the combination of (signature, ip_src, ip_dst) to be unique in the result (one row for each combination) then you can try something like this: SELECT m

Categories : Postgresql

SQL exclude a where clause if the variable is null or empty
SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE (t1.name = nameVariable OR nameVariable IS NULL) AND (t2.field = fieldVariable OR t2.field IS NULL OR fieldVariable IS NULL) ;

Categories : Postgresql

How to Calculate Cost of Goods Sold
SQL Fiddle The approach in this solution is to expand each row with n items to n rows with a single item using generate_series. Then building two separate sets for purchased and sold. Each set's rows are numbered in the inventory_id order so it is possible to eliminate the purchased items that have already been sold. Then order by the inventory_id and limit to the quantity. create or replace fun

Categories : Postgresql

Postgres JDBC Numbered Parameters
No, this isn't possible. JDBC only has positional parameters, and therefor the PostgreSQL driver will render it as: PREPARE mystatement AS SELECT mycolumn->>$1, count(*) FROM mytable GROUP BY mycolumn->>$2 And as the value of $1 is not necessarily the same as $2, the parser of PostgreSQL will reject it as you are potentially not grouping on the same column. Th

Categories : Postgresql

UndefinedFunction: ERROR: function st_distance(geography, geometry, numeric) does not exist
You are mixing geography and geometry types, that is what the error message means. If you look at the ST_DWithin docs, you will see that the signature are ST_DWithin (geometry, geometry, distance) or ST_DWithin (geography, geography, distance). I don't know much about Ruby, so am not sure if there is a GEOM_Factory equivalent of the GEO_Factory you have used, but if you use ST_GeogFromText inste

Categories : Postgresql

Trying to add an Array Column to existing Postgres table
You need to specify a datatype. If you want an array of strings, use text: ALTER TABLE candidate ADD COLUMN blocked_companies text[]; if you want an array of numbers, use int: ALTER TABLE candidate ADD COLUMN blocked_companies int[]; More details can be found in the manual: http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-DECLARATION But in most cases using arrays is not su

Categories : Postgresql

postgres ALTER TABLE being blocked
You have a prepared transaction in place. Prepared transactions - those where PREPARE TRANSACTION but not COMMIT PREPARED or ROLLBACK PREPARED has been run - hold locks, just like normal running transactions do. Prepared transactions may be used by XA transaction managers, JTA, etc, not necessarily directly by your app. Many queuing systems use them too. If you don't know what the transaction is

Categories : Postgresql

Postgres Full Text Search on multiple columns
The english dictionary doesn't stem nicknames. regress=> SELECT to_tsvector('english', 'Christopher'), to_tsquery('english', 'Chris'); to_tsvector | to_tsquery ---------------+------------ 'christoph':1 | 'chris' (1 row) You'll need to overlay a dictionary that maps nicknames too, so christopher can be stemmed to chris.

Categories : Postgresql

Why my psql set timezone not work?
It does work, exactly as advertised. Refer to the documentation for SET: SET only affects the value used by the current session.. [..SESSION] Specifies that the command takes effect for the current session. (This is the default if neither SESSION nor LOCAL appears.) Each psql client connection establishes a new session that is terminated when the client disconnets. See this answer for

Categories : Postgresql

Postgresql insert is adding trailing zeros to numeric
If you specify a precision and scale, Pg pads to that precision and scale. regress=> SELECT '0'::NUMERIC(8,4); numeric --------- 0.0000 (1 row) There's no way to turn that off. It's still the same number, and the precision is defined by the type, not the value. If you want to have the precision defined by the value you have to use unconstrained numeric: regress=> SELECT '0'::NUMERIC

Categories : Postgresql

Running JRuby/Rails app "off-premises" using Heroku Postgres
It looks like I was able to fix this by adding all of my database connection details to database.yml: production: adapter: postgresql url: <%= ENV['RAILS_PG_URL'] %> database: <%= ENV['RAILS_PG_DATABASE'] %> port: <%= ENV['RAILS_PG_PORT'] %> host: <%= ENV['RAILS_PG_HOST'] %> username: <%= ENV['RAILS_PG_USER'] %> password: <%= ENV['RAILS_PG_PASSWORD']

Categories : Postgresql

Check Postgres access for a user
You could query the table_privileges table in the information schema: SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee='MY_USER'

Categories : Postgresql

Postgresql stops working for remote clients after some time
It is highly likely that the issue here is a connection tracking firewall,r router or NAT device that's expiring connection tracking entries. Enable TCP keepalives, either server-side, or if your client supports it, client-side.

Categories : Postgresql

Postgresql password and dokku
You shouldn't have to create a new database for the app. If it's already been created, you should only see a response that database has been created. This is achieved by dokku postgres:link app db Where app is the name of the app you wish to link and db is the name of the database you've previously created. What will happen is the DATABASE_URL will be exposed in the app by an environment vari

Categories : Postgresql

How to CAST a value in PostgreSQL for use in WHERE with LIKE statement?
The syntax could be: WHERE n0_.anno::text LIKE '%45%'; You need to cast the number to text (or varchar) before you can use it with the LIKE operator. The right hand argument for LIKE is a text value. Your input is a string literal to be precise. You need single quotes for values, double quotes are for identifiers. If anno is supposed to hold a year and you are just interested in the last two

Categories : Postgresql

Deadlock in Postgres
In case your pgsql version >= 9.2, the locking query would be there select * from pg_stat_activity where pid=6656 In case your pgsql version < 9.2, the locking query would not be there select * from pg_stat_activity where procpid=6656 In order to kill the process select pg_terminate_backend(pid_to_kill); or select pg_cancel_backend(pid_to_cancel); I'd rather recommend that you do pg

Categories : Postgresql

Tablefunc in Redshift to pivot table not supported?
CREATE EXTENSION was introduced with Postgres 9.1. Amazon Redshift is based on PostgreSQL 8.0.2. Only selected features from later versions were implemented. I does not seem like Redshift supports the extension tablefunc at all. You'll have to use CASE statements to emulate the functionality. Here is a related post on the AWS (that you already found in your comment).

Categories : Postgresql

The same sql statement works differently in Jasper and pgAdmin
Try without the date keyword. Jasper may be understanding this the wrong way. (Just a shot in the dark.) String literals are cast to date automatically in this context: SELECT spent_on FROM time_entries WHERE spent_on BETWEEN '2014-01-01' AND '2014-11-1' ORDER BY spent_on DESC; I would also have suggested ORDER BY spent_on DESC NULLS LAST, but you say the column is defined NOT NULL, so not

Categories : Postgresql

Avoid duplicate entries based on multiple columns in PostgresSQL
Try this: SELECT targetID, targetName, missionID, teamID, date_trunc('minute',eventTimestamp ) AS eventTimestamp FROM ( SELECT a.characterID, a.targetID, a.targetName, a.missionID, b.teamID as teamID, a.eventTimestamp FROM events_live a LEFT JOIN ( SELECT distinct characterID, teamID from events_live WHERE eventName = 'missionStarted' OR eventName = 'charact

Categories : Postgresql

Backup specific tables in AWS RDS Postgres Instance
When restoring RDS from a Snapshot, a new database instance is created. If you only wish to copy a portion of the snapshot: Restore the snapshot to a new (temporary) database Connect to the new database and dump the desired tables using pg_dump Connect to your staging server and restore the tables using pg_restore (most probably deleting any matching existing tables first) Delete the temporary d

Categories : Postgresql

Transactions - Oracle Vs PostgreSQL
PostgreSQL do not have feature to insert Save Points in the trigger functions Actually, it does, they're just implicit in BEGIN ... EXCEPTION blocks. Your code looks correct, and it's not clear what you are trying to achieve that it doesn't already do. If you want multiple savepoints in triggers, nest BEGIN ... EXCEPTION blocks. You can use RAISE with a custom SQLSTATE for flow control. It

Categories : Postgresql




© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.