SELECT con.conname AS constraint_name, CASE con.contype WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' WHEN 'f' THEN 'FOREIGN KEY' WHEN 'c' THEN 'CHECK' WHEN 'x' THEN 'EXCLUSION' ELSE con.contype END AS constraint_type, rel.relname AS table_name, pg_get_constraintdef(con.oid) AS definition FROM pg_constraint con JOIN pg_class rel ON rel.oid = con.conrelid JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace WHERE nsp.nspname = 'owner' AND rel.relname = 'table_name' ORDER BY constraint_name;
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Friday, May 23, 2025
How to find constraints on a table in postgres
Thursday, May 22, 2025
PostgreSQL: difference between \copy and COPY
If you put a statement like this in a file:
This in turn requires that the user executing the COPY command is defined as a superuser or has the pg_read_server_files privilege granted to it.
As the error message says, we could use the client-side command \copy instead. Put this in the same script as above, or use the -f flag directly at the prompt:
COPY at_locations FROM '/external/data/geolocations_at_fixed.csv' WITH ( FORMAT csv, DELIMITER ';', NULL 'NULL', HEADER false );and execute it like this:
psql -h myserver.oric.no -U vinmonopolet -d vinmonopolet vinmonopolet=> \i cp_at.sqlYou will see the error
ERROR: must be superuser or have privileges of the pg_read_server_files role to COPY from a file HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.This is because the COPY command runs server-side, and PostgreSQL server expects be able to access the file and its entire path on the server.
This in turn requires that the user executing the COPY command is defined as a superuser or has the pg_read_server_files privilege granted to it.
As the error message says, we could use the client-side command \copy instead. Put this in the same script
\copy vinmonopolet.at_locations FROM '/external/data/geolocations_at_fixed.csv' WITH (FORMAT csv, DELIMITER ';', NULL 'NULL', HEADER false)and execute either with the \i
psql -h myserver.oric.no -U vinmonopolet -d vinmonopolet -f cp_at.sql Password for user vinmonopolet: COPY 5This works because the \copy command is interpreted client-side. psql reads the file from your local machine (in my case, the "local machine" was indeed the postgres server, but the point stands), and pipes the content into the database. And it succeeds because no elevated privileges are needed for this method.
Subscribe to:
Posts (Atom)