accepted answer is correct but if you want more than just the EoghanM’s comment to go on the following worked for me in COPYing a table out to CSV… from sqlalchemy import sessionmaker, create_engine eng = create_engine(“postgresql://user:pwd@host:5432/db”) ses = sessionmaker(bind=engine) dbcopy_f = open(‘/tmp/some_table_copy.csv’,’wb’) copy_sql=”COPY some_table TO STDOUT WITH CSV HEADER” fake_conn = eng.raw_connection() fake_cur = … Read more
I finally discover the error. The problem is that the primary key constraint name is equal the table name. I don know how postgres represents constraints, but I think the error “Relation already exists” was being triggered during the creation of the primary key constraint because the table was already declared. But because of this … Read more
It is not supported. DataFrameWriter can either append to or overwrite existing table. If your application requires more complex logic you’ll have to deal with this manually. One option is to use an action (foreach, foreachPartition) with standard JDBC connection. Another one is to write to a temporary and handle the rest directly in the … Read more
Your libpq, which is used by psycopg2 expects Postgres socket to be in /var/run/postgresql/ but when you install Postgres from source it is by default it in /tmp/. Check if there is a file /tmp/.s.PGSQL.5432 instead of /var/run/postgresql/.s.PGSQL.5432. Try: conn=psycopg2.connect( database=”mydb”, user=”postgres”, host=”/tmp/”, password=”123″ )
Download the PostgreSQL JDBC Driver from https://jdbc.postgresql.org/download.html Then replace the database configuration values by yours. from pyspark.sql import SparkSession spark = SparkSession \ .builder \ .appName(“Python Spark SQL basic example”) \ .config(“spark.jars”, “/path_to_postgresDriver/postgresql-42.2.5.jar”) \ .getOrCreate() df = spark.read \ .format(“jdbc”) \ .option(“url”, “jdbc:postgresql://localhost:5432/databasename”) \ .option(“dbtable”, “tablename”) \ .option(“user”, “username”) \ .option(“password”, “password”) \ .option(“driver”, “org.postgresql.Driver”) … Read more
You have to edit postgresql.conf file and change line with ‘listen_addresses’. This file you can find in the /etc/postgresql/9.3/main directory. Default Ubuntu config have allowed only localhost (or 127.0.0.1) interface, which is sufficient for using, when every PostgreSQL client work on the same computer, as PostgreSQL server. If you want connect PostgreSQL server from other … Read more
With Docker Compose When working with Docker Compose, you can use command: postgres -c option=value in your docker-compose.yml to configure Postgres. For example, this makes Postgres log to a file: command: postgres -c logging_collector=on -c log_destination=stderr -c log_directory=/logs Adapting Vojtech Vitek’s answer, you can use command: postgres -c config_file=/etc/postgresql.conf to change the config file Postgres … Read more
The reason this feels weird to you is that you are thinking of the increment on the counter as part of the insert operation, and therefore the “DO NOTHING” ought to mean “don’t increment anything”. You’re picturing this: Check values to insert against constraint If duplicate detected, abort Increment sequence Insert data But in fact, … Read more
This is what I do in PostgreSQL 9.2, although I must admit I did not find this approach documented. There is a function pg_trigger_depth() documented here, which I use to differentiate between original and nested calls in the trigger. CREATE TRIGGER trg_taxonomic_positions AFTER INSERT OR UPDATE OF taxonomic_position ON taxon_concepts FOR EACH ROW WHEN (pg_trigger_depth() … Read more
I think the easiest way would be a regular expression match: select ‘12.41212’ ~ ‘^[0-9\.]+$’ => true select ‘Service’ ~ ‘^[0-9\.]+$’ => false