PostgreSQL syntax problem.

Query below will run but result is: ERROR:  out of memory for query result

SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,

AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,

FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,

LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,

AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,

SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,

L.LOCALITY_NAME as LOCALITY_NAME,

ST.STATE_ABBREVIATION as STATE_ABBREVIATION,

AD.POSTCODE as POSTCODE,

ADG.LATITUDE as LATITUDE,
ADG.LONGITUDE as LONGITUDE,
GTA.NAME as GEOCODE_TYPE,

AD.CONFIDENCE as CONFIDENCE,

AD.ALIAS_PRINCIPAL as ALIAS_PRINCIPAL,
AD.PRIMARY_SECONDARY as …

Query problem in postgreSQL

There are 33 tables and there is a query that is meant to set the delimiter as '|'

COPY address_alias_type_aut
C:\GNAF\Authority Code\Authority_Code_ADDRESS_ALIAS_TYPE_AUT_psv.csv' DELIMITER '|' CSV HEADER;
 
But it will not run:
ERROR:  syntax error at or near "C"
LINE 3: C:\GNAF\Authority Code\Authority_Code_ADDRESS_ALIAS_TYPE_AUT...
        ^
SQL state: 42601
Character: 30

I've doublechecked the folders and file names in C: drive and they appear to be correct (files uploaded here)
The full tables setup query and the full delimiter query are attached.

Help will be much appreciated.
create-tables.txt set-delimiter.txt Authority-Code.rar

Hi,  Please find attached reference code in detail, it is regarding Postgres store procedure/function and temp tables on Greenplum database.
I am looking for a  meaningful solution or work around solution, please don't provide time wasting ambiguous answers.
expert_exchange_question.txt

Hi Experts,

I get the following error for python application.

django.db.utils.OperationalError: FATAL:  remaining connection slots are reserved for non-replication superuser connections

Select all Open in new window

I use dj_database_url.py for configuring database.   I am using Postgres as a database

I had set connection_max_age to 0 in the config. dj_database_url.py

Still, I get this error.

I had checked the entire source code by using grep there is no connection in the source code.

I getting this error from inside docker container.  the database is outside the docker container.

the connection string I use to connect is DATABASE=postgres://test:*****@172.17.0.1/test where 172.17.0.1 is docker gateway.

max_connection =100 in postgresql.conf which located inside /etc/postgresql/9.5/main/

Any help is greatly appreciated.

I have a c# application that needs to write into a postgresql table using a stored procedure

I have tried the stored procedure manually to conform it allows the insert operation and its ok

I attached a step by step explanation of what i have done in the attached word file

The following is contained in the attachment

1.      Values in the Model Based on Debugger
2.           Stored Procedure scripts for the inserts
3.     .InvalidCastException: 'Can't write CLR type System.Int32 with handler type TextHandler'
        Error Screen during the execution of the script
4.      SCHEMA of the Table  into which I have already inserted a record to test the storedprocedure
What could i possibly doing wrong and how can i resolve the issue

Thanks

Oluwole
Error-Inserting-into-a-postgre-tabl.docx

I have C# Application writing into a postgresql database and basically  what i want to do during login is to

1. Login with a valid UserName and Password  (2 parameters)

2. Get the Database  to return 3 values (parameters)  after  successful  validation of username and password
    ie  ID (serial No)  of Person  ,  the  COMPANY   and  ROLE

3. I want the returned values placed on the login screen  ( for my verification )

I have achieved  1 and 2 above

and for the sample data that i tested  the values  returned from the database  are
ID = 6    COMPANY = company03  and  ROLE = GEN  (see debugger screen in word file)

Getting the 3 values above from the Data Access Layer to the User Interface layer (Login form) is where i have a challenge

I attach  a detailed word file showing  the following

1. DataAccess  Layer  Script showing the 2 input and 3 output parameters. It is ok and working

2. Refactored Class in the Data Access layer

3. Script Under Login Button

4. Login Screen Showing 3 Values to be Returned from Database

5. Debugger  Values Showing the 3 variables selected from the database and values returned

6. Debugger Values on Loginscreen showing var03 = null  even though it is selected  as "GEN" from Database

The Major Issues I want addressed  are:

1. The refactored class  only has variable var03 specified  I want the correct syntax  that will accomodate var01, var02 and var03

2.  From the debugged login screen my var03 is …

Hi,
I have created the following table-
CREATE TABLE rbl_kyc_profile (
      userid int8 NOT NULL,
      kyctype varchar(50) NOT NULL,
      effectivekycstatus varchar(50) NOT NULL,
      validfrom timestamp NULL,
      validtill timestamp NULL,
      authtype varchar(50) NULL,
      ovdnumber varchar(50) NULL,
      attrs jsonb NULL,
      name varchar(100) NULL
);
Many places i have seen an extra column id in the table.
Do i need to do that in this table ??
what are the pros and cons of it ?

Thanks

Hi,
I have to create the following table in postgreSQL :

create table rbl_kyc_profile (
  id int8 not null,
  userid varchar(50) not null,
  kyctype enum ('PAPER','E_KYC','MINIMAL','SHORTFALL')
)

Select all Open in new window

I got an error : SQL Error [42704]: ERROR: type "enum" does not exist
 
I saw on postgreSQL documentation page : https://www.postgresql.org/docs/9.2/datatype-enum.html
That one first needs to create an enum type in postgreSQL.
This is very different from MYSQL where you can just create a column of enum type by specifying the values...
I have the following questions..

1) Since the kycType in my java classes is an enum. And it has a fixed set of values should i create an ENUM in postgre for it or just create a varchar for it ?
2) Because postgreSQL uses enum types in a different way than MySQL does what impact it has if i create the above kycType as an enum type and then use it in the table ?

Thanks

I have a c# application and need to run a stored procedure in local  postgresql database

Postgre 9.5.1 is installed on my PC  and

I have tried the stored procedure .  It works ( Its a simple select statement)

Below  are the following:

1. My connection  string
2.  Segment that  generated the error
3. Details of the error
4. Screen Print of where error occured using the Visual Studio Debugger

1. Connection String

<add name="Tournaments" connectionString="Server=(localhost:5432);Database=Tournaments;Integrated Security=true;Provider=PostgreSQL;" providerName="System.Data.OleDb.OleDbConnection"/>

2. Error on Output =  Line

public List<AbseCodeModel> getabsecode_all()
        {
            List<AbseCodeModel> output;
            using (IDbConnection connection = new System.Data.OleDb.OleDbConnection(GlobalConfig.CnnString(db)))
            {
                output = connection.Query<AbseCodeModel>("spgetabsecode_all").ToList();
            }
            return output;
        }


3. Detailed Error

System.Data.OleDb.OleDbException
  HResult=0x80040E21
  Message=Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
  Source=System.Data
  StackTrace:
   at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection …

Hi All,
   
 Postgresql 9.6 and we have two tables.
First table layout is like below.

   TB_ID       Col_id     Col_nm                       Col_Desc
      1               1           fin_cd1                         finance Code 1
      1               2           fin_cd2                         finance Code 2
      1               3           fin_cd3                         finance Code 3

Select all Open in new window

  TB_ID                 Code_Value
      1                       FA|BR|CM
  

Select all Open in new window

I need to display the result as follows:

    TB_ID                fin_cd1            fin_cd2                  fin_cd3
       1                        FA                     BR                         CM

Select all Open in new window

           

First table is joined to the second, on the TB_ID, and the number of rows in  the first table is equal to the number of pipe delimited values in the second table.

Please help