Questions tagged [postgresql]

PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact version of Postgres when asking questions. Questions concerning administration or advanced features are best directed to dba.stackexchange.com.

Filter by
Sorted by
Tagged with
0
votes
1answer
22 views

More Elegant way to Batch Validate Phone Numbers

I've got this test database that I want to use for validating a phone numbers. http://sqlfiddle.com/#!17/18a38/3 However it currently only works with a single number at a time. SELECT countries....
-1
votes
1answer
16 views

Golang lib/pq: Runtime error when querying a database

I'm setting up a PostgreSQL database for my golang backend, but I'm getting this error when trying to read a table: runtime error: invalid memory address or nil pointer dereference /FwzFiles/go/src/...
1
vote
1answer
10 views

“Error”: “invalid input value for enum” only when using pg package in application to do INSERT SQL operation

I am beginning to build out the user authentication part of my application and I am using PostgreSQL as my database. I have a table set up in PG with the following code. This is the code I used to set-...
0
votes
1answer
14 views

SQL find parent where children match over multiple rows

I have a database where one user has multiple submission and one submission has multiple post I would like to find all users who have made posts with the tag "car" and posts with the tag "bike". ...
0
votes
0answers
14 views

Ruby on Rails: Accesing postgresql via console on Google Cloud

On my Google App Engine I've depoloyed a Ruby on Rails project and connected it to postgresql. Application seems to be properly connected and everything works fine. However when I want to get access ...
0
votes
0answers
14 views

OffsetDateTime problem in postgresql and spring boot

i want to save date & time & time zone of user when he signup. i have a 'signup_date' column in account table that its type in java is 'OffsetDateTime' and in liquibase is 'Datetime' and in ...
0
votes
1answer
10 views

How to make psql and logs output english with zh_CN.UTF-8 locale set?

I am running postgresql 11. I have set the locale to be zh_CN.UTF-8 because I need to query zh_CN text. But the weird part is I just want it for query, but not the whole system. Right now, all psql ...
0
votes
0answers
7 views

Login in odoo database Through Android App

I have created a Android Mobile app for pushing some data in my database. Now before pushing the data I want the user to login through the credentials he use to login in odoo. I can get the email/...
0
votes
1answer
12 views

Handling database disconnect

I would like to have some advise on how to handle an error in case of my database server is disconnected. Let's say I have 2 server running: API Database Using postman to test my endpoint, I create ...
-1
votes
2answers
17 views

How to insert colons into Mac address from Postgres SELECT without any

I'm trying to convert the output of a SELECT of a MAC address in a Postgres database from for example: ab12cd34ef56 to AB:12:CD:34:EF:56 and I haven't been able to find a Postgres function or Regex ...
0
votes
1answer
21 views

How to join and filter between 3 tables with ecto query expressions

I have 3 schemas: Post, Author, Assistant. Post belongs_to :author Author has_many :posts and has_one :assistant Assistant belongs_to :author I want to build a query that only gets Posts where the ...
0
votes
0answers
26 views

Running SQL script from Jenkins

I have a Jenkins job that does the following - 1) SSH into a server 2) Run a psql script The script runs for 4 hours when I run it manually, but every time I run it from Jenkins it stops at 1 hr ...
1
vote
1answer
20 views

How to determine if an element to the right or left of a range?

I'm looking for a simple method to determine if an integer is to the right or the left of a range. The << or >> range operators would be perfect but they only appear to work range to range. Is ...
0
votes
1answer
12 views

Exclude updated record from trigger function

I have a table with a column named priority. I created a trigger function that fires after update. I want the function to change the values of the rest of the rows' priority column. For example I ...
0
votes
1answer
15 views

Insert into table from select only when select returns valid rows

I want to insert into table from select statement but it is required that insert only happens when select returns valid rows. If no rows return from select, then no insertion happens. insert into ...
0
votes
0answers
16 views

Clean database in production

I successfully deployed my website on Linode on a Ubuntu server. I am using Postgres for database. I can perform all the actions correctly. However, I would like to clean my database (deleting all ...
-1
votes
0answers
16 views

Postgres query taking over an hour

I've got an application running a Postgres db. It sees medium usage--at most it gets about 3-4 RPS, but usually much less. Recently timeout errors have been popping up, which appear to be caused by ...
1
vote
1answer
27 views

How do I write a Django query that finds words in a Postgres column?

I'm using Django and Python 3.7. How do I scan for words in a Django query? A word is a string surrounded by whitespace (or the beginning or end of a line). I have this ... def ...
0
votes
0answers
14 views

Select a MAX when using a window function that orders with nulls first in Postgres

I have a query that I'm attempting to optimize and running into some unexpected behavior. For example, take a table like this: CREATE TABLE objects (id int, external_id int, timestamp timestamp);...
-1
votes
0answers
8 views

Is there a method of performing a query on a postgres rds instance and then performing a COPY command straight to an s3 bucket?

I am currently planning on using an AWS ECS task (or maybe a lambda function) to perform a SQL COPY command against a Postgres database to send a CSV file to the ECS instance running the task and then ...
0
votes
0answers
21 views

Pyspark EMR “The connection attempt failed” when i try to save a datafreme in Postgres

When I try to save a dataframe in Postgres using pyspark on EMR I get the following error: org.postgresql.util.PSQLException: The connection attempt failed. However the table is created, but ...
0
votes
0answers
4 views

DB Connection Keeps Timing Out when I try to connect my AWS RDS PostgreSQL DB to Navicat Essentials

I am unable to establish a DB connection between Navicat Essentials and AWS RDS PostgreSQL. I haven't had any problems or issues in the past so it just stopped working out of the blue.
1
vote
2answers
39 views

Moving PostgreSQL bigint array unique value to another index

How can I move the array bigint value from one index to another? For example, I have an array ARRAY[1, 2, 3, 4] of bigint unique values and want to move value 1 to index 3 so the final result would be ...
0
votes
1answer
25 views

Showing this year and last year's data in one row in PostgreSQL

I have a table of sales grouped by week. I want to write a query that creates a new table giving the sales of the week in question AND the sales of that item from this time last year, but my attempts ...
0
votes
0answers
9 views

AWS DMS - Oracle to PG RDS full load operation error - failed to load data from csv file

I am trying to move data from a oracle instance to postgres RDS using DMS. I am only doing a full load operation and I have disabled all the foreign keys on the target. I also made sure that the ...
1
vote
2answers
21 views

How do I use a JPARepository inside of a PrePersist Annotation?

I have an alert table which is transactional and an alert type table which is master. I would like to send out an email whenever an alert is added to the table, so I figured I would use PrePersist. ...
-1
votes
2answers
51 views

SQL: Select only if multiple values are IN 1 column from CatalogTable

I have the following tables: ---------------- **TableResults** (ResultId pk) ResultId1 ResultId2 --------------- -------------------------------------------------------- **TableResultsPatterns** (...
-1
votes
0answers
15 views

Failure to Connect with Postgresql Database on DigitalOcean Server Uisng PHP (Call to Undefined function…)

I'm unable to Connect with Postgresql Database on DigitalOcean Server. I am getting this Call to Undefined function pg_connect..." error message when I tried to test with postman tool. My PHP ...
2
votes
1answer
19 views

Create nested json blobs in PostgreSQL

I'm trying to create a nested json from a table like this: +----------+---------+------------------------------+ | unixtime | assetid | data | +----------+---------+-----------...
0
votes
0answers
11 views

What is the command line arguments to compile an SQR report using PostgreSQL?

I am trying to compile an SQR report using PostgreSQL. Here is my command line for Oracle: C:\hyperion\SQR\bin\SQR\Server\Oracle\bin\sqr.exe LogAsRun.sqr ads/[email protected] -RS -M sqrsize.dat What would ...
0
votes
2answers
37 views

Proper use of semicolons in functions and procedures

I don't know where to put the semicolons in the following structure. I've tried writing the semicolons after both 'with as' as per my interpretation of the docs but that's not right: CREATE OR ...
1
vote
1answer
15 views

Converting an HEXEWKB to a readable geometry data in postgresql

I have a table from OSM with a column titled way which has values that appear to be HEXWEKB coded geometry(LineString, 3857) (I'm looking at the column type via pgAdmin). I'm trying to understand how ...
0
votes
2answers
42 views

Postgresql: An alternative to subqueries to make the query more efficient?

So I have the following table with the schema: CREATE TABLE stages ( id serial PRIMARY KEY, cid VARCHAR(6) NOT NULL, stage varchar(30) NOT null, status varchar(30) not null, ); with the ...
0
votes
1answer
19 views

Use returned values from a query in the same procedure

I'm quite new to pgpsql. I have two tables - product and product_category - with a one to many relationship. I'm creating a procedure to insert a value into the category table if it doesn't exist and ...
0
votes
1answer
25 views

Sum all numeric columns in database and log results

I have a query which gives me all numeric columns in my Postgres database: SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE table_schema in ( 'datawarehouse_x', '...
0
votes
1answer
20 views

what type of text stored in img field bytea type cannot display using php

i have no idea what data is in the field img bytea.. because this data comes from the previous developer using delphi language i just want to display it in my php code I tried to use ...
-2
votes
0answers
30 views

Why extra apostrophe is added to the psql repace query?

So I have sql statements as follows: var sql = `SELECT pat.title, json_agg (json_build_object( 'statementId', ps.id, '...
0
votes
0answers
24 views

Conditional function not working as intended postgresql

So I got this table on postgresql that people regularly update and the below function. table id integer status integer date date on_hold boolean What this function is supposed to do is to fill ...
0
votes
0answers
16 views

Want to execute a local sql file (using \i to execute multiple sql files) using psql command on ssh server

I have multiple .sql files on my local machine, which I was executing rapidly with one command psql .....-f abc.sql (containing \i def.sql; \i ghi.sql) on db server hosted on aws. Now, I have ...
0
votes
0answers
31 views

Find out leaf level node at each level [on hold]

I have a table id parent_id Name id parent_id 1 null 2 1 3 1 4 2 5 3 I want leaf children at each level. Output Parent child 1 4 1 5 2 4 3 5
0
votes
1answer
25 views

getting more records than expected with simple query in postgresql

I have these query: select b.type,b.name,a.name_b,a.start_time from runinfo a left JOIN definition b on a.sched_table=b.parent_table and a....
0
votes
2answers
49 views

How can I get all records from the database table where the counterparty_id is equal to the received value from the session with the last time stamp?

There are two tables with names "table_A" and "table_B". table_A contains records of documents. table_B constains records of transactions. When creating a new document, a new record is created in ...
1
vote
0answers
8 views

sqlalchemy postgres add comment on table [duplicate]

While doing Declaring Mapping import sqlalchemy import sqlalchemy.ext.declarative Base = sqlalchemy.ext.declarative.declarative_base() class YourTableName(Base): __tablename__ = '...
-1
votes
0answers
15 views

adjust postgres query not to return already fetched data

I have the below query, I am trying to get all points that are within 100m of each, the query generally works ok, but it will return 'duplicates'. (the table location_signals is a table that contains ...
0
votes
0answers
22 views

When using set record in plpgsql?

I have an issue with records in plpgsql. I tried to return a set of record like this. But postgres doesn't want it and raise me en error so how can I do that ? Error is something like I cannot ...
2
votes
1answer
23 views

Updating the values of key columns in Postgresql

I have the following table | ID | Seq No| +++++++++++++++ | 1 | 12345 | | 2 | 12345 | | 3 | 12345 | | 4 | 12345 | | 5 | 12345 | Both the columns are primary key values. Here I need to ...
1
vote
1answer
17 views

Values are not getting inserted into the PostgreSQL database

I have made a database on pgAdmin 4 but values are not updated when sent through signup form on node.also npm install postgre-sql giving an error var express = require('express'); var ...
0
votes
2answers
25 views

Postgresql Exclude constraint on a set of values

Suppose I have a table with two columns: | id | status | Status can take 3 values: booked, started, completed I need a constraint that will satisfy the following requirement: For entire set of ...
2
votes
0answers
20 views

How to ignore corrupted rows in pandas to_sql

I save pandas dataframe to postgres db table using to_sql(). If one of rows is of incorrect format, psycopg2.DataError is raised and whole dataframe does not get saved. I tried to catch the error and ...
-2
votes
5answers
41 views

SELECT users with at least 2 identical products

Users table: user_id name 1 john 2 mark 3 scott 4 piter user_products table: user_id product_id 1 2 1 4 1 5 2 4 2 5 2 7 3 1 3 5 3 4 3 2 4 1 As we see, users 1,2,3 all have products 4 and 5. So how ...