See also questions close to this topic

• ERROR: invalid byte sequence for encoding WITH psql

  I've seen numerous issues in other posts with the copy command and:

  ERROR:  invalid byte sequence for encoding "UTF8": 0xfc
  

  And the consensus in these posts appears to be to specify the encoding in the command you're doing the copy with. I have done so:

  psql -h localhost -p 5432 -d BOBDB -U BOB -c "\COPY \"BOBTB01\" FROM 'C:\Temp\file.csv' with csv HEADER ENCODING 'WIN1252'";
  Password for user BOB:
  **ERROR:  character with byte sequence 0x81 in encoding "WIN1252" has no equivalent in encoding "UTF8"
  CONTEXT:  COPY BOBTB01, line 76589**
  

  So, that confused me and I changed to UTF8 to WIN1252 and having done so I get a slightly different error, the failure is on a different line and the text is slightly different.

  psql -h localhost -p 5432 -d BOBDB -U BOB -c "\COPY \"BOBTB01\" FROM 'C:\Temp\file.csv' with csv HEADER ENCODING 'UTF8'";
  Password for user BOB:
  **ERROR:  invalid byte sequence for encoding "UTF8": 0xfc
  CONTEXT:  COPY BOBTB01, line 163**
  

  This is the encoding shown in the database:

  show client_encoding;
   client_encoding
  -----------------
   UTF8
  (1 row)
  

  The file is from a reliable source and I happen to have "R" installed which also does .csv import. The file was pulled into "R" without issue, that's making me think it's not the file but something else. Is there another switch or syntax that can bypass these issues perhaps?

  I'm not sure what is wrong.

  Can you help?

  Thanks.
• Whats missing on my Ruby 'Inverse Of' relationship

  I know this topic has been addressed, but I have been at this for 2 days and I'm just stuck. I know inverse of does not create a new query, so should I use another method?

  Question: How to set up an 'inverse of' with a has_one, belongs_to situation & same class..

  Explanation: A user 'has_one :spouse' and 'belongs_to :spouse_from'. They are inverse of each other. When a User signs up, they can invite their significant other. For Example

  1. user_a invites & creates user_b
  2. user_b.spouse_id is set to user_a.id
  3. In a separate method I want to be able to update like.. user_a.spouse_id = user_a.spouse.id

  The only association that works at this point is user_b.spouse.

   Class User
    has_one :spouse, class_name: 'User', foreign_key: :spouse_id, dependent: :nullify, inverse_of: :spouse_from
  
  
    belongs_to :spouse_from, class_name: 'User', foreign_key: :spouse_id, inverse_of: :spouse, optional: true
  
• Normalizing data in postgresql

  Flag This application will read an iTunes library in comma-separated-values (CSV) and produce properly normalized tables as specified below. Once you have placed the proper data in the tables, press the button below to check your answer.

  We will do some things differently in this assignment. We will not use a separate "raw" table, we will just use ALTER TABLE statements to remove columns after we don't need them (i.e. we converted them into foreign keys).

  We will use the same CSV track data as in prior exercises - this time we will build a many-to-many relationship using a junction/through/join table between tracks and artists.

  To grade this assignment, the program will run a query like this on your database and look for the data it expects to see:

  
  SELECT track.title, album.title, artist.name
  FROM track
  JOIN album ON track.album_id = album.id
  JOIN tracktoartist ON track.id = tracktoartist.track_id
  JOIN artist ON tracktoartist.artist_id = artist.id
  ORDER BY track.title
  LIMIT 3;
  

  Expected out put is this

  The expected result of this query on your database is: title album artist A Boy Named Sue (live) The Legend Of Johnny Cash Jo

  
  DROP TABLE album CASCADE;
  CREATE TABLE album (
      id SERIAL,
      title VARCHAR(128) UNIQUE,
      PRIMARY KEY(id)
  );
  
  DROP TABLE track CASCADE;
  CREATE TABLE track (
      id SERIAL,
      title TEXT, 
      artist TEXT, 
      album TEXT, 
      album_id INTEGER REFERENCES album(id) ON DELETE CASCADE,
      count INTEGER, 
      rating INTEGER, 
      len INTEGER,
      PRIMARY KEY(id)
  );
  
  DROP TABLE artist CASCADE;
  CREATE TABLE artist (
      id SERIAL,
      name VARCHAR(128) UNIQUE,
      PRIMARY KEY(id)
  );
  
  DROP TABLE tracktoartist CASCADE;
  CREATE TABLE tracktoartist (
      id SERIAL,
      track VARCHAR(128),
      track_id INTEGER REFERENCES track(id) ON DELETE CASCADE,
      artist VARCHAR(128),
      artist_id INTEGER REFERENCES artist(id) ON DELETE CASCADE,
      PRIMARY KEY(id)
  );
  
  \copy track(title,artist,album,count,rating,len) FROM 'library.csv' WITH DELIMITER ',' CSV;
  
  INSERT INTO album (title) SELECT DISTINCT album FROM track;
  UPDATE track SET album_id = (SELECT album.id FROM album WHERE album.title = track.album);
  
  INSERT INTO tracktoartist (track, artist) SELECT DISTINCT ...
  
  INSERT INTO artist (name) ...
  
  UPDATE tracktoartist SET track_id = ...
  UPDATE tracktoartist SET artist_id = ...
  
  -- We are now done with these text fields
  ALTER TABLE track DROP COLUMN album;
  ALTER TABLE track ...
  ALTER TABLE tracktoartist DROP COLUMN track;
  ALTER TABLE tracktoartist ...
  
  SELECT track.title, album.title, artist.name
  FROM track
  JOIN album ON track.album_id = album.id
  JOIN tracktoartist ON track.id = tracktoartist.track_id
  JOIN artist ON tracktoartist.artist_id = artist.id
  LIMIT 3;
  

  What am i doing wrong with the code?
• Hosting frontend and backend on two different Heroku domains

  I made a full-stack web application with React and Express as part of my portfolio projects. I tried publishing it on Vercel and Heroku (tried Netlify also) but unfortunately, I learned that sending cookies between two different domains is not possible. The next thing I tried, is to host both on Heroku (e.g: client.herokuapp.com & server.herokuapp.com) which, unfortunately, does not work also.

  What else do I need to do? I am still learning how to deploy properly so I might miss out on some workarounds. Advice is greatly appreciated.
• There is an error 502 .How can i solve it .(Internal Server Error)

  I use to load server code in heroku . Sometime I get data from heroku .But sometime give me an error in 502. I use noSql database MongoDB
• Heroku buttons styling issue

  I made an app locally and pushed it on Heroku. The app is working perfectly on Heroku, however on the Homepage I have 2 buttons ( login and register ). When I run my app locally my buttons have some whitespace in between and they are rounded ( I am using tailwind for this). But on Heroku both buttons are not rounded and there is no whitespace in between them. When I inspect the button on Heroku, I see: class:".... rounded-full", which is what I expected and should have made the button rounded. Anyone has any idea what could cause this and how I can solve this ?

  This is how my index.js looks like:

  export default function Login() {
    return(
      <div>
        <Loginbuttons/>
      </div>
     )
  }
  

  and Loginbuttons.js defined as:

  import Link from 'next/link'
  
  export default function Loginbutton(props){
    return (
     <div>
     <div className = 'block'>
      <button
        style={{width:"10%", paddingTop:"15px",   paddingBottom:"15px"}}
        className='mt-10 mb-10 bg-blue-500 hover:bg-blue-700 text-wgvhite font-bold py-2 px-4 rounded-full'>
          <Link href='login' style={{textDecoration: 'none'}}>Login</Link>
      </button>
  
      <button
        style={{width:"10%", paddingTop:"15px", paddingBottom:"15px"}}
        className='mt-10 mb-10 ml-10 bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded-full'>
          <Link href='register' style={{textDecoration: 'none'}}>Register</Link>
      </button>
    </div>
  </div>)}
  
• how can I solve this circular dependent imports in order to execute many to one insert

  I have the following two models in several files:

  routes/camera/model.py

  from services.db import db
  from dataclasses import dataclass
  from routes.project.models import Project
  
  @dataclass
  class Camera(db.Model):
      __tablename__ = 'cameras'
      id: int
      name: str
      project_id: int
      url: str
  
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(80), unique=True, nullable=False)
      url = db.Column(db.String(1000), unique=True, nullable=False)
      project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), nullable=False)
      
  db.create_all()
  

  routes/project/model.py

  from services.db import db
  from dataclasses import dataclass
  from routes.camera.models import Camera
  
  @dataclass
  class Project(db.Model):
      __tablename__ = 'projects'
      id: int
      name: str
  
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(80), unique=True, nullable=False)
      cameras = db.relationship(Camera, backref='projects')
      
  db.create_all()
  

  The problem is that I need make a insert by project name in camera in order to do this I have the following approach:

  def create_camera(name: str, url:str, project_name:str):
      project = Project(name=project_name)
      camera = Camera(name=name, project_id=project, url=url)
      db.session.add(camera)
      db.session.commit()
  

  but I get a circular dependant and I cannot make this insert

  So, how can I solve this problem (circular dependent import) or the insert in order to avoid to instantiate the Project class?

  Thanks
• Multiple flask calls in single pytest having different databases

  So I'm trying to test an endpoint in flask that I need to call multiple times in one test. The issue I am having is that on the second request the objects I placed into the DB using pytest fixture with the scope of function do not appear in the db on that second call. Is this normal / is there a way to change this behavior? Using SqlAlchemy for ORM with a scoped_session. Thanks!
• Query difference between db.session.query and Model.query in flask-SQLAlchemy

  The database is near 5 millions rows. I declare a model like below:

  class Amodel(db.Model):
       id = db.Column(db.Integer, primary_key=True)
       date = db.Column(db.String)
       money = db.Column(db.String)
  

  • I made a index of money column and it doesn't affect result.

  Way 1 - session.query：

  from flask import Flask
  from flask_sqlalchemy import SQLAlchemy
  
  app = Flask(__name__)
  db = SQLAlchemy(app)
  s1 = db.session.query(Amodel).filter(Amodel.money=='1000').all()
  
  

  Way 2 - Model.query：

  s2 = Amodel.query.filter(Amodel.money=='1000').all()
  
  

  Time consumption

  test1:
  s1:0.06102442741394043
  s2:0.6709990501403809
  
  test2:
  s1:0.0010263919830322266
  s2:0.6235842704772949
  
  test3:
  s1:0.0029985904693603516
  s2:0.5942485332489014
  

  They got the same result but time consumption is so different. I usually use way2 for query because I think it's more readble. Could someone explain what's happen inside and how to optimize?
• PostGIS partition table by POLYGON geometry

  Hello my fellow postgis users,

  I am in need of your help!

  I have a table that has a geometry of type POLYGON that I would like to partition to make queries more efficient. As the main condition for the searches is a WHERE clause of type ST_Intersects with an AOI, I thought this would be a good idea. (please advise if not)

  I have searched and found little or old information regarding partitioning a table on geometry.

  I am trying to use a PARTITION BY RANGE on the geometry column but I am having a few issues.

  I read that PostGIS can sort geometries and so I prepared a grid of points for the FROM-TO values of the partitions.

  • The first problem was that I could not mix POINT in this clause because it was a different type of geometry, so I instead used POLYGON that has al vertex as the same point (puaj!).
  • Second, I find that the ordering of this grid is not what I expected and so I have not been able to successfully build al the needed partitions.
  • Third, I am doubting this to be a viable solution as there may be records in which the geometries may span more than one of the regions I selected for partitioning and so they would not belong to any partition or to multiple partitions (not acceptable).

  Do you I have a future here or should I give up completely on geometry partitioning this table? Thanks!!

  Following a simplified version of what I am trying to do:

  CREATE TABLE usercare.product_catalog (
      id serial4 NOT NULL,
      geom public.geometry(polygon, 4326) NULL,
      CONSTRAINT product_catalog_pkey PRIMARY KEY (id, geom)
  ) PARTITION BY RANGE (geom);
  
  CREATE TABLE product_catalog_A1 PARTITION OF product_catalog for VALUES FROM (ST_GEOMFROMTEXT('POLYGON((-135 67.5, -135 67.5, -135 67.5, -135 67.5))',4326)) TO (ST_GEOMFROMTEXT('POLYGON ((-180 90, -180 90, -180 90, -180 90))',4326));
  
• Pgrouting insert od columns into pgr_dijkstra query

  I try to calculate travels between a pair list of 2 layers : origin and destination. I create a table of combinaison between this two layers with vertices id in vertices_o for origin and vertices_d for destination. I call the columns into this query.

  The combination table

  

  The query

  CREATE TABLE travel.results AS
  (SELECT seq, path_seq, node,  edge, di.cost, agg_cost, the_geom FROM pgr_dijkstra('
              SELECT gid as id,
               source::integer,
               target::integer,
               length_m::double precision as cost
              FROM travel.ways', 
              vertices_o, vertices_d, true) as di
  JOIN travel.ways ways_vertices_pgr
  ON di.edge = ways_vertices_pgr.gid);
  

  He didn't recognize the columns vertices_o and vertices_d.
• Dockerfile postgres install extention pg_stat_monitor

  I want to install pg_stat_monitor extension in Dockerfile. I found this article https://pgxn.org/dist/pg_stat_monitor/. Prepared this step in the Dockerfile

  RUN git clone https://github.com/percona/pg_stat_monitor \
              && cd /build/pg_stat_monitor \
              && make USE_PGXS=1 \
              && make USE_PGXS=1 install 
  

  No, I get this error

   ---> Running in baece8df47d0
  fetch https://dl-cdn.alpinelinux.org/alpine/v3.15/main/x86_64/APKINDEX.tar.gz
  fetch https://dl-cdn.alpinelinux.org/alpine/v3.15/community/x86_64/APKINDEX.tar.gz
  ERROR: unable to select packages:
    clone (no such package):
      required by: .fetch-deps-20220504.193645[clone]
    https://github.com/percona/pg_stat_monitor (no such package):
      required by: .fetch-deps-20220504.193645[https://github.com/percona/pg_stat_monitor]
  
  

  Could you please help me install this extension inside the docker container. Thanks.