ERROR: operator does not exist: timestamp without time zone + integer
i am adding nthmonth (2) in my postgresql function , but at the time of execution it showing error "ERROR: operator does not exist: timestamp without time zone + integer" HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT pi_date + nthMonth || ' month ' :: INTERVAL
DECLARE beginMonth timestamp; pi_date timestamp := to_timestamp('14-Jan-2016 01:50 AM,'DD-MON-YYYY HH:MI AM); > beginMonth := pi_date + nthMonth || ' month ' :: INTERVAL;
It's fairly obvious - the "+" is binding more tightly than the "||" (as it is telling you).
You want something like:
pi_date + (nthMonth || ' months'::interval)
Or, perhaps a little clearer:
pi_date + (nthMonth * interval '1 month')
See also questions close to this topic
Saiku report error while creating using Mondrian datasource
I am dealing with a Postgresql database to create a new Mondrian DataSource. A relational DataSource works fine adding to the Report Server lib path the Postgresql jdbc client JAR file and configuring database URL, user and password, I am able to create BIRT reports based on this relational DataSources. The problem comes when i try to create a new Mondrian Datasource based on a Star Schema Postgresql Database on the same Postgresql server as Relational one. I have created the OLAP schema with Pentaho Schema Workbench, i am able to navigate with the tool thorugh the Star Schema Database with the jdbc connection. I created the Mondrian Datasource with the following configuration:
Name: Mondrian Username: tad Password: password (the same credentials that i use for relational database conection since both databases are in the same instance) URL: jdbc:postgresql://ip:port/database_name(I cant share ip and database name as per policy ) Properties: type=OLAP name=Mondrian driver=mondrian.olap4j.MondrianOlap4jDriver jdbcDrivers=org.postgresql.Driver Schema: (Schema generated with Pentaho Schema Workbench)
When I click on apply i am not getting any Error (there is no test connection so i guess there is no reason why i should get any error) , but once i tried to create a new Saiku report using this Datasource, i get the following login failed error: The server requested password-based authentication, but no password was provided.
How to avoid duplicate records with user edits in a referential system?
I am curious to see how the SO Community deals with this common database problem.
The problem is this. I have textual descriptions of various problems being entered at the desktop into the table ICD_Descriptions (the middle box below). Generally speaking there will also be one code associated with each description. However, over time (i.e., years), the codes for a specific text phrase/description will change. Hence, a general many-to-many relationship will exist for some codes to descriptions. So a third table, dx_log, is being used to allow for a many-to-many relationship between the code and the descriptions. Lastly, other "children" tables that need to see a specific combination of 'code-description' will be given a reference to the primary key (the recid) of the dx_log. I believe this arrangement to be fairly standard database management.
O.K., now for the problem. I wish the codes in the icd_code table to be unique to that table. I also wish the descriptions in the icd_description table to be unique to their table.
The problem. This being a referential system, changes to either the "data" part of the code table, (the code), or the description (in the descriptions table) will be seen in the child tables.
But, how to correctly manage user edits to a code or description that would conflict with the "unique" rule of the respective table?
As an example, lets say the initial text of a description is "this is mispelled", whereas another description text has "this is misspelled". At one point it time, both phrases coexist and are unique. However, at a later point in time, the wrong record is corrected (i.e., mispelled --->misspelled). When the edits are attempted to be saved to the file, it will be detected that the correct record already exists.
So if the dx_log table is also unique on (icd_description_recid, icd_code_recid), then simply replacing the reference to the already existing correctly spelled record in the dx_log will lead to a violation of uniqueness for the dx_log table. Therefore, I can think of only three solutions:
- The table which the children tables actually reference CAN NOT HAVE A UNIQUES constraint on its reference pointers,
- Leave the uniqueness constraint on the dx_log, but when a conflict would violate uniqueness, then use a migrate procedure to move the child table references to the existing record (in postgresql this would be heavy use of the catalogs) to the "new record", then delete the existing record before adding the new record.
- Add an additional self-reference pointer in the dx_log such that when a record would conflict with an already existing record in dx_log, then don't change it but rather place a pointer to the already existing "correct" record in the dx_log.
I hope I've explained my question well enough. What is the recommended approach?
Thanks for any comments.
ASP.NET MVC4, EF 6, Postgresql : script generation issue
I have been developing ASP.NET MVC 4 application using PostGreSQL database.
- "Npgsql" - Npgsql is the opoen source .NET data provider for PostgreSQL.
- "Npgsql for Entity Framework 6" - PostgreSQL provider for Entity Framework 6.
Below exception occurs when calling the SimpleMembership provider.
2018-01-19 09:00:30 IST : [1-1] user=postgres,db=PatientScheduler,app=[unknown],client=127.0.0.1 ERROR: syntax error at or near "[" at character 14 2018-01-19 09:00:30 IST : [2-1] user=postgres,db=PatientScheduler,app= [unknown],client=127.0.0.1 STATEMENT: CREATE TABLE [UserProfile]([UserId] int NOT NULL PRIMARY KEY IDENTITY, [UserName] nvarchar(56) NOT NULL UNIQUE)
[PostgresException (0x80004005): 42601: syntax error at or near "["] Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage) +311 Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode) +272 Npgsql.NpgsqlConnector.ReadExpecting() +29 Npgsql.NpgsqlDataReader.NextResultInternal() +296 Npgsql.NpgsqlDataReader.NextResult() +184 Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior) +906 Npgsql.NpgsqlCommand.ExecuteNonQueryInternal() +134 WebMatrix.Data.Database.Execute(String commandText, Object args) +129 WebMatrix.WebData.SimpleMembershipProvider.CreateTablesIfNeeded() +309 WebMatrix.WebData.WebSecurity.InitializeMembershipProvider(SimpleMembershipProvider simpleMembership, DatabaseConnectionInfo connect, String userTableName, String userIdColumn, String userNameColumn, Boolean createTables) +86 WebMatrix.WebData.WebSecurity.InitializeProviders(DatabaseConnectionInfo connect, String userTableName, String userIdColumn, String userNameColumn, Boolean autoCreateTables) +88 PatientSchedulingSystem.Filters.SimpleMembershipInitializer..ctor() in d:\Projects\PatientSchedulingSystem\PatientSchedulingSystem\Filters\InitializeSimpleMembershipAttribute.cs:41
The Entity Framework is not generated scripts based on PostgreSQL standard.
Please assist on this issue. Thanks in advance.
Need to use one temporary table in different session in postgresql
created a temporary table in a postgresql function. and then calling this function from my screen to save some business logic and this screen is multi user , multiple user can access it from different 2 client machine then want to know this same temporary table can be used in it.
create temporary table temp_test ( id int, name text) on commit drop;
using this script in an function fn_test() which created it and inserting records in it. if multi user will access this screen/code then it will work or not ??
Getting instances of a model in Sequelize with two associations
I'm using Sequelize, and am not entirely sure how to get all instances of a model with conditions on two associations. Take the following setup, where individuals own many dogs and don't necessarily use the same kennel to board each one:
let Owner = sequelize.define('owner', ...); let Kennel = sequelize.define('kennel', ...); let Dog = sequelize.define('dog', ...); Dog.belongsTo(Owner); Dog.belongsTo(Kennel); Kennel.hasMany(Dog); Owner.hasMany(Dog);
Given an instance of a kennel and an instance of an owner, how do I find all the dogs that are owned by that owner and boarded in that kennel? Obviously if I only cared about one of those conditions, I could do
owner.getDogs(), but I'm not sure how to generalise that.
server closed the connection unexpectedly in postgresql while compiling plpytghon3u function
While defining a PL/Python function in psql, I get the error:
server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
CREATE OR REPLACE FUNCTION pymax (a integer, b integer) RETURNS integer AS $$ if a > b: return a return b $$ LANGUAGE plpython3u;
The PostgreSQL log:
2018-01-16 17:17:57.296 IST  LOG: server process (PID 5128) was terminated by exception 0xC0000409 2018-01-16 17:17:57.296 IST  DETAIL: Failed process was running: CREATE OR REPLACE FUNCTION pymax (a integer, b integer) RETURNS integer AS $$ if a > b: return a return b $$ LANGUAGE plpython3u; 2018-01-16 17:17:57.296 IST  HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. 2018-01-16 17:17:57.296 IST  LOG: terminating any other active server processes 2018-01-16 17:17:57.300 IST  WARNING: terminating connection because of crash of another server process 2018-01-16 17:17:57.300 IST  DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2018-01-16 17:17:57.300 IST  HINT: In a moment you should be able to reconnect to the database and repeat your command. 2018-01-16 17:17:57.324 IST  FATAL: the database system is in recovery mode 2018-01-16 17:17:57.342 IST  LOG: all server processes terminated; reinitializing 2018-01-16 17:17:57.373 IST  LOG: database system was interrupted; last known up at 2018-01-16 17:17:41 IST 2018-01-16 17:17:57.993 IST  LOG: database system was not properly shut down; automatic recovery in progress 2018-01-16 17:17:58.022 IST  LOG: redo starts at 0/279BA58 2018-01-16 17:17:58.023 IST  LOG: invalid record length at 0/279BA90: wanted 24, got 0 2018-01-16 17:17:58.023 IST  LOG: redo done at 0/279BA58 2018-01-16 17:17:58.247 IST  LOG: database system is ready to accept connections