newbie db design question
hello! really noob question here. i have this experimental database design: create table products ( product_id serial primary key, description text, supplier_id) ; create table supplier ( supplier_id serial primary key, description text) ; the products table should be linked to the supplier table via "supplier_id" column. i can't find out what would be the data type of supplier_id in table products to do that. i tried supplier_id serial primary key references supplier but it won't allow multiple primary key. how then to proceed? regards,
Re: newbie db design question
thank you. i have received some tips on how to approach my problem. i will be trying them out. On Sat, 11 Jun 2022, 5:51 pm Peter J. Holzer wrote: > On 2022-06-11 09:43:46 +0100, Ray O'Donnell wrote: > > On 11 June 2022 08:05:41 Rino Mardo wrote: > > really noob question here. i have this experimental database design: > > > > create table products ( > > product_id serial primary key, > > description text, > > supplier_id) ; > > > > create table supplier ( > > supplier_id serial primary key, > > description text) ; > > > > > > the products table should be linked to the supplier table via > "supplier_id" > > column. i can't find out what would be the data type of supplier_id > in > > table products to do that. i tried > > > [...] > > > > how then to proceed? > > > > > > > > You need a foreign key add this to the first table: > > > > constraint foreign key (supplier_id) references > supplier > > (supplier_id) > > Or somewhat simpler: > > create table products ( > product_id serial primary key, > description text, > supplier_id integer references supplier > ); > > (You need to create supplier before doing that, of course.) > > hp > > PS: I noticed that "products" is plural and "supplier" is singular. It's > genereally helpful to choose one convention and stick to it. > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
accessing postgres from c++
hi. i found odbc.postgresql.org when i was looking for a way to hook up my c++ project to postgresql server. installed it and tested to be successful in connection. this was when i had postgresql running from a virtualbox vm in Ubuntu. i installed a postgresql container using Docker for Windows in my laptop. from a Windows terminal i can connect to the postgresql container and from there use psql client to create database. when postgresql was running in an Ubuntu vm in virtualbox, i wasn't able to test the c++ connection. now i want to but can't find a way how. either from python or any programming language. i guess my problem is more of the programming than db server side but i thought to try asking here in case someone can help. thanks. regards,
Re: accessing postgres from c++
a db connector that will fit everyone? i think that's timely. i have tried, once, libpqxx but i think it forces me to use the source. couldn't find a binary that will just install and use like how python works, i.e., "pip install psycopg2". On Tue, 21 Jun 2022, 3:57 pm Matthias Apitz wrote: > > We are developing a huge Library Management System with some 400 tables > and which is/was running on many UNIX derivates (SINIX, HP-UX, AIX, > SunOS, Linux) and all kind of databases one can imagine (INFORMIX, > Oracle, Sybase and now PostgreSQL). The system is written in C, C++ and > Perl and the C/C++ dblayer uses ESQL/C which made it highly portable > between > the above mentioned DBSes. The PostgreSQL ESQL/C has an unbeatable > logging feature which logs all ESQL/C operations with their arguments > and results, like this: > > [20746] [20.06.2022 11:57:19:817]: ECPGconnect: opening database test01 on > bvbzflltdb1 port 5432 with options application_name=SunRise DBCALL V7.2 > (pid=20746) for user sisis > [20746] [20.06.2022 11:57:19:821]: ecpg_execute on line 822: query: select > current_date; with 0 parameter(s) on connection test01 > [20746] [20.06.2022 11:57:19:821]: ecpg_execute on line 822: using PQexec > [20746] [20.06.2022 11:57:19:821]: ecpg_process_output on line 822: > correctly got 1 tuples with 1 fields > [20746] [20.06.2022 11:57:19:821]: ecpg_get_data on line 822: RESULT: > 20.06.2022 offset: 80; array: no > [20746] [20.06.2022 11:57:19:822]: prepare_common on line 936: name > sid_sisisinst; query: "SELECT ctid, * from sisisinst WHERE version = $1" > [20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1174: query: > declare sisisinst_seq cursor with hold for SELECT ctid, * from sisisinst > WHERE version = $1; with 1 parameter(s) on connection test01 > [20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1174: using > PQexecParams > [20746] [20.06.2022 11:57:19:823]: ecpg_free_params on line 1174: > parameter 1 = V7.2 > [20746] [20.06.2022 11:57:19:823]: ecpg_process_output on line 1174: OK: > DECLARE CURSOR > [20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1585: query: fetch > sisisinst_seq; with 0 parameter(s) on connection test01 > [20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1585: using PQexec > [20746] [20.06.2022 11:57:19:823]: ecpg_process_output on line 1585: > correctly got 1 tuples with 7 fields > [20746] [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: > (0,35) offset: 19; array: no > [20746] [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: > 22.09.2021 offset: 137; array: no > [20746] [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: > srap34dxr1-20210616 offset: 137; array: no > ... > > The only missing thing in this logging was the PID and exact timestamp > of the operation. The latter makes performance analysing very easy. > We added this (PID and timestamp) to the sources of the ecpglib: > > postgresql-14.1/src/interfaces/ecpg/ecpglib/misc.c > > I could share the diff for maybe to be included in the original > sources. > > matthias > -- > Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ > +49-176-38902045 > Public GnuPG key: http://www.unixarea.de/key.pub > May, 9: Спаси́бо освободители! Thank you very much, Russian liberators! >
Re: How to use 32 bit ODBC driver
hi. you didn't mention, after downloading did you install it? On Tue, 21 Jun 2022, 9:11 pm Aditya Bhardwaj wrote: > Hi, > > For Postgres, I'd like to utilize a 32-bit ODBC driver. I attempted to > download it, but it does not appear in the 32-bit ODBC Data Source. Please > assist me in locating or downloading the PostgresSql 32Bit OBDC driver. > > > > Regards, > > Aditya Bhardwaj > Disclaimer: *The content of this email is confidential and intended for > the recipient specified in message only. It is strictly forbidden to share > any part of this message with any third party, without a written consent of > the sender. If you received this message by mistake, please reply to this > message and follow with its deletion, so that we can ensure such a mistake > does not occur in the future.* >
